locked
Script to migrate database level security in sql server. RRS feed

  • Question

  • Hi,

    I am pretty new to sql platform as DBA. I have to restore one of the prod database to Dev however I need to keep all the login and security as it is as earlier in the DEV. For this I need to script login along with all permission of Dev database before I restore Prod one and later need to run the script to get all login back along with the same  permission. I am not being able to get proper  script to be run in the DEV to get all login scripted. Please any one help me. 

    Wednesday, September 10, 2014 8:17 PM

Answers

  • Hi,

    Run this script on the Dev database and copy the result set to the text or word document. Perform the restore and run the copied script back on the restored database. This will put all the database users back in place.

    SET NOCOUNT ON
    
     
    
    DECLARE @Database varchar(max)
    
    SET @Database = 'Database_Name'-- set the database name here
    
     
    
    IF  EXISTS (SELECT * FROM SYS.SYSDATABASES WHERE name = @Database)
    
    EXEC('USE ' + @Database + ';
    
    DECLARE @temp table(Extract Varchar(MAX))
    
    INSERT INTO @temp (Extract)
    
    SELECT ''EXEC( ''''USE ' + @Database + '; IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'''''''''' + name + '''''''''')IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'''''''''' + name + '''''''''') DROP USER [''  + name + '']''''); ''
    
    FROM sys.sysusers WHERE islogin = 1 AND hasdbaccess = 1 AND name NOT IN (''dbo'')
    
    INSERT INTO @temp (Extract) 
    
    SELECT ''EXEC( ''''USE ' + @Database + '; IF NOT EXISTS 
    
    (SELECT * FROM sys.database_principals WHERE name = N'''''''''' + dp.name + '''''''''') 
    
    CREATE USER ['' + dp.name + ''] FOR LOGIN ['' + sp.name + '']''''); ''
    
    FROM sys.server_principals sp 
    
    JOIN sys.database_principals dp ON (sp.sid = dp.sid) 
    
    AND dp.name NOT IN (''dbo'')
    
     
    
    INSERT INTO @temp (Extract) 
    
    SELECT ''EXEC( ''''USE ' + @Database + '; IF NOT EXISTS
    
     (SELECT * FROM sys.database_principals WHERE name = N'''''''''' + dp.name + '''''''''')
    
      CREATE USER ['' + dp.name + ''] WITHOUT LOGIN '''');  ''
    
    FROM sys.database_principals dp left 
    
    JOIN sys.server_principals sp ON (sp.sid = dp.sid) 
    
    where dp.principal_id between 5 and 16383
    
     
    
    INSERT INTO @temp (Extract) 
    
    SELECT ''EXEC( ''''USE ' + @Database + '; EXEC sp_addrolemember '''''''''' + User_Name([groupuid]) + '''''''''', '''''''''' + User_Name([memberuid]) + ''''''''''''''); ''
    
    FROM sys.sysmembers WHERE User_Name([memberuid]) NOT IN (''dbo'')
    
     
    
    INSERT INTO @temp (Extract) 
    
    SELECT ''EXEC( ''''USE ' + @Database + '; ''
    
     + CASE [a].[state_desc] WHEN ''GRANT_WITH_GRANT_OPTION'' THEN ''GRANT '' ELSE [a].[state_desc]  END
    
     + '' '' 
    
     + [a].[permission_name] + CASE class WHEN 1 THEN '' ON ['' 
    
     + [c].[name] 
    
     + ''].[''
    
     + Object_Name([a].[major_id]) + ''] '' ELSE '''' END + '' TO ['' 
    
     + User_Name([a].[grantee_principal_id]) 
    
     + ''] '' + CASE [a].[state_desc] WHEN ''GRANT_WITH_GRANT_OPTION'' THEN '' WITH GRANT OPTION'' ELSE '''' END 
    
     + ''''''); '' COLLATE Latin1_General_CI_AS AS [SQL]
    
    FROM sys.database_permissions a 
    
     inner join [sys].[all_objects] b 
    
      ON [a].[major_id] = [b].[object_id]
    
     inner join [sys].[schemas] c
    
      ON [b].[schema_id] = [c].[schema_id]
    
     
    
     INSERT INTO @temp (Extract)  
    
     SELECT ''EXEC( ''''USE ' + @Database + '; ''
    
     + [a].[state_desc] 
    
     + '' '' 
    
     + [a].[permission_name] 
    
     +  '' TO [''  
    
     + USER_NAME([a].[grantee_principal_id])
    
     + ''] '''');''
    
     from sys.database_permissions  a 
    
     where a.class=0
    
     
    
     INSERT INTO @temp (Extract)  
    
     SELECT ''EXEC( ''''USE ' + @Database + '; ''
    
     + [a].[state_desc] 
    
     + '' '' 
    
     + [a].[permission_name] 
    
     + '' ON SCHEMA ::''
    
     + '' [''
    
     + SCHEMA_NAME([a].[major_id]) +
    
     + ''] TO [''  
    
     + USER_NAME([a].[grantee_principal_id])
    
     + ''] '''');''
    
     from sys.database_permissions  a 
    
     where a.class=3
    
     
    
    SELECT Extract AS [Extract] FROM @temp
    
    GO
    
    ')
    
    ELSE
    
    SELECT '--NO'
    
    GO
    

    Many Thanks

    Bhanu

    Wednesday, September 10, 2014 9:45 PM

All replies

  • Hi,

    Run this script on the Dev database and copy the result set to the text or word document. Perform the restore and run the copied script back on the restored database. This will put all the database users back in place.

    SET NOCOUNT ON
    
     
    
    DECLARE @Database varchar(max)
    
    SET @Database = 'Database_Name'-- set the database name here
    
     
    
    IF  EXISTS (SELECT * FROM SYS.SYSDATABASES WHERE name = @Database)
    
    EXEC('USE ' + @Database + ';
    
    DECLARE @temp table(Extract Varchar(MAX))
    
    INSERT INTO @temp (Extract)
    
    SELECT ''EXEC( ''''USE ' + @Database + '; IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'''''''''' + name + '''''''''')IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'''''''''' + name + '''''''''') DROP USER [''  + name + '']''''); ''
    
    FROM sys.sysusers WHERE islogin = 1 AND hasdbaccess = 1 AND name NOT IN (''dbo'')
    
    INSERT INTO @temp (Extract) 
    
    SELECT ''EXEC( ''''USE ' + @Database + '; IF NOT EXISTS 
    
    (SELECT * FROM sys.database_principals WHERE name = N'''''''''' + dp.name + '''''''''') 
    
    CREATE USER ['' + dp.name + ''] FOR LOGIN ['' + sp.name + '']''''); ''
    
    FROM sys.server_principals sp 
    
    JOIN sys.database_principals dp ON (sp.sid = dp.sid) 
    
    AND dp.name NOT IN (''dbo'')
    
     
    
    INSERT INTO @temp (Extract) 
    
    SELECT ''EXEC( ''''USE ' + @Database + '; IF NOT EXISTS
    
     (SELECT * FROM sys.database_principals WHERE name = N'''''''''' + dp.name + '''''''''')
    
      CREATE USER ['' + dp.name + ''] WITHOUT LOGIN '''');  ''
    
    FROM sys.database_principals dp left 
    
    JOIN sys.server_principals sp ON (sp.sid = dp.sid) 
    
    where dp.principal_id between 5 and 16383
    
     
    
    INSERT INTO @temp (Extract) 
    
    SELECT ''EXEC( ''''USE ' + @Database + '; EXEC sp_addrolemember '''''''''' + User_Name([groupuid]) + '''''''''', '''''''''' + User_Name([memberuid]) + ''''''''''''''); ''
    
    FROM sys.sysmembers WHERE User_Name([memberuid]) NOT IN (''dbo'')
    
     
    
    INSERT INTO @temp (Extract) 
    
    SELECT ''EXEC( ''''USE ' + @Database + '; ''
    
     + CASE [a].[state_desc] WHEN ''GRANT_WITH_GRANT_OPTION'' THEN ''GRANT '' ELSE [a].[state_desc]  END
    
     + '' '' 
    
     + [a].[permission_name] + CASE class WHEN 1 THEN '' ON ['' 
    
     + [c].[name] 
    
     + ''].[''
    
     + Object_Name([a].[major_id]) + ''] '' ELSE '''' END + '' TO ['' 
    
     + User_Name([a].[grantee_principal_id]) 
    
     + ''] '' + CASE [a].[state_desc] WHEN ''GRANT_WITH_GRANT_OPTION'' THEN '' WITH GRANT OPTION'' ELSE '''' END 
    
     + ''''''); '' COLLATE Latin1_General_CI_AS AS [SQL]
    
    FROM sys.database_permissions a 
    
     inner join [sys].[all_objects] b 
    
      ON [a].[major_id] = [b].[object_id]
    
     inner join [sys].[schemas] c
    
      ON [b].[schema_id] = [c].[schema_id]
    
     
    
     INSERT INTO @temp (Extract)  
    
     SELECT ''EXEC( ''''USE ' + @Database + '; ''
    
     + [a].[state_desc] 
    
     + '' '' 
    
     + [a].[permission_name] 
    
     +  '' TO [''  
    
     + USER_NAME([a].[grantee_principal_id])
    
     + ''] '''');''
    
     from sys.database_permissions  a 
    
     where a.class=0
    
     
    
     INSERT INTO @temp (Extract)  
    
     SELECT ''EXEC( ''''USE ' + @Database + '; ''
    
     + [a].[state_desc] 
    
     + '' '' 
    
     + [a].[permission_name] 
    
     + '' ON SCHEMA ::''
    
     + '' [''
    
     + SCHEMA_NAME([a].[major_id]) +
    
     + ''] TO [''  
    
     + USER_NAME([a].[grantee_principal_id])
    
     + ''] '''');''
    
     from sys.database_permissions  a 
    
     where a.class=3
    
     
    
    SELECT Extract AS [Extract] FROM @temp
    
    GO
    
    ')
    
    ELSE
    
    SELECT '--NO'
    
    GO
    

    Many Thanks

    Bhanu

    Wednesday, September 10, 2014 9:45 PM
  • HI Bhanu 

    I appreciate your effort. I am running this script in Dev and always getting "no" in the result set. Could you please review it once as whats wrong with it.

    Monday, October 20, 2014 6:12 PM
  • Hi Prasant,

    Did you made sure that you replace the 'Database_Name' string at the beginning of the script with the name of the database that you are trying to capture the permissions from. I think this is the problem in your case

    Many Thanks,

    Bhanu

    Monday, October 27, 2014 10:59 PM