script out database users for the selected database

  • zombi

    SSCrazy

    Points: 2605

    Hi All,

    This might be old question but i am not able to find answer for this.

    Actually i am looking for some script which can script out users for the selected database and i can run that script to restore the user permission into the database.

    When we restore the database from production to the development, users are also get copied from production to development. Are there any script by which i can script out the users from the development database and then restore that database from production. and once database restored from production to development i can just run the script and restore user permission.

    I want to use this for both windows and sql logins. Thanks.

  • Leo.Miller

    SSChampion

    Points: 12824

    It's been a while since I ran this, but it's one I wrote some time back for SQL 2005.

    select 'GRANT ' + permission_name + ' on ' + o.name + ' to ' + u.name

    from sys.sysobjects o

    join sys.database_permissions p on o.id = p.major_id

    join sys.sysusers u on u.uid = p.grantee_principal_id

    --where u.name in ('OptionalMane')

    order by u.name, o.name

    Cheers

    Leo

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

  • WayneS

    SSC Guru

    Points: 95341

    If you're talking about actually performing a database restore, then your biggest issue isn't the GRANT permissions. It's going to be missing/orphaned users.

    Missing users is obvious. Orphaned users is a little bit different - you might have the same user on the new server, but it will still be an orphaned user in the database because the SID is different. You can utilize sp_change_users_login 'report' to get a list of orphaned Windows users. However, every time that you need to restore the database to this lower environment, you will have to do this again.

    What I have done is to get sp_help_revlogin (available in posts on Microsoft's site). This will script out the logins from one server, and you can apply them to a different server. This includes specifying the SID from the source. Then, delete the users from the lower environment, and add them back in with this script. Now, when you restore the database, the SIDs will match, and you won't have all those orphaned users.

    Note that this script will also script out the password hash (for SQL logins) - not the actual password, but the hash of it. When you then add the user, it stores the hash - so the login will have the same password on the lower environment as the higher environment. This may or may not be desirable for you. But it is also a neat way to back up your logins without compromising the passwords.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • zombi

    SSCrazy

    Points: 2605

    Hi,

    I am getting the following error when i run the above script.

    Msg 451, Level 16, State 1, Line 1

    Cannot resolve collation conflict for column 1 in SELECT statement.

    Please advise. Thanks.

  • zombi

    SSCrazy

    Points: 2605

    HI Wayne,

    Actually i am here talking about scripting out users from the database not from the server.

    what i do each time i do a restore from production to development is

    Stqp1 -> Run sp_helpuser statement to get the user and permission information from the database

    Step2 -> Restore database

    Step3 -> Delete users from the database because restored database will contain production database users.(this is on the restored database - development database)

    Step4-> Restore back the old users to the development database from the Step1.

    Now here in step4 i have to run that for individual user manually. SO what i want is to create a script in the step 1 from the database. so that i can run that script in step 4 once the database restore.

    Please not i am not talking about orphaned users here. Thanks.

  • WayneS

    SSC Guru

    Points: 95341

    Does this get the information that you're looking for?

    SELECT 'IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ' + QuoteName(dp.name, char(39)) +

    ') CREATE USER ' + QuoteName(dp.name) +

    IsNull(' FOR LOGIN ' + QuoteName(sp.name),'') +

    IsNull(' WITH DEFAULT_SCHEMA = ' + QuoteName(dp.default_schema_name),'') + ';'

    FROM sys.database_principals dp

    LEFT JOIN sys.server_principals sp

    ON sp.sid = dp.sid

    WHERE dp.type like '[GUS]'

    SELECT dp.state_desc COLLATE SQL_Latin1_General_CP1_CI_AS + ' ' +

    dp.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS +

    ' ON ' + QuoteName(ss.name) + '.' + QuoteName(so.name) +

    ' TO ' + QuoteName(dp2.name) + ';',

    ss.name,

    so.name

    FROM sys.database_permissions dp

    JOIN sys.database_principals dp2

    ON dp2.principal_id = dp.grantee_principal_id

    JOIN sys.objects so

    ON so.object_id = dp.major_id

    JOIN sys.schemas ss

    ON ss.schema_id = so.schema_id

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • kyleheath33

    Ten Centuries

    Points: 1089

    I use this script for moving logins, first one creates a sp in the db and the next executes it with the passwords and user permissions.

    When you get the output from the sp just run that in the SQL destination.

    Kyle

  • Leo.Miller

    SSChampion

    Points: 12824

    zombi (8/31/2010)


    Hi,

    I am getting the following error when i run the above script.

    Msg 451, Level 16, State 1, Line 1

    Cannot resolve collation conflict for column 1 in SELECT statement.

    Please advise. Thanks.

    You need to compare the collations between the user database and master. If they are different you need to include a COLLATE clause in your select.

    Leo

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

  • zombi

    SSCrazy

    Points: 2605

    Hi Wayne,

    I ran the script,

    SELECT 'IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ' + QuoteName(dp.name, char(39)) +

    ') CREATE USER ' + QuoteName(dp.name) +

    IsNull(' FOR LOGIN ' + QuoteName(sp.name),'') +

    IsNull(' WITH DEFAULT_SCHEMA = ' + QuoteName(dp.default_schema_name),'') + ';'

    FROM sys.database_principals dp

    LEFT JOIN sys.server_principals sp

    ON sp.sid = dp.sid

    WHERE dp.type like '[GUS]'

    Which lists out all the users from the database which is good. But it doesn't restore the permissions.

    For Example, If i have a user A having DataReader Permission. Then once i restore from production to development, permission and user will be lost. With the above script i can restore the user but can't restore the permission. Is there any way i can do this? Thanks for your help.

  • zombi

    SSCrazy

    Points: 2605

    Hi Guys,

    Below combination worked for me. Just run the Script below for the individual database and copy the output and run into the new query window to restore users.

    SELECT 'IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ' + QuoteName(dp.name, char(39)) +

    ') CREATE USER ' + QuoteName(dp.name) +

    IsNull(' FOR LOGIN ' + QuoteName(sp.name),'') +

    IsNull(' WITH DEFAULT_SCHEMA = ' + QuoteName(dp.default_schema_name),'') + ';'

    FROM sys.database_principals dp

    LEFT JOIN sys.server_principals sp

    ON sp.sid = dp.sid

    WHERE dp.type like '[GUS]'

    GO

    SELECT 'exec sp_addrolemember ' + '''' + (r3.name) + '''' + ',' + '''' + (r2.name) + '''' + ';'

    FROM sys.database_role_members r1

    inner join sys.database_principals r2

    on r1.member_principal_id = r2.principal_id

    inner join sys.database_principals r3

    on r1.role_principal_id = r3.principal_id

    GO

  • pruthvirajgowda 8776

    Mr or Mrs. 500

    Points: 561

    try this ,

    it will extract the complete database permission

    the script is just too big to copy paste. make sure you are in the database.

    it will only create a temp table , so no need to worry

    https://gallery.technet.microsoft.com/Extract-Database-dfa53d5a

    Thank you

  • pruthvirajgowda 8776

    Mr or Mrs. 500

    Points: 561

    hi,

    i have written script , can you check .

    gallery.technet.microsoft.com/Extract-Database-dfa53d5a

    Thank you

  • Snargables

    SSCrazy Eights

    Points: 8669

    so combining the two will give u this and it worked for me. Run the below in the source database
    --step 1 -------------------------------------------------------------------------------
    --RUN THIS ON SOURCE DB
    --COPY OUTPUT THEN RUN OUTPUT ON NEW SERVER
    ----------------------------------------------------------------------------------------

    SELECT 'IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ' + QuoteName(dp.name, char(39)) +
    ') CREATE USER ' + QuoteName(dp.name) +
    IsNull(' FOR LOGIN ' + QuoteName(sp.name),'') +
    IsNull(' WITH DEFAULT_SCHEMA = ' + QuoteName(dp.default_schema_name),'') + ';'
    FROM sys.database_principals dp
    LEFT JOIN sys.server_principals sp
    ON sp.sid = dp.sid
    WHERE dp.type like '[GUS]'

    --step 2 -------------------------------------------------------------------------------
    --RUN THIS ON SOURCE DB
    --COPY OUTPUT THEN RUN OUTPUT ON NEW SERVER
    ----------------------------------------------------------------------------------------

    set nocount off

    IF OBJECT_ID(N'tempdb..##temp1') IS NOT NULL
      DROP TABLE ##temp1

    create table ##temp1(query varchar(1000))

    insert into ##temp1
    select 'use '+db_name() +';'

    insert into ##temp1
    select 'go'

    /*creating database roles*/
    insert into ##temp1
           select 'if DATABASE_PRINCIPAL_ID('''+name+''') is null
           exec sp_addrole '''+name+'''' from sysusers
    where issqlrole = 1 and (sid is not null and sid <> 0x0)

    /*creating application roles*/
    insert into ##temp1
           select 'if DATABASE_PRINCIPAL_ID('+char(39)+name+char(39)+')
           is null CREATE APPLICATION ROLE ['+name+'] WITH DEFAULT_SCHEMA = ['+
           default_schema_name+'], Password='+char(39)+'Pass$w0rd123'+char(39)+' ;'
    from sys.database_principals
    where type_desc='APPLICATION_ROLE'

    insert into ##temp1
           select
               case
                  when state_desc='GRANT_WITH_GRANT_OPTION'
                       then
                          substring (state_desc,0,6)+' '+permission_name+' to '+'['+USER_NAME(grantee_principal_id)+']'+' WITH GRANT OPTION ;'
                         
                       else
                          state_desc+' '+permission_name+' to '+'['+USER_NAME(grantee_principal_id)+']'+' ;'
           END
    from sys.database_permissions
    where class=0 and USER_NAME(grantee_principal_id) not in ('dbo','guest','sys','information_schema')

    insert into ##temp1
           select
               case
                  when state_desc='GRANT_WITH_GRANT_OPTION'
                     then
                         substring (state_desc,0,6)+' '+permission_name+' on '+OBJECT_SCHEMA_NAME(major_id)+'.['+OBJECT_NAME(major_id)
                         +'] to '+'['+USER_NAME(grantee_principal_id)+']'+' with grant option ;'
                      else
                         state_desc+' '+permission_name+' on '+OBJECT_SCHEMA_NAME(major_id)+'.['+OBJECT_NAME(major_id)
                         +'] to '+'['+USER_NAME(grantee_principal_id)+']'+' ;'
                end
    from sys.database_permissions where class=1 and USER_NAME(grantee_principal_id) not in ('public');

    insert into ##temp1
            select
               case
                   when state_desc='GRANT_WITH_GRANT_OPTION'
                      then
                         substring (state_desc,0,6)+' '+permission_name+' ON schema::['+sa.name+
                         '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                       else
                         state_desc+' '+permission_name+' ON schema::['+sa.name+
                         '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                       COLLATE LATIN1_General_CI_AS
                 end
    from sys.database_permissions dp inner join sys.schemas sa on
    sa.schema_id = dp.major_id where dp.class=3

    insert into ##temp1
           select
               case
                   when state_desc='GRANT_WITH_GRANT_OPTION'
                   then
                      substring (state_desc,0,6)+' '+permission_name+' ON APPLICATION ROLE::['+sa.name+
                      '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                   else
                      state_desc+' '+permission_name+' ON APPLICATION ROLE::['+sa.name+
                      '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                      COLLATE LATIN1_General_CI_AS
             end
    from sys.database_permissions dp inner join sys.database_principals sa on
    sa.principal_id = dp.major_id where dp.class=4 and sa.type='A'

    insert into ##temp1
            select
               case
                  when state_desc='GRANT_WITH_GRANT_OPTION'
                   then
                     substring (state_desc,0,6)+' '+permission_name+' ON ROLE::['+sa.name+
                     '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                   else
                     state_desc+' '+permission_name+' ON ROLE::['+sa.name+
                      '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                      COLLATE LATIN1_General_CI_AS
                   end
    from sys.database_permissions dp inner join
    sys.database_principals sa on sa.principal_id = dp.major_id
    where dp.class=4 and sa.type='R'

    insert into ##temp1
            select
                case
                   when state_desc='GRANT_WITH_GRANT_OPTION'
                       then
                         substring (state_desc,0,6)+' '+permission_name+' ON ASSEMBLY::['+sa.name+
                          '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                       else
                          state_desc+' '+permission_name+' ON ASSEMBLY::['+sa.name+
                          '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                          COLLATE LATIN1_General_CI_AS
                 end
    from sys.database_permissions dp inner join sys.assemblies sa on
    sa.assembly_id = dp.major_id
    where dp.class=5

    insert into ##temp1
           select
               case
                   when state_desc='GRANT_WITH_GRANT_OPTION'
                   then
                      substring (state_desc,0,6)+' '+permission_name+' ON type::['
                      +SCHEMA_NAME(schema_id)+'].['+sa.name+
                      '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                   else
                      state_desc+' '+permission_name+' ON type::['
                      +SCHEMA_NAME(schema_id)+'].['+sa.name+
                      '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                      COLLATE LATIN1_General_CI_AS
                    end
    from sys.database_permissions dp inner join sys.types sa on
    sa.user_type_id = dp.major_id
    where dp.class=6

    insert into ##temp1
            select
               case
                  when state_desc='GRANT_WITH_GRANT_OPTION'
                   then
                      substring (state_desc,0,6)+' '+permission_name+' ON XML SCHEMA COLLECTION::['+
                      SCHEMA_NAME(SCHEMA_ID)+'].['+sa.name+'] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                   else
                      state_desc+' '+permission_name+' ON XML SCHEMA COLLECTION::['+
                      SCHEMA_NAME(SCHEMA_ID)+'].['+sa.name+'] to ['+user_name(dp.grantee_principal_id)+'];'
                      COLLATE LATIN1_General_CI_AS
                end
    from sys.database_permissions dp inner join sys.xml_schema_collections sa on
    sa.xml_collection_id = dp.major_id
    where dp.class=10

    insert into ##temp1
           select
               case
                  when state_desc='GRANT_WITH_GRANT_OPTION'
                  then
                     substring (state_desc,0,6)+' '+permission_name+' ON message type::['+sa.name+
                      '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                   else
                      state_desc+' '+permission_name+' ON message type::['+sa.name+
                      '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                      COLLATE LATIN1_General_CI_AS
                   end
    from sys.database_permissions dp inner join sys.service_message_types sa on
    sa.message_type_id = dp.major_id
    where dp.class=15

    insert into ##temp1
            select
                case
                   when state_desc='GRANT_WITH_GRANT_OPTION'
                    then
                       substring (state_desc,0,6)+' '+permission_name+' ON contract::['+sa.name+
                       '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                    else
                       state_desc+' '+permission_name+' ON contract::['+sa.name+
                       '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                       COLLATE LATIN1_General_CI_AS
                end
    from sys.database_permissions dp inner join sys.service_contracts sa on
    sa.service_contract_id = dp.major_id
    where dp.class=16

    insert into ##temp1
            select
               case
                   when state_desc='GRANT_WITH_GRANT_OPTION'
                   then
                      substring (state_desc,0,6)+' '+permission_name+' ON SERVICE::['+sa.name+
                       '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                    else
                       state_desc+' '+permission_name+' ON SERVICE::['+sa.name+
                       '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                       COLLATE LATIN1_General_CI_AS
                end
    from sys.database_permissions dp inner join sys.services sa on
    sa.service_id = dp.major_id
    where dp.class=17

    insert into ##temp1
            select
                case
                    when state_desc='GRANT_WITH_GRANT_OPTION'
                    then
                        substring (state_desc,0,6)+' '+permission_name+' ON REMOTE SERVICE BINDING::['+sa.name+
                        '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                     else
                        state_desc+' '+permission_name+' ON REMOTE SERVICE BINDING::['+sa.name+
                        '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                        COLLATE LATIN1_General_CI_AS
                 end
    from sys.database_permissions dp inner join sys.remote_service_bindings sa on
    sa.remote_service_binding_id = dp.major_id
    where dp.class=18

    insert into ##temp1
            select
                case
                   when state_desc='GRANT_WITH_GRANT_OPTION'
                    then
                       substring (state_desc,0,6)+' '+permission_name+' ON route::['+sa.name+
                       '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                    else
                        state_desc+' '+permission_name+' ON route::['+sa.name+
                        '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                       COLLATE LATIN1_General_CI_AS
                 end
    from sys.database_permissions dp inner join sys.routes sa on
    sa.route_id = dp.major_id
    where dp.class=19

    insert into ##temp1
            select
               case
                   when state_desc='GRANT_WITH_GRANT_OPTION'
                   then
                      substring (state_desc,0,6)+' '+permission_name+' ON FULLTEXT CATALOG::['+sa.name+
                      '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                   else
                       state_desc+' '+permission_name+' ON FULLTEXT CATALOG::['+sa.name+
                       '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                       COLLATE LATIN1_General_CI_AS
                 end
    from sys.database_permissions dp inner join sys.fulltext_catalogs sa on
    sa.fulltext_catalog_id = dp.major_id
    where dp.class=23

    insert into ##temp1
            select
               case
                   when state_desc='GRANT_WITH_GRANT_OPTION'
                   then
                       substring (state_desc,0,6)+' '+permission_name+' ON SYMMETRIC KEY::['+sa.name+
                       '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                   else
                       state_desc+' '+permission_name+' ON SYMMETRIC KEY::['+sa.name+
                       '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                       COLLATE LATIN1_General_CI_AS
                   end
    from sys.database_permissions dp inner join sys.symmetric_keys sa on
    sa.symmetric_key_id = dp.major_id
    where dp.class=24

    insert into ##temp1
            select
                case
                   when state_desc='GRANT_WITH_GRANT_OPTION'
                   then
                       substring (state_desc,0,6)+' '+permission_name+' ON certificate::['+sa.name+
                       '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                    else
                        state_desc+' '+permission_name+' ON certificate::['+sa.name+
                        '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                        COLLATE LATIN1_General_CI_AS
                end
    from sys.database_permissions dp inner join sys.certificates sa on
    sa.certificate_id = dp.major_id
    where dp.class=25

    insert into ##temp1
           select
               case
                  when state_desc='GRANT_WITH_GRANT_OPTION'
                  then
                      substring (state_desc,0,6)+' '+permission_name+' ON ASYMMETRIC KEY::['+sa.name+
                      '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                   else
                      state_desc+' '+permission_name+' ON ASYMMETRIC KEY::['+sa.name+
                       '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                       COLLATE LATIN1_General_CI_AS
            end
    from sys.database_permissions dp inner join sys.asymmetric_keys sa on
    sa.asymmetric_key_id = dp.major_id
    where dp.class=26

    insert into ##temp1
           select 'exec sp_addrolemember ''' +p.NAME+''','+'['+m.NAME+']'+' ;'
    FROM sys.database_role_members rm
    JOIN sys.database_principals p
    ON rm.role_principal_id = p.principal_id
    JOIN sys.database_principals m
    ON rm.member_principal_id = m.principal_id
    where m.name not like 'dbo';

    select * from ##temp1

  • pruthvirajgowda 8776

    Mr or Mrs. 500

    Points: 561

    glad it worked for you. thank you for reviewing the script

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

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