Script to bulk add SysAdmins to SQL Server

Recently I had a project where I needed to add a number of logins as SQL Server sysadmin users to several SQL Server instances. Rather than spending hours clicking through the SSMS dialogs to add those users I decided to script it out. The requirement was the given a list of usernames and passwords the user be added as a sysadmin if they do not exist, and if they already exist, then just reset their password to match the password provided.

I decided to do this as a temporary stored procedure since I didn’t need to keep it around. so I created the procedure with the # sign as #addSysAdmin. the code looks like this.

CREATE PROCEDURE #addSysAdmin
(
	@username NVARCHAR(1000),
	@password NVARCHAR(1000)
)
AS
BEGIN
	-- Adds the user, unless they are already there it sets the password for that user
	DECLARE @tsql NVARCHAR(MAX) = '';

	IF NOT EXISTS 
		(SELECT name  
		 FROM master.sys.server_principals
		 WHERE name = @username)
	BEGIN
		print 'Does not exists, creating user';

		SET @tsql = CONCAT('CREATE LOGIN [', @username, '] WITH PASSWORD=N''', @password,''', DEFAULT_DATABASE=[tempDB], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON;
		ALTER SERVER ROLE [sysadmin] ADD MEMBER [', @username, '];');
		print @tsql;
		EXEC sp_executesql @tsql;
	END
	ELSE
	BEGIN
		print 'Exists, setting password';
		SET @tsql = CONCAT('ALTER LOGIN [', @username, '] WITH PASSWORD=N''', @password,''';');
		print @tsql;
		EXEC sp_executeSQL @tsql;
	END
END
GO
EXEC #addSysAdmin @username  = 'newAdmin', @password = 'Password1%';

GO
DROP PROCEDURE #addSysAdmin;

I then added several lines to call the #addSysAdmin sproc one for each user that I needed to add, and I copied and pasted the script onto each of the SQL Servers where the users needed to be added.

Note this script only works on SQL Server 2012 and newer due to my use of the CONCAT statement. It could easly be adapted to run on older version of SQL Server by replacing the CONCAT with the old style concatenation of the plus symbol +.

Script to bulk add SysAdmins to SQL Server

Leave a Reply Cancel reply