Migrate all logins and Database Security

Applicability:

SQL Server 2000: N/A

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Tested

SQL Server 2014: Tested

SQL Server 2016: Tested

SQL Server 2017: Not Tested

Credits:

Author: ChillyDBA

Date: 6 Jun 2020

Description

Generates TSQL Scripts to recreate server logins and database security for a single database.

Only server logins that have mapping into the database are scripted

All passwords are scripted out in encrypted form

There are more instructions in the script comment header.

GENERATE script on: SOURCE SERVER (per USER database to be migrated)

EXECUTE script on: DESTINATION SERVER (per USER database to be migrated)

Code

SET NOCOUNT ON--******************************************* --**** RUN THIS ON THE SOURCE SERVER *** --******************************************* /* Purpose: Originally to create scripts to recreate security for the REFERENCE DB when it was being rationalised/migrated but can be used for any DB Scope includes: Create server logins corresponding to DB users, inclusing passwords NOTE: Default database is overridded to be [master] Create DB users, including remapping orphaned users Create DB roles Create DB Schemas Map DB users to roles Assign permissions to DB roles (with a check to report if an object is missing) Assign permissions on User Defined Types Assign DB level permisisons to users Output Data Each data set is populated from the viewpoint of a database, and all active databases are iterated over Each data set includes key attributes on the subject item, plus the TSQL code to recreate the item on any server/database ServerLoginsForDB - all server logins tied to active database users per database. Duplicates will be elimiated when transferring into Swing Server cetnral DBA_Control DB DatabaseUsers - all database users per database. Duplicates will be elimiated when transferring into Swing Server cetnral DBA_Control DB NOTE: Orphan users (with no server login) and non-mapped (with mis-matched SID) will be identified also DatabaseRoles - all non-system database roles defined in each database DatabaseSchemas - all non-system schemas defined in each database DatabaseRoleMembers - mapping of user to database role, including system roles DatabaseObjectPermissions - permissions granted to both users and roles against Tables, SPs and Views in each DB DatabaseLevelPermissions - permissions granted to both users and roles against the database itself (eg CONNECT, VIEW_DEFINITION) SchemaLevelPermissions - permissions granted to both users and roles against schemas in each database (not used very widely) Author: Originally wsoranno@winona.edu and choffman Augmented by ChillyDBA History: 6 Jun 2020 - Initial Issue - rewritten to remove cursor, use a temp table and extend to include login/password plus a formatting cleanup for readability *//***************************************************************** *** Variable Declarations *** ******************************************************************/DECLARE--general use @SQLStatement VARCHAR(MAX),@Sort INT-- loop variables ,@DatabaseName NVARCHAR(256),@ServerName NVARCHAR(256),@ServerLoginID INT,@CRLF VARCHAR(10),@TAB VARCHAR(10)SELECT @Servername = @@ServerName,@DatabaseName = DB_NAME()SELECT @CRLF = CHAR(13) + CHAR(10),@TAB = CHAR(9)/***************************************************************** *** Temp Table Definitions *** ******************************************************************/IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE name LIKE '#ServerLoginsForDB%' AND type = 'u')DROP TABLE #ServerLoginsForDBCREATE TABLE #ServerLoginsForDB(ServerLoginID INT IDENTITY(1,1),ServerName NVARCHAR(256),DatabaseName NVARCHAR(256),LoginSID VARBINARY(85),LoginName NVARCHAR(256),LoginType VARCHAR(100),LoginDisabled INT,DefaultDB NVARCHAR(256),HasAccess INT,DateCreated DATETIME,DateModified DATETIME,CredentialID INT,IsDenied INT,Is_Policy_Checked VARCHAR (MAX),Is_Expiration_Checked VARCHAR (MAX),PasswordHash VARCHAR (MAX),CreateStatementText VARCHAR(MAX))IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE name LIKE '#DatabaseUsers%' AND type = 'u')DROP TABLE #DatabaseUsersCREATE TABLE #DatabaseUsers(DBUserID INT IDENTITY(1,1),ServerName NVARCHAR(256),DatabaseName NVARCHAR(256),DBUserName NVARCHAR(256),DBUserSID VARBINARY(85),CreateStatementText VARCHAR(MAX),UserHasNoServerLogin INT,UserRequiresSIDFix INT)IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE name LIKE '#DatabaseRoles%' AND type = 'u')DROP TABLE #DatabaseRolesCREATE TABLE #DatabaseRoles(DBRoleID INT IDENTITY(1,1),ServerName NVARCHAR(256),DatabaseName NVARCHAR(256),DatabaseRoleName NVARCHAR(256),DateCreated DATETIME,DateModified DATETIME,CreateStatementText VARCHAR(MAX))IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE name LIKE '#DatabaseSchemas%' AND type = 'u')DROP TABLE #DatabaseSchemasCREATE TABLE #DatabaseSchemas(DBSchemaID INT IDENTITY(1,1),ServerName NVARCHAR(256),DatabaseName NVARCHAR(256),DatabaseSchemaName NVARCHAR(256),CreateStatementText VARCHAR(MAX))IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE name LIKE '#DatabaseRoleMembers%' AND type = 'u')DROP TABLE #DatabaseRoleMembersCREATE TABLE #DatabaseRoleMembers(DBRoleMemberID INT IDENTITY(1,1),ServerName NVARCHAR(256),DatabaseName NVARCHAR(256),DatabaseRoleName NVARCHAR(256),DatabaseUserName NVARCHAR(256),CreateStatementText VARCHAR(MAX))IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE name LIKE '#DatabaseObjectPermissions%' AND type = 'u')DROP TABLE #DatabaseObjectPermissionsCREATE TABLE #DatabaseObjectPermissions(DBObjectPermission INT IDENTITY(1,1),ServerName NVARCHAR(256),DatabaseName NVARCHAR(256),GrantType VARCHAR(MAX),GrantPermissionType VARCHAR(MAX),GrantedByUser VARCHAR(MAX),GrantedToUser VARCHAR(MAX),UserType VARCHAR(MAX),GrantedOnObject VARCHAR(MAX),ObjectType VARCHAR(MAX),CreateStatementText VARCHAR(MAX))IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE name LIKE '#DatabaseLevelPermissions%' AND type = 'u')DROP TABLE #DatabaseLevelPermissionsCREATE TABLE #DatabaseLevelPermissions(DBLevelPermissionID INT IDENTITY(1,1),ServerName NVARCHAR(256),DatabaseName NVARCHAR(256),GrantType VARCHAR(MAX),GrantPermissionType VARCHAR(MAX),GrantedByUser VARCHAR(MAX),GrantedToUser VARCHAR(MAX),UserType VARCHAR(MAX),ObjectType VARCHAR(MAX),CreateStatementText VARCHAR(MAX))IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE name LIKE '#SchemaLevelPermissions%' AND type = 'u')DROP TABLE #SchemaLevelPermissionsCREATE TABLE #SchemaLevelPermissions(SchemaLevelPermissionID INT IDENTITY(1,1),ServerName NVARCHAR(256),DatabaseName NVARCHAR(256),GrantType VARCHAR(MAX),GrantPermissionType VARCHAR(MAX),GrantedByUser VARCHAR(MAX),GrantedToUser VARCHAR(MAX),UserType VARCHAR(MAX),ObjectName VARCHAR(MAX),ObjectType VARCHAR(MAX),CreateStatementText VARCHAR(MAX))IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE name like '#Results%' AND type = 'U')DROP TABLE #ResultsCREATE TABLE #Results(ResultID INT IDENTITY(1,1),ResultText VARCHAR(MAX))/*********************************************************//********* START GET SERVER LOGINS *********//*********************************************************/SELECT @SQLStatement = ''SELECT @SQLStatement = @SQLStatement +' USE [' + @DatabaseName + '] DECLARE @name NVARCHAR(256) ,@type VARCHAR (MAX) ,@hasaccess INT ,@denylogin INT ,@is_disabled INT ,@PWD_VARBINARY VARBINARY (MAX) ,@PWD_string VARCHAR (MAX) ,@SID_VARBINARY VARBINARY (MAX) ,@SID_string VARCHAR (MAX) ,@tmpstr VARCHAR (MAX) ,@is_policy_checked VARCHAR (MAX) ,@is_expiration_checked VARCHAR (MAX) ,@defaultdb NVARCHAR(256) -- loop variables ,@ServerLoginID INT INSERT #ServerLoginsForDB ( ServerName ,DatabaseName ,LoginSID ,LoginName ,LoginType ,LoginDisabled ,DefaultDB ,HasAccess ,DateCreated ,DateModified ,CredentialID ,IsDenied ,Is_Policy_Checked ,Is_Expiration_Checked ,PasswordHash ,CreateStatementText ) SELECT ''' + @Servername + ''' AS ServerName ,''' + @DatabaseName + ''' AS DatabaseName ,p.sid AS LoginSid ,p.name AS LoginName ,p.type_desc AS LoginType ,p.is_disabled AS LoginDisabled ,p.default_database_name AS DefaultDB ,l.hasaccess AS HasAccess ,p.create_date AS DateCreated ,p.modify_date As DateModified ,p.credential_id AS CredentialID ,l.denylogin AS IsDenied ,NULL AS Is_Policy_Checked ,NULL AS Is_Expiration_Checked ,NULL AS PasswordHash ,NULL AS CreateStatementText FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name COLLATE SQL_Latin1_General_CP1_CI_AI ) INNER JOIN sysusers s on p.sid = s.sid WHERE p.type IN ( ''S'', ''G'', ''U'' ) AND p.name COLLATE SQL_Latin1_General_CP1_CI_AI <> ''sa'' ORDER BY p.name -- lopp through all serverlogis in scope for this DB and construct the CREATE statement SELECT @ServerLoginID = MIN(ServerLoginID) FROM #ServerLoginsForDB WHERE ServerName = ''' + @Servername + ''' AND DatabaseName = ''' + @DatabaseName + ''' WHILE @ServerLoginID IS NOT NULL BEGIN SELECT @SID_VARBINARY = LoginSID ,@name = LoginName ,@type = LoginType ,@is_disabled = LoginDisabled ,@defaultdb = DefaultDB ,@hasaccess = HasAccess ,@denylogin = IsDenied ,@is_policy_checked = CASE Is_Policy_Checked WHEN 1 THEN ''ON'' WHEN 0 THEN ''OFF'' ELSE NULL END ,@is_expiration_checked = CASE Is_Expiration_Checked WHEN 1 THEN ''ON'' WHEN 0 THEN ''OFF'' ELSE NULL END FROM #ServerLoginsForDB WHERE ServerLoginID = @ServerLoginID SELECT @tmpstr = '''' SELECT @tmpstr = ''IF NOT EXISTS (SELECT 1 FROM master..syslogins WHERE name = '''''' + @name + '''''') '' IF (@type IN ( ''WINDOWS_GROUP'', ''WINDOWS_LOGIN'')) BEGIN -- NT authenticated account/group SET @tmpstr = @tmpstr + ''CREATE LOGIN '' + QUOTENAME( @name ) + '' FROM WINDOWS WITH DEFAULT_DATABASE = [master]'' END ELSE BEGIN -- SQL Server authentication -- obtain password and sid SET @PWD_VARBINARY = CAST( LOGINPROPERTY( @name, ''PasswordHash'' ) AS VARBINARY (256) ) EXEC sp_hexadecimal @PWD_VARBINARY, @PWD_string OUT EXEC sp_hexadecimal @SID_VARBINARY,@SID_string OUT --override default database to master (as the target server may not have the database and master is MS best practice anyway) SET @tmpstr = @tmpstr +''CREATE LOGIN '' + QUOTENAME( @name ) + '' WITH PASSWORD = '' + @PWD_string + '' HASHED, SID = '' + @SID_string + '', DEFAULT_DATABASE = [master]'' --SET @tmpstr = ''CREATE LOGIN '' + QUOTENAME( @name ) + '' WITH PASSWORD = '' + @PWD_string + '' HASHED, SID = '' + @SID_string + '', DEFAULT_DATABASE = ['' + @defaultdb + '']'' IF ( @is_policy_checked IS NOT NULL ) BEGIN SET @tmpstr = @tmpstr + '', CHECK_POLICY = '' + @is_policy_checked END IF ( @is_expiration_checked IS NOT NULL ) BEGIN SET @tmpstr = @tmpstr + '', CHECK_EXPIRATION = '' + @is_expiration_checked END END IF (@denylogin = 1) BEGIN -- login is denied access SET @tmpstr = @tmpstr + ''; DENY CONNECT SQL TO '' + QUOTENAME( @name ) END ELSE IF (@hasaccess = 0) BEGIN -- login exists but does not have access SET @tmpstr = @tmpstr + ''; REVOKE CONNECT SQL TO '' + QUOTENAME( @name ) END IF (@is_disabled = 1) BEGIN -- login is disabled SET @tmpstr = @tmpstr + ''; ALTER LOGIN '' + QUOTENAME( @name ) + '' DISABLE'' END UPDATE #ServerLoginsForDB SET PasswordHash = @PWD_String ,CreateStatementText = @tmpstr WHERE ServerLoginID = @ServerLoginID SELECT @ServerLoginID = MIN(ServerLoginID) FROM #ServerLoginsForDB WHERE ServerName = ''' + @Servername + ''' AND DatabaseName = ''' + @DatabaseName + ''' AND ServerLoginID > @ServerLoginID END 'EXEC(@SqlStatement)/*********************************************************//********* END GET SERVER LOGINS *********//*********************************************************//*********************************************************//********* START GET DB USERS *********//*********************************************************/SELECT @SQLStatement = ''SELECT @SQLStatement = @SQLStatement +' USE [' + @DatabaseName + '] INSERT #DatabaseUsers ( ServerName ,DatabaseName ,DBUserName ,DBUserSID ,CreateStatementText ,UserHasNoServerLogin ,UserRequiresSIDFix ) SELECT ''' + @Servername + ''' AS ServerName ,''' + @DatabaseName + ''' AS DatabaseName ,p.[name] AS DBUserName ,p.[SID] AS DBUserSID ,''IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = '' + SPACE(1) + '''''''' + p.[name] + '''''''' + '') BEGIN CREATE USER '' + SPACE(1) + QUOTENAME(p.[name]) + '' FOR LOGIN '' + QUOTENAME(CASE WHEN suser_sname(p.[sid]) IS NULL THEN p.[name] ELSE suser_sname(p.[sid]) END) + '' WITH DEFAULT_SCHEMA = '' + QUOTENAME(ISNULL(p.[default_schema_name], ''dbo'')) + SPACE(1) + ''END; '' AS CreateStatementText ,CASE WHEN sp1.name IS NULL THEN 1 ELSE 0 END AS UserHasNoServerLogin ,CASE WHEN sp2.name IS NULL AND sp1.name IS NOT NULL THEN 1 ELSE 0 END AS UserRequiresSIDFix FROM sys.database_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name COLLATE SQL_Latin1_General_CP1_CI_AI ) INNER JOIN sysusers s on p.sid = s.sid LEFT OUTER JOIN sys.server_principals sp1 ON p.name COLLATE SQL_Latin1_General_CP1_CI_AI= sp1.name LEFT OUTER JOIN sys.server_principals sp2 ON p.name COLLATE SQL_Latin1_General_CP1_CI_AI= sp2.name AND p.SID = sp2.SID WHERE p.type IN ( ''S'', ''G'', ''U'' ) AND p.name NOT IN (''sa'', ''public'', ''guest'', ''dbo'' ) ORDER BY p.name 'EXEC(@SqlStatement)/*********************************************************//********* END GET DB USERS *********//*********************************************************//*********************************************************//********* START GET DB ROLES *********//*********************************************************/SELECT @SQLStatement = ''SELECT @SQLStatement = @SQLStatement +' USE [' + @DatabaseName + '] INSERT #DatabaseRoles ( Servername ,DatabaseName ,DatabaseRoleName ,DateCreated ,DateModified ,CreateStatementText ) SELECT ''' + @Servername + ''' AS ServerName ,''' + @DatabaseName + ''' AS DatabaseName ,dp.name AS DatabaseRoleName ,dp.create_date AS DateCreated ,dp.modify_date AS DateModified ,''IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE type_desc = ''''DATABASE_ROLE'''' AND name = '''''' + dp.name + '''''')'' + '' EXEC sp_addrole @rolename ='' + SPACE(1) + QUOTENAME((dp.name), '''''''') AS CreateStatementText FROM sys.database_principals dp WHERE [type] IN (''R'') -- R = Database Role AND [principal_id] > 4 -- 0 to 4 are system users/schemas AND is_fixed_role = 0 'EXEC(@SqlStatement)/*********************************************************//********* END GET DB ROLES *********//*********************************************************//*********************************************************//********* START GET DB SCHEMAS *********//*********************************************************/SELECT @SQLStatement = ''SELECT @SQLStatement = @SQLStatement +' USE [' + @DatabaseName + '] INSERT #DatabaseSchemas ( ServerName ,DatabaseName ,DatabaseSchemaName ,CreateStatementText ) SELECT ''' + @Servername + ''' AS ServerName ,''' + @DatabaseName + ''' AS DatabaseName ,ss.Name AS DatabaseSchemaName ,''IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'''''' + ss.name + '''''')'' + '' EXEC sys.sp_executesql N''''CREATE SCHEMA'' + SPACE(1) + QUOTENAME((ss.name)) + '' AUTHORIZATION '' + QUOTENAME((dp.name)) + '''''''' AS CreateStatementText FROM sys.schemas ss INNER JOIN sys.database_principals dp ON ss.principal_id = dp.principal_id WHERE dp.[principal_id] > 4 -- 0 to 4 are system users/schemas AND is_fixed_role = 0 'EXEC(@SqlStatement)/*********************************************************//********* END GET DB SCHEMAS *********//*********************************************************//*********************************************************//********* START GET DB ROLE MEMBERS *********//*********************************************************/SELECT @SQLStatement = ''SELECT @SQLStatement = @SQLStatement +' USE [' + @DatabaseName + '] INSERT #DatabaseRoleMembers ( ServerName ,DatabaseName ,DatabaseRoleName ,DatabaseUserName ,CreateStatementText ) SELECT ''' + @Servername + ''' AS ServerName ,''' + @DatabaseName + ''' AS DatabaseName ,USER_NAME(rm.role_principal_id) AS DatabaseRoleName ,USER_NAME(rm.member_principal_id) As DatabaseUserName ,''EXEC sp_addrolemember @rolename ='' + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''''''') + '', @membername ='' + SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''''''') AS CreateStatementText FROM sys.database_role_members AS rm WHERE USER_NAME(rm.member_principal_id) IN ( --get user names on the database SELECT [name] FROM sys.database_principals WHERE [principal_id] > 4 -- 0 to 4 are system users/schemas and [type] IN (''G'', ''S'', ''U'') -- S = SQL user, U = Windows user, G = Windows group ) 'EXEC(@SqlStatement)/*********************************************************//********* END GET DB ROLE MEMBERS *********//*********************************************************//*********************************************************//********* START GET DB OBJECT PERMISSIONS *********//*********************************************************/SELECT @SQLStatement = ''SELECT @SQLStatement = @SQLStatement +' USE [' + @DatabaseName + '] INSERT #DatabaseObjectPermissions ( ServerName ,DatabaseName ,GrantType ,GrantPermissionType ,GrantedByUser ,GrantedToUser ,UserType ,GrantedOnObject ,ObjectType ,CreateStatementText ) SELECT ''' + @Servername + ''' AS ServerName ,''' + @DatabaseName + ''' AS DatabaseName ,perm.state_desc AS GrantType ,perm.permission_name AS GrantPermissionType ,USER_NAME(perm.grantor_principal_id) AS GrantedByUser ,USER_NAME(perm.grantee_principal_id) AS GrantedToUser ,usr.type_desc AS UserType ,obj.name AS GrantedOnObject ,obj.type_desc AS ObjectType ,CAST(''IF EXISTS (SELECT 1 FROM sys.objects so WHERE so.name = '''''' + obj.name + '''''' AND schema_id ('''''' + SCHEMA_NAME(obj.schema_id) + '''''') IS NOT NULL '' + '') '' + CASE WHEN perm.state <> ''W'' THEN perm.state_desc ELSE ''GRANT'' END + SPACE(1) + perm.permission_name + SPACE(1) + ''ON '' + QUOTENAME(SCHEMA_NAME(obj.schema_id)) + ''.'' + QUOTENAME(obj.name) --select, execute, etc on specific objects + CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE ''('' + QUOTENAME(cl.name) + '')'' END + SPACE(1) + ''TO'' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default + CASE WHEN perm.state <> ''W'' THEN SPACE(0) ELSE SPACE(1) + ''WITH GRANT OPTION'' END + '' ELSE SELECT ''''OBJECT '' + QUOTENAME(SCHEMA_NAME(obj.schema_id)) + ''.'' + QUOTENAME(obj.name) + '' DOES NOT EXIST'''''' AS VARCHAR(MAX)) AS CreateStatementText FROM sys.database_permissions AS perm INNER JOIN sys.objects AS obj ON perm.major_id = obj.[object_id] INNER JOIN sys.database_principals AS usr ON perm.grantee_principal_id = usr.principal_id LEFT JOIN sys.columns AS cl ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id WHERE usr.[principal_id] > 4 -- 0 to 4 are system users/schemas 'EXEC(@SqlStatement)/*********************************************************//********* END GET DB OBJECT PERMISSIONS *********//*********************************************************//*********************************************************//********* START GET DB LEVEL PERMISSIONS *********//*********************************************************/SELECT @SQLStatement = ''SELECT @SQLStatement = @SQLStatement +' USE [' + @DatabaseName + '] INSERT #DatabaseLevelPermissions ( ServerName ,DatabaseName ,GrantType ,GrantPermissionType ,GrantedByUser ,GrantedToUser ,UserType ,ObjectType ,CreateStatementText ) SELECT ''' + @Servername + ''' AS ServerName ,''' + @DatabaseName + ''' AS DatabaseName ,perm.state_desc AS GrantType ,perm.permission_name AS GrantPermissionType ,USER_NAME(perm.grantor_principal_id) AS GrantedByUser ,USER_NAME(perm.grantee_principal_id) AS GrantedToUser ,usr.type_desc AS UserType ,''Database'' AS ObjectType ,CASE WHEN perm.state <> ''W'' THEN perm.state_desc --W=Grant With Grant Option ELSE ''GRANT'' END + SPACE(1) + perm.permission_name --CONNECT, etc + SPACE(1) + ''TO'' + SPACE(1) + ''['' + USER_NAME(usr.principal_id) + '']'' COLLATE database_default --TO <user name> + CASE WHEN perm.state <> ''W'' THEN SPACE(0) ELSE SPACE(1) + ''WITH GRANT OPTION'' END AS CreateStatementText FROM sys.database_permissions AS perm INNER JOIN sys.database_principals AS usr ON perm.grantee_principal_id = usr.principal_id WHERE [perm].[major_id] = 0 AND [usr].[principal_id] > 4 -- 0 to 4 are system users/schemas AND [usr].[type] IN (''G'', ''S'', ''U'') -- S = SQL user, U = Windows user, G = Windows group 'EXEC(@SqlStatement)/*********************************************************//********* END GET DB LEVEL PERMISSIONS *********//*********************************************************//*********************************************************//********* START GET SCHEMA LEVEL PERMISSIONS *********//*********************************************************/SELECT @SQLStatement = ''SELECT @SQLStatement = @SQLStatement +' USE [' + @DatabaseName + '] INSERT #SchemaLevelPermissions ( ServerName ,DatabaseName ,GrantType ,GrantPermissionType ,GrantedByUser ,GrantedToUser ,UserType ,ObjectName ,ObjectType ,CreateStatementText ) SELECT ''' + @Servername + ''' AS ServerName ,''' + @DatabaseName + ''' AS DatabaseName ,perm.state_desc AS GrantType ,perm.permission_name AS GrantPermissionType ,USER_NAME(perm.grantor_principal_id) AS GrantedByUser ,USER_NAME(perm.grantee_principal_id) AS GrantedToUser ,dbprin.type_desc AS UserType ,SCHEMA_NAME(major_id) AS ObjectName ,class_desc AS ObjectType ,CASE WHEN perm.state <> ''W'' THEN perm.state_desc --W=Grant With Grant Option ELSE ''GRANT'' END + SPACE(1) + perm.permission_name --CONNECT, etc + SPACE(1) + ''ON'' + SPACE(1) + class_desc + ''::'' COLLATE database_default --TO <user name> + QUOTENAME(SCHEMA_NAME(major_id)) + SPACE(1) + ''TO'' + SPACE(1) + QUOTENAME(USER_NAME(grantee_principal_id)) COLLATE database_default + CASE WHEN perm.state <> ''W'' THEN SPACE(0) ELSE SPACE(1) + ''WITH GRANT OPTION'' END AS CreateStatementText FROM sys.database_permissions AS perm INNER JOIN sys.schemas s ON perm.major_id = s.schema_id INNER JOIN sys.database_principals dbprin ON perm.grantee_principal_id = dbprin.principal_id WHERE class = 3 --class 3 = schema 'EXEC(@SqlStatement)/*********************************************************//********* END GET SCHEMA LEVEL PERMISSIONS *********//*********************************************************/-- Now the section to create the output INSERT #Results(ResultText) VALUES ('/**********************************************************************')INSERT #Results(ResultText) VALUES ('*** Script to recreate security on a single database')INSERT #Results(ResultText) VALUES ('***')INSERT #Results(ResultText) VALUES ('*** Original Database:' + @TAB + @DatabaseName)INSERT #Results(ResultText) VALUES ('*** Original Server:' + @TAB + @TAB + @ServerName)INSERT #Results(ResultText) VALUES ('*** Script created on:' + @TAB + CAST(GETDATE() AS VARCHAR(11)))INSERT #Results(ResultText) VALUES ('*** Script created by:' + @TAB + SUSER_SNAME())INSERT #Results(ResultText) VALUES ('**********************************************************************/')INSERT #Results(ResultText) VALUES ('')INSERT #Results(ResultText) VALUES ('')INSERT #Results(ResultText) VALUES ('/**********************************************************************/')INSERT #Results(ResultText) VALUES ('/****** CREATE Server Logins ******/')INSERT #Results(ResultText) VALUES ('/**********************************************************************/')INSERT #Results(ResultText) VALUES ('')INSERT #Results(ResultText) VALUES ('USE master')INSERT #Results(ResultText) VALUES ('GO')INSERT #Results(ResultText) VALUES ('')INSERT #Results(ResultText)SELECTCreateStatementTextFROM #ServerLoginsForDBORDER BY LoginNameINSERT #Results(ResultText) VALUES ('')INSERT #Results(ResultText) VALUES ('')INSERT #Results(ResultText) VALUES ('')INSERT #Results(ResultText) VALUES ('/**********************************************************************/')INSERT #Results(ResultText) VALUES ('/****** CREATE Database Users ******/')INSERT #Results(ResultText) VALUES ('/**********************************************************************/')INSERT #Results(ResultText) VALUES ('')INSERT #Results(ResultText) VALUES ('USE [' + @DatabaseName + ']')INSERT #Results(ResultText) VALUES ('GO')INSERT #Results(ResultText) VALUES ('')INSERT #Results(ResultText)SELECTCreateStatementTextFROM #DatabaseUsersORDER BY DBUserNameINSERT #Results(ResultText) VALUES ('')INSERT #Results(ResultText) VALUES ('')INSERT #Results(ResultText) VALUES ('')INSERT #Results(ResultText) VALUES ('/**********************************************************************/')INSERT #Results(ResultText) VALUES ('/****** CREATE Database Roles ******/')INSERT #Results(ResultText) VALUES ('/**********************************************************************/')INSERT #Results(ResultText) VALUES ('')INSERT #Results(ResultText) VALUES ('USE [' + @DatabaseName + ']')INSERT #Results(ResultText) VALUES ('GO')INSERT #Results(ResultText) VALUES ('')INSERT #Results(ResultText)SELECTCreateStatementTextFROM #DatabaseRolesORDER BY DatabaseRoleNameINSERT #Results(ResultText) VALUES ('')INSERT #Results(ResultText) VALUES ('')INSERT #Results(ResultText) VALUES ('')INSERT #Results(ResultText) VALUES ('/**********************************************************************/')INSERT #Results(ResultText) VALUES ('/****** CREATE Database Role Members ******/')INSERT #Results(ResultText) VALUES ('/**********************************************************************/')INSERT #Results(ResultText) VALUES ('')INSERT #Results(ResultText) VALUES ('USE [' + @DatabaseName + ']')INSERT #Results(ResultText) VALUES ('GO')INSERT #Results(ResultText) VALUES ('')INSERT #Results(ResultText)SELECTCreateStatementTextFROM #DatabaseRoleMembersORDER BY DatabaseRoleName, DatabaseUserNameINSERT #Results(ResultText) VALUES ('')INSERT #Results(ResultText) VALUES ('')INSERT #Results(ResultText) VALUES ('')INSERT #Results(ResultText) VALUES ('/**********************************************************************/')INSERT #Results(ResultText) VALUES ('/****** CREATE Database Schemas ******/')INSERT #Results(ResultText) VALUES ('/**********************************************************************/')INSERT #Results(ResultText) VALUES ('')INSERT #Results(ResultText) VALUES ('USE [' + @DatabaseName + ']')INSERT #Results(ResultText) VALUES ('GO')INSERT #Results(ResultText) VALUES ('')INSERT #Results(ResultText)SELECTCreateStatementTextFROM #DatabaseSchemasORDER BY DatabaseSchemaNameINSERT #Results(ResultText) VALUES ('')INSERT #Results(ResultText) VALUES ('')INSERT #Results(ResultText) VALUES ('')INSERT #Results(ResultText) VALUES ('/**********************************************************************/')INSERT #Results(ResultText) VALUES ('/****** CREATE Database Object Permissions ******/')INSERT #Results(ResultText) VALUES ('/**********************************************************************/')INSERT #Results(ResultText) VALUES ('')INSERT #Results(ResultText) VALUES ('USE [' + @DatabaseName + ']')INSERT #Results(ResultText) VALUES ('GO')INSERT #Results(ResultText) VALUES ('')INSERT #Results(ResultText)SELECTCreateStatementTextFROM #DatabaseObjectPermissionsORDER BY GrantedOnObjectINSERT #Results(ResultText) VALUES ('')INSERT #Results(ResultText) VALUES ('')INSERT #Results(ResultText) VALUES ('')INSERT #Results(ResultText) VALUES ('/**********************************************************************/')INSERT #Results(ResultText) VALUES ('/****** CREATE Database Level Permissions ******/')INSERT #Results(ResultText) VALUES ('/**********************************************************************/')INSERT #Results(ResultText) VALUES ('')INSERT #Results(ResultText) VALUES ('USE [' + @DatabaseName + ']')INSERT #Results(ResultText) VALUES ('GO')INSERT #Results(ResultText) VALUES ('')INSERT #Results(ResultText)SELECTCreateStatementTextFROM #DatabaseLevelPermissionsORDER BY GrantedToUser, GrantPermissionTypeINSERT #Results(ResultText) VALUES ('')INSERT #Results(ResultText) VALUES ('')INSERT #Results(ResultText) VALUES ('')INSERT #Results(ResultText) VALUES ('/**********************************************************************/')INSERT #Results(ResultText) VALUES ('/****** CREATE Schema Level Permissions ******/')INSERT #Results(ResultText) VALUES ('/**********************************************************************/')INSERT #Results(ResultText) VALUES ('')INSERT #Results(ResultText) VALUES ('USE [' + @DatabaseName + ']')INSERT #Results(ResultText) VALUES ('GO')INSERT #Results(ResultText) VALUES ('')INSERT #Results(ResultText)SELECTCreateStatementTextFROM #SchemaLevelPermissionsORDER BY GrantedToUser, GrantPermissionTypeINSERT #Results(ResultText) VALUES ('')INSERT #Results(ResultText) VALUES ('')INSERT #Results(ResultText) VALUES ('')INSERT #Results(ResultText) VALUES ('/**********************************************************************/')INSERT #Results(ResultText) VALUES ('/****** Fix Database Users/Login link ******/')INSERT #Results(ResultText) VALUES ('/**********************************************************************/')INSERT #Results(ResultText) VALUES ('')INSERT #Results(ResultText) VALUES ('USE [' + @DatabaseName + ']')INSERT #Results(ResultText) VALUES ('GO')INSERT #Results(ResultText) VALUES ('')INSERT #Results(ResultText)SELECT'EXEC sp_change_users_login ''auto_fix'', ''' + DBUserName + ''', NULL, NULL'FROM #DatabaseUsersORDER BY DBUserNameSELECT ResultTextFROM #ResultsORDER BY ResultIDSET NOCOUNT OFF