locked
Is there any TSQL script or system strored procedure to fine which permissions have a user? RRS feed

  • Question

  • Is there any TSQL script or system strored procedure to fine which permissions have a user? e.f I have a user sa or any other like Test etc have permission Select,update ,delete ,Create database etc Then I want result set like LoginName Permissions Sa Select Sa Update Sa delete Sa Create database .. ……………………….. ... ………………………..
    Friday, August 7, 2009 8:21 AM

Answers



  • I am working on it since when I post this question and I find the solution my self


    For server level permission use this code

    EXECUTE AS login = 'test';

    SELECT permission_name FROM fn_my_permissions(null, 'server')

    REVERT;

    GO

     

    For datbase level permission use this code

    EXECUTE AS USER = 'test';

    SELECT permission_name FROM fn_my_permissions(null, 'database')

    REVERT;

    GO

     

    For reference check this

     

    http://msdn.microsoft.com/en-us/library/ms176097.aspx

     

     

    Ewan thanx for replying I also use below system tables but I think above is the better approach.

     

    Database:
    sys.database_permissions
    sys.database_principals

    Server:
    sys.server_permissions
    sys.server_principals
       


    Aamir

    Friday, August 7, 2009 10:50 AM

All replies

  • Hi Muhammed

    Permissions are stored in

    Database:
    sys.database_permissions
    sys.database_principals

    Server:
    sys.server_permissions
    sys.server_principals

    The tables are very straightforward to join and query.

    If you need any further help, let me know.

    HTH

    Ewan


    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.
    Friday, August 7, 2009 9:29 AM


  • I am working on it since when I post this question and I find the solution my self


    For server level permission use this code

    EXECUTE AS login = 'test';

    SELECT permission_name FROM fn_my_permissions(null, 'server')

    REVERT;

    GO

     

    For datbase level permission use this code

    EXECUTE AS USER = 'test';

    SELECT permission_name FROM fn_my_permissions(null, 'database')

    REVERT;

    GO

     

    For reference check this

     

    http://msdn.microsoft.com/en-us/library/ms176097.aspx

     

     

    Ewan thanx for replying I also use below system tables but I think above is the better approach.

     

    Database:
    sys.database_permissions
    sys.database_principals

    Server:
    sys.server_permissions
    sys.server_principals
       


    Aamir

    Friday, August 7, 2009 10:50 AM