List all permissions a particular user has on sql instance?

  • Is there any way to get the list of all user permissions on a sql server instance through script for a particular user?

  • Here is a script that will list the principals and their related permissions, but remember that the user may also have rights to different roles too that have additional permissions.

    select a.name

    , a.type_desc

    , b.permission_name

    , b.state_desc

    , b.class_desc

    , object_name(b.major_id)

    from sys.database_principals a

    left join sys.database_permissions b on b.grantee_principal_id = a.principal_id

    Also, check out the scripts listed in this post:

    http://www.sqlservercentral.com/Forums/Topic745531-359-3.aspx



    Microsoft Certified Master - SQL Server 2008
    Follow me on twitter: @keith_tate

    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Thanks Keith,

    The script is giving database level permissions. I'm looking entire instance level.

    For example the user has read on one database and write on another database and has view definition on another database. I want see all permissions a user has on instance level.

    Please advise.

  • Mani-584606 (12/17/2013)


    Thanks Keith,

    The script is giving database level permissions. I'm looking entire instance level.

    For example the user has read on one database and write on another database and has view definition on another database. I want see all permissions a user has on instance level.

    Please advise.

    on the page Kieth pointed you to, there's this post:

    http://www.sqlservercentral.com/Forums/FindPost1268197.aspx

    which links to vikingDBA's script for what he described as an Instance Audit.

    http://www.sqlservercentral.com/Forums/Topic1226870-359-1.aspx#bm1230153

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply