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