none
Find system privilege granted to login/user RRS feed

  • Question

  • Hi,

    I have granted select privilege (object privilege)  to use jack to table 'employee'. I executed below query and able to find what privilege I have granted to user-

    SELECT pr.principal_id, pr.name, pr.type_desc, 
        pr.authentication_type_desc, pe.state_desc, 
        pe.permission_name, s.name + '.' + o.name AS ObjectName
    FROM sys.database_principals AS pr
    JOIN sys.database_permissions AS pe
        ON pe.grantee_principal_id = pr.principal_id
    JOIN sys.objects AS o
        ON pe.major_id = o.object_id
    JOIN sys.schemas AS s
        ON o.schema_id = s.schema_id;

    Then I granted 'view server state' privilege to user jack. But when I executed above query the granted 'view server state' privilege is not showing in the output. 

    Can anyone provide any query so that I can find out what system privilege (not object level privilege) the users are assigned?



    Monday, May 27, 2019 8:50 PM

All replies

  • Hi arifulhaq,

     

    >>Can anyone provide any query so that I can find out what system privilege (not object level privilege) the users are assigned?

     

    You can get the server permission of login by the following query:

     

    SELECT pr.principal_id, pr.name, pr.type_desc,       pe.state_desc, pe.permission_name   FROM sys.server_principals AS pr   JOIN sys.server_permissions AS pe       ON pe.grantee_principal_id = pr.principal_id; 

     

    For more details, please refer to https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-server-permissions-transact-sql?view=sql-server-2017

     

    Hope this could help you .

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, May 28, 2019 2:57 AM