i want script to know all logins with with there server roles, database and object level

  • ravishankar.yedoti

    Right there with Babe

    Points: 738

    i want script to know all logins with with there server roles, database and object level permisions

  • Ron McCullough

    SSC Guru

    Points: 63877

    See if this gives you the information you seek.

    sp_helplogins

    For your information it is located in the Master database.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • ravishankar.yedoti

    Right there with Babe

    Points: 738

    sp_helplogins will give logins with server level permissions only........... i want to know what permission's they have at database level and object level. (with single stored procedure ...... i want all the information). i want to put that in a table for reference ...........

    bitbucket-25253 (8/19/2011)


    See if this gives you the information you seek.

    sp_helplogins

    For your information it is located in the Master database.

  • ravishankar.yedoti

    Right there with Babe

    Points: 738

    sp_helplogins will give logins with server level permissions only........... i want to know what permission's they have at database level and object level. (with single stored procedure ...... i want all the information). i want to put that in a table for reference ...........

  • NJ-DBA

    SSChampion

    Points: 13832

    ravishankar.yedoti (8/19/2011)


    sp_helplogins will give logins with server level permissions only........... i want to know what permission's they have at database level and object level. (with single stored procedure ...... i want all the information). i want to put that in a table for reference ...........

    this seems to be a really common question lately... this is how I do it.. no warranties, dont claim it's perfect, but it works for me... please let me know if it works for you too:

    /**********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'')'

    --grant execute to db_executortest

Viewing 5 posts - 1 through 5 (of 5 total)

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