SQL Script To Bulk Alter Users With Logins

Back in July 2013 I did a post where I looked at a problem copying live to test. The basic issue was that the Microsoft Dynamics GP user is also a login (at the SQL Server level) and a user (at the SQL Server database level) and when a database is copied from the live server to the test server (or from the current live top the new live) you can run a script to transfer across the logins, but the users come across with the database and will have different SIDs (Security IDs).

You can use the ALTER USER command in SQL to re-link the login with the user, but this is one statement per user per database. The old post showed how to do this, but this quickly becomes a pain when there are more than a handful of users.

As Perfect Image has grown we have clients with more and more users and/or company databases. Our largest client has over 250 users in their Dynamics GP installation while another has fewer users, but well over 100 companies. Both of these can make copying live to test problematic, especially when only a company database might be copied over rather than the whole system.

I needed to automate the process of altering the login to match the user; the below script is the result of this need. Before running the script in SQL Server Management Studio set the results to output to text.

The scripts needs to be run against the system database (by default called DYNAMICS).

The result is that the script generates an ALTER USER script for each (non-web client) Dynamics GP user for the system database and for each database that the user has access to via Dynamics GP. The output is the ALTER scripts which you can then copy and run in a New Query window.

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
SELECT 
		CASE WHEN
			ROW_NUMBER() OVER (PARTITION BY QUOTENAME(Companies.INTERID) ORDER BY (SELECT NULL)) = 1
		THEN
			'USE ' + QUOTENAME(Companies.INTERID) + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) + UserAlterStatement + CHAR(13) + CHAR(10) + 'GO'
		ELSE
			UserAlterStatement + CHAR(13) + CHAR(10) + 'GO'
		END
FROM
	(SELECT
		'ALTER USER [' + RTRIM(['User Master'].USERID) + '] WITH LOGIN = [' + RTRIM(['User Master'].USERID) + ']' AS UserAlterStatement
	FROM
		SY01400 AS ['User Master'] WITH(NOLOCK)
	WHERE
		['User Master'].WCUser = 0
	AND
		['User Master'].USERID NOT IN ('sa','DYNSA','LESSONUSER1','LESSONUSER2')) AS Users
	CROSS APPLY
		(SELECT
			DB_NAME() AS INTERID
		) AS COMPANIES
UNION ALL
	SELECT
			CASE WHEN
				ROW_NUMBER() OVER (PARTITION BY QUOTENAME(Companies.INTERID) ORDER BY (SELECT NULL)) = 1
			THEN
				'USE ' + QUOTENAME(Companies.INTERID) + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) + UserAlterStatement + CHAR(13) + CHAR(10) + 'GO'
			ELSE
				UserAlterStatement + CHAR(13) + CHAR(10) + 'GO'
			END
	FROM
		(SELECT
			'ALTER USER [' + RTRIM(['User-Company Access'].USERID) + '] WITH LOGIN = [' + RTRIM(['User-Company Access'].USERID) + ']' AS UserAlterStatement,INTERID
		FROM
			SY60100 AS ['User-Company Access'] WITH(NOLOCK)
		INNER JOIN
			SY01500 AS ['Company Master'] WITH(NOLOCK)
				ON ['Company Master'].CMPANYID = ['User-Company Access'].CMPANYID
		INNER JOIN
			SY01400 AS ['User Master'] WITH(NOLOCK)
				ON ['User Master'].USERID = ['User-Company Access'].USERID
		WHERE
			['User Master'].WCUser = 0
		AND
			['User Master'].USERID NOT IN ('sa','DYNSA','LESSONUSER1','LESSONUSER2')) AS Users
		CROSS APPLY
			(SELECT
				RTRIM(INTERID) AS INTERID
			FROM
				SY01500 AS SY WITH(NOLOCK)
			WHERE
				Users.INTERID = SY.INTERID
			) AS Companies