Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

MSDN: fn_my_permissions

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.

Background:

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

to

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.

share|improve this question

closed as not a real question by jcolebrand May 21 '13 at 2:30

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

    
Are you really using 'dbo' (you should avoid using a name that has obvious breaking connotations)? What login is this particular user associated with? What permissions do you expect to see for the login? Are you sure it should be returning any rows? –  Aaron Bertrand May 21 '13 at 1:17
    
@AaronBertrand those are good questions, thank you for them. I'll get back to you soon. –  zespri May 21 '13 at 1:22
    
Why didn't you ask your new question in the first place? I suggest you start a new question, then people don't go down rabbit-holes investigating the wrong problem (fn_my_permissions not working the way you think it should). –  Aaron Bertrand May 21 '13 at 1:50
    
Aaron, every complex system has many interconnected components, and their relationship often can be not that obvious. Still they are all interconnected and affect each other and the end solution. I would like to understand the whole thing, from the beginning to end. It is not uncommon on SE that when you start digging a problem a new information is uncovered. I'd like to solve my problem. But I'd ALSO like to understand fn_my_permissions that returns different results. I'm not sure how to split the question. The parts are interconnected. –  zespri May 21 '13 at 1:57
    
Your primary problem is that your stored procedure is not able to access the rows in sys.dm_exec_requests that you expect it to. That seems like a pretty coherent question that is separately answerable without getting into a thesis about SQL Server security (research you can do). –  Aaron Bertrand May 21 '13 at 1:58

1 Answer 1

I can't explain precisely why you can't get server-level permissions for a database-level principal while impersonating them inside the database, but I can't say I'd ever expect that to work anyway. Especially since you are expecting a special database principal (dbo) to have server-level permissions associated with them. I think there are a couple of points of confusion here, about that, and about the fact that the documentation you linked to says user (which you take to mean database user) when they in fact meant server-level login or server principal.

Here is a workaround that doesn't require impersonation in the first place. Sure it's a little more code but it's also much more flexible.

SELECT perms.* -- list the columns you want 
FROM sys.server_permissions AS perms
INNER JOIN sys.server_principals AS sp
ON perms.grantee_principal_id = sp.principal_id
INNER JOIN sys.database_principals AS dp
ON sp.sid = dp.sid
WHERE dp.name = N'my_user'; -- I assume you didn't mean an impossible
                            -- database principal named 'dbo'...

I tested this by running the following:

CREATE LOGIN my_login WITH PASSWORD = 'foo', CHECK_POLICY = OFF;
GO
GRANT ALTER ANY ENDPOINT TO my_login;
GO
USE my_database;
GO
CREATE USER my_user FROM LOGIN my_login;

Sure enough, the row for ALTER ANY ENDPOINT was returned with my query (but not with the call to fn_my_permissions). CONNECT was also returned. This query won't return any permissions that haven't been explicitly granted, denied, revoked, etc. You may have to infer whether they've got certain permissions by also inspecting what roles they're in, as not all permissions are given through an explicit GRANT.

As a demonstration that VIEW SERVER STATE has nothing to do with any database principal:

USE mydatabase;
GO
CREATE LOGIN floobat WITH PASSWORD = 'foo', CHECK_POLICY = OFF;
GO
CREATE USER flooblattle FROM LOGIN floobat;
GO
GRANT VIEW SERVER STATE TO flooblattle;

This returns the error:

Msg 4621, Level 16, State 10, Line 1
Permissions at the server scope can only be granted when the current database is master.

This is because it is searching for a login to grant that permission, not a database user. If I switch to master and try again:

Msg 15151, Level 16, State 1, Line 1
Cannot find the login 'flooblattle', because it does not exist or you do not have permission.

Again, it is looking for a server-level login, not a database user. It doesn't matter if that database user is dbo or not - they aren't magically granted the keys to the kingdom unless you've done so explicitly and have associated a specific server-level login as the database owner.

share|improve this answer
    
Thank you for the feedback. I have updated my questions. I would like to list all the server permission of a given user, not just one row. –  zespri May 21 '13 at 1:40
1  
@zespri egads. My query returned two rows, actually, the one single permission I granted, and the default CONNECT. I only mentioned the one that I granted. Do you really expect that all server principals will automatically have all of those privileges? It won't return any privileges that haven't been explicitly granted or denied. –  Aaron Bertrand May 21 '13 at 1:45
    
'Do you really expect that all server principals will automatically have all of those privileges?' I expect that the DBO user to have them. Yet it returns a single row. I do understand that we have Server Login and Database Users and that those are mapped to each other. However I don't understand how to get Server Permissions of a user for the purpose of the article I have linked. I have added some more info to the questions. Thank you for you help. –  zespri May 21 '13 at 1:52
    
The dbo principal in a database does not get magically upgraded to sysadmin, sorry, that's not how security works. –  Aaron Bertrand May 21 '13 at 1:55

Not the answer you're looking for? Browse other questions tagged or ask your own question.