One of the databases that is for DBA use was in restricted user mode , this allows only certain users access to the database, basically its sysadmin users , db_owner or db_creater access to the database.

I wanted to create a table to hold some capacity data / so current DB size and Log size etc ( script in another post to come)

I got the following error message. So started to problem solve.

Msg 916, Level 14, State 1, Procedure add_operator_check, Line 123

The server principal “S-1-9-3-1619820567-1096921846-2423696570-2687005632.” is not able to access the database “Database Named Changed to protect the innocent” under the current security context.

My initial thought was its the restricted user was causing issues , as this was a production server I tried to replicate the issue on my test server without success. Tried changing the ownership of the database was user to SA and visa versa with little success.

I double checked my permissions with my favorite check quick check on production

SELECT IS_SRVROLEMEMBER('sysadmin')

Which returns 1 if I am sysadmin or 0 if I am not. Turns out I was.  I then actually read the message in a bit more detail it referenced a procedure add_operator_check

SELECT * FROM sysobjects WHERE name  like '%add_operator_check%'

This returned 0 rows. Again the error message had got me, first time I had not read it , 2nd time I had taken it literally. I checked a couple more things, ruled out it was something to do with my account, getting someone else to try.

Next steps I am sysadmin and I can’t create a table what are the possible reasons, a database trigger I thought, so I checked that.

SELECT * FROM [sys].[triggers]

Got you. Yep It had returned several rows but the one that I was interested in

But the next weird thing this trigger had actually been in place over a year and hadn’t been modified. But the last table created was in the current month. Curiouser and curiouser ( Can’t believe I am quoting Alice in Wonderland and Yes I did Google that to find out)

looking at the Trigger

CREATE TRIGGER [add_operator_check]
ON DATABASE
WITH EXECUTE AS 'mdw_check_operator_admin'
FOR CREATE_TABLE

The penny starts to drop, the CREATE TABLE statement is not running as myself, sysadmin but the mdw_check_operator_admin which is to do with the management datawarehouse, which wasn’t in use. Further investigation showed this user actually doesn’t have a login to the server hence the actual message so it did actually make sense even though it wasn’t a procedure!!.

The server principal “S-1-9-3-1619820567-1096921846-2423696570-2687005632.” is not able to access the database