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 #ServerLoginsForDB
CREATE 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 #DatabaseUsers
CREATE 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 #DatabaseRoles
CREATE 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 #DatabaseSchemas
CREATE 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 #DatabaseRoleMembers
CREATE 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 #DatabaseObjectPermissions
CREATE 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 #DatabaseLevelPermissions
CREATE 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 #SchemaLevelPermissions
CREATE 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 #Results
CREATE 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)SELECT CreateStatementTextFROM #ServerLoginsForDBORDER BY LoginName

INSERT #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)SELECT CreateStatementTextFROM #DatabaseUsersORDER BY DBUserName

INSERT #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)SELECT CreateStatementTextFROM #DatabaseRolesORDER BY DatabaseRoleName

INSERT #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)SELECT CreateStatementTextFROM #DatabaseRoleMembersORDER BY DatabaseRoleName, DatabaseUserName

INSERT #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)SELECT CreateStatementTextFROM #DatabaseSchemasORDER BY DatabaseSchemaName

INSERT #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)SELECT CreateStatementTextFROM #DatabaseObjectPermissionsORDER BY GrantedOnObject

INSERT #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)SELECT CreateStatementTextFROM #DatabaseLevelPermissionsORDER BY GrantedToUser, GrantPermissionType

INSERT #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)SELECT CreateStatementTextFROM #SchemaLevelPermissionsORDER BY GrantedToUser, GrantPermissionType

INSERT #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 DBUserName
SELECT ResultTextFROM #ResultsORDER BY ResultID
SET NOCOUNT OFF

Page updated

Google Sites

Report abuse