I have sysadmin privileges on a SQL Server (2014). When I query the table sys.databases from one of my user database (test_db) I get the full list of databases (13 of them inclusive of system databases) that are actually on the server.
But when I execute as 'dbo', some kind of permission deny seems to be applied and the full list of databases don't appear. Neither am I able to see the list of databases nor I am not able to query objects within them as well. For example: I am not able to query msdb.dbo.sysjobs.
Here is what I am running on my user database called "test_db":
EXECUTE AS user = 'dbo'
Then I do the following query:
select * from sys.databases
I see only 3 databases (out of 13 databases) as shown below (master, tempdb, test_db):
I checked the login and principal identifications after the "execute as" and below you can see what those are:
After revert, it executes as myself and I am able to see the full list of DBs back again.
As user 'dbo' which is the database principal for server principal 'sa' it is supposed to have full db_owner access across all databases on the server, but unfortunately it is not so, in this case.
One additional information here is, "test_db" gets restored from a prod server backup every 2 weeks.
Can you help me to figure out why this is happening, please?
Edit: sorry i meant server principal 'sa' not login.