Post reply

List all users on SQL instance and their privilages

  • August 26, 2010 at 8:42 am

    #228365

    Is there a way to get info on all the users and their associated privileges ?

    thanks in advance

  • August 26, 2010 at 8:55 am

    #1212363

    Something like this executed against that DB:

    select * from sys.database_permissions d inner join sys.database_principals p

    on grantee_principal_id = principal_id

    inner join sys.objects o on major_id = object_id

    order by p.name, o.name

  • August 26, 2010 at 8:56 am

    #1212366

    Have a read about sys.sysprotects and sp_helprotect for permissions GRANTed to users on objects. As for server level permissions (if you require that), I'm not sure, but you'll be able to find out if you do enough searching.

    John

  • August 26, 2010 at 9:03 am

    #1212371

    If you look in the scripts section (here at SSC) there are several scripts that will help you get the full permission set for all users.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

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

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