SQL Server Compare Users Script

ben himself's picture

If you need to compare two users in SQL Server, here is a handy script. The output is simply a script to make the “UserToChange” sync up with the “ModelUser”. So this script generates a script.

/*
	Author:		Ben Farnsworth
	Date:		11/14/12
	
	Purpose:	Find differences between two users 
				and generate script to make them match.
*/


-- Press Ctrl+Shift+m to fill in variables

/*	For the record:

	Model User:		
	User to change:	
*/

/*



SELECT * FROM sys.server_principals WHERE name = ''
GO
SELECT * FROM sys.server_principals WHERE name = ''
GO



--*/


--=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
-- *********************************************************************
--							Set RESULTS TO TEXT
							-------------------
-- *********************************************************************
--=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SET NOCOUNT ON
CREATE TABLE #templogin(
	[Database] varchar(100) null,
	[Login Type] varchar(100) null,
	[srvLogin] varchar(100) null,
	srvRole varchar(100) null,
	dbUser varchar(100) null,
	dbRole varchar(100) null
)
Go
EXEC sp_msforeachdb @command1 = '
	use [?]
		insert into #templogin
			select 
				''?'',
				[Login Type]=
				case sp.type
				when ''u'' then ''WIN''
				when ''s'' then ''SQL''
				when ''g'' then ''GRP''
				end,
				convert(char(45),sp.name) as srvLogin, 
				convert(char(45),sp2.name) as srvRole,
				/* Wow, found an error after using this for a year. */
				/*convert(char(45),dbp.name) as dbUser,*/
				convert(char(45),sp.name) as dbUser,
				convert(char(45),dbp2.name) as dbRole
			from 
				sys.server_principals as sp join
				sys.database_principals as dbp on sp.sid=dbp.sid join
				sys.database_role_members as dbrm on dbp.principal_Id=dbrm.member_principal_Id join
				sys.database_principals as dbp2 on dbrm.role_principal_id=dbp2.principal_id left join 
				sys.server_role_members as srm on sp.principal_id=srm.member_principal_id left join
				sys.server_principals as sp2 on srm.role_principal_id=sp2.principal_id
		 
			where 
				sp.name = ''''
				or
				sp.name = ''''
	'
DECLARE @defdb varchar(50)
SET @defdb = (SELECT [default_database_name] FROM sys.server_principals WHERE name = '')

SELECT CASE
(SELECT 'TRUE' 
	WHERE '' in 
	(	SELECT name FROM sys.server_principals	)
)
WHEN 'TRUE' THEN '/*Server principle exists.*/'
ELSE 'USE [master]
	GO
		CREATE LOGIN [] FROM WINDOWS WITH DEFAULT_DATABASE=[' + @defdb + ']
	GO
		PRINT ''Added  to ' + @defdb + '''
	GO
	'
 
END



/*	Get database mappings and generate mapping where needed	*/
PRINT '/* 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
		The following script will map the user to
		any currently unmapped databases that the 
		model user has.
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
		*/'
SELECT 'USE [' + [Database] + ']
GO
CREATE USER ['+ RTRIM(LTRIM('')) +'] FOR LOGIN [' + RTRIM(LTRIM('')) + ']
GO
PRINT ''Created user [] in [' + [Database] + ']''
GO
'
FROM
(
SELECT  DISTINCT [Database]
FROM #templogin
WHERE dbUser = /*model user*/ '' and ([Database]) NOT IN
(SELECT [Database] FROM #templogin WHERE dbUser = '')
) qq



/*	Get roles and generate mapping where roles are needed	*/
PRINT '/* 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
		The following script will grant roles to 
		the user that they do not already have
		where the model user does have that role.
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
		*/'
SELECT 'USE [' + [Database] + ']
GO
EXEC sp_addrolemember N''' + RTRIM(LTRIM(dbRole)) + ''', N'''+ RTRIM(LTRIM('')) +'''
GO
PRINT ''Added ' + RTRIM(LTRIM(dbRole)) + ' role to user []''
GO
' 

FROM
(
SELECT [Database] + dbRole as [all], *
FROM #templogin
WHERE dbUser = /*model user*/ '' and ([Database] + dbRole) NOT IN
(SELECT [Database] + dbRole FROM #templogin WHERE dbUser = '')
) q



DROP TABLE #templogin

Tags: 

Categories: