Post reply

Query to find database User permissions

  • August 18, 2011 at 1:30 pm

    #242320

    Hi, Can someone help me with the query to find all the permissions for a particular database user? like any database roles assigned, individual object permissions for that user etc..

    Thanks

  • August 18, 2011 at 1:33 pm

    #1370347

    This might be overkill, but it should get you everything you need.

    http://jasonbrimhall.info/2010/03/19/security-audit/

    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

  • August 18, 2011 at 1:51 pm

    #1370366

    I use the following to generate scripts of the permissions for all users and roles in a database...

    /**********SET RESULTS TO TEXT TO ENSURE FULL OUTPUT******/

    /**

    NOTE: This script should be run once prior to a database restore to generate the

    appropriate permissions scripts. Set results to text and retain them

    Once the database is restored, the script must be run against the restored database,

    AGAIN, the results should be output to text. These results will include "drop schema"

    and "drop user" statements which must be copied and run against the restored database

    to drop the production users.

    so, you will run the script, restore the database, run the output of first script,

    then run the output of that script again... it's simpler than it sounds**/

    /*** NOTE- THIS SCRIPT RETAINS PERMISSONS BUT DOES NOT REPAIR ORPHANED USERS

    YOU MUST STILL RUN SP_CHANGE_USERS_LOGIN TO REPAIR ORPHANED USERS

    ***/

    SET NOCOUNT ON

    print'--paste these results into new query window and run with results to text,'

    print'--then execute the drop statement output again'

    print'--permissions script for' +db_name()+' on '+@@servername

    print ' '

    if ((select COUNT (name) from sys.database_principals where type='R' and is_fixed_role =0 and name!='public') >0)

    begin

    print '--recreate any user created roles'

    select 'create role ['+name+'] authorization [dbo]' from sys.database_principals where type='R' and is_fixed_role =0 and name!='public'

    end

    else

    begin

    print '--no user created roles to script'

    end

    print 'go'

    print'--grant users access'

    SELECT 'EXEC [sp_grantdbaccess] @loginame =['+[master].[dbo].[syslogins].[loginname]+'], @name_in_db =['+

    [sysusers].[name]+']'

    FROM [dbo].[sysusers]

    INNER JOIN [master].[dbo].[syslogins]

    ON [sysusers].[sid] = [master].[dbo].[syslogins].[sid]

    --WHERE [sysusers].[name]

    print 'go'

    PRINT '--add users to roles'

    select 'EXEC sp_addrolemember ' + '@rolename=['+r.name+ '], @membername= ['+ m.name+']'

    from sys.database_role_members rm

    join sys.database_principals r on rm.role_principal_id = r.principal_id

    join sys.database_principals m on

    rm.member_principal_id = m.principal_id

    where m.name!='dbo'

    order by r.name, m.name

    print 'go'

    print '--object level perms'

    select p.state_desc + ' ' + p.permission_name + ' ON [' + s.name +'].['+ o.name collate Latin1_general_CI_AS+ '] TO [' + u.name collate Latin1_general_CI_AS + ']' from sys.database_permissions p inner join sys.objects o on p.major_id = o.object_id inner join sys.schemas s on s.schema_id = o.schema_id inner join sys.database_principals u on p.grantee_principal_id = u.principal_id

    print 'go'

    print '--grant databasewide permissions'

    select p.state_desc + ' ' + p.permission_name +' TO [' + u.name collate Latin1_general_CI_AS + ']' from sys.database_permissions p inner join sys.database_principals u on p.grantee_principal_id = u.principal_id

    where p.class_desc='DATABASE'

    print 'go'

    PRINT'--list of users and schemas to drop'

    PRINT' '

    select 'select ''drop schema[''+name+'']'' from sys.schemas where name not in('

    select ''''+name+''',' from sys.schemas

    PRINT '''dropusername'')'

    select 'select ''drop user[''+name+'']'' from sysusers where name not in('

    select ''''+name+''',' from sysusers

    PRINT '''dropusername'')'

  • August 18, 2011 at 1:59 pm

    #1370378

    Thanks guys! The script works great.

  • August 18, 2011 at 2:23 pm

    #1370400

    Good to hear.

    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 5 posts - 1 through 4 (of 4 total)

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