If I run this query:
select * From fn_my_permissions(NULL, 'SERVER')
This is what I'm getting:
entity_name subentity_name permission_name ------------- --------------- ------------------------------- server CONNECT SQL server SHUTDOWN server CREATE ENDPOINT server CREATE ANY DATABASE server CREATE AVAILABILITY GROUP server ALTER ANY LOGIN server ALTER ANY CREDENTIAL server ALTER ANY ENDPOINT server ALTER ANY LINKED SERVER server ALTER ANY CONNECTION server ALTER ANY DATABASE server ALTER RESOURCES server ALTER SETTINGS server ALTER TRACE server ALTER ANY AVAILABILITY GROUP server ADMINISTER BULK OPERATIONS server AUTHENTICATE SERVER server EXTERNAL ACCESS ASSEMBLY server VIEW ANY DATABASE server VIEW ANY DEFINITION server VIEW SERVER STATE server CREATE DDL EVENT NOTIFICATION server CREATE TRACE EVENT NOTIFICATION server ALTER ANY EVENT NOTIFICATION server ALTER SERVER STATE server UNSAFE ASSEMBLY server ALTER ANY SERVER AUDIT server CREATE SERVER ROLE server ALTER ANY SERVER ROLE server ALTER ANY EVENT SESSION server CONTROL SERVER (31 row(s) affected)
However if I run this:
execute ('select * From fn_my_permissions(NULL, ''SERVER'')') AS USER = 'dbo'
It returns empty record set. I would like to know why. I expect it to return permissions of a database user called 'dbo'. I do not believe that it does not have any.
Note, that dbo user exists in every database and usually has principal_id of 1 as can be seen by running this query on a database:
select * from sys.database_principals
I would like to know how can I get the server level permission as listed above for an arbitrary user. This MSDN article says:
If the user has VIEW SERVER STATE permission on the server, the user will see all executing sessions on the instance of SQL Server; otherwise, the user will see only the current session.
So I would like to get all server permissions for an arbitrary user to determine weather they meet the article criteria.
I have a stored procedure that queries sys.dm_exec_requests view. In the stored procedure only one row is returned, while the stored procedure needs to see all of them. The MSDN article on the view says that what is returned depends on the user permission as per above.
The stored procedure is actually an activation stored procedure of a broker queue:
CREATE QUEUE test_queue WITH STATUS = ON, RETENTION = OFF , ACTIVATION ( STATUS = ON, PROCEDURE_NAME = test_procedure, MAX_QUEUE_READERS = 1, EXECUTE AS SELF ), POISON_MESSAGE_HANDLING (STATUS = ON) ON [PRIMARY]
When I read the MSDN article, I changed
EXECUTE AS SELF
EXECUTE AS 'dbo'
which did not make any difference.
sys.dm_exec_requests would still return a single row. So naturally I decided to check if the dbo user (which as I understand is supposed to be a super user) has the VIEW SERVER STATE permission. This is how this question was born.
Also I tried to do
EXECUTE AS OWNER
Which made no difference either. As I understand the stored procedure owner is who created it, which was me. And as I'm in a sysadmin that should be working, but it does not. So I'll appreciate any troubleshooting tips.