Assign a user role for all databases

I’m moving the backup jobs we run onto specific users and need to assign the db_backupoperator role to the user for each database. Very tedious to do in SSMS so
here’s a quick script I knocked up.

The script will assign a specific database role to all databases on a SQL Server. The user will be created in the database if it doesn’t already exist. Just make sure the login exists at a server level, set the @user and @role variables and you’re good to go.

DECLARE @sql VARCHAR(MAX),
		@user VARCHAR(100),
		@role VARCHAR(100);

SET @user = 'DOMAIN\backupuser'; -- set the user here (server level login must exist)
SET @role = 'db_backupoperator'; -- set the role to assign

-- Create the user if it doesn't exist in the current db
SET @sql = 'USE ?;
			IF NOT EXISTS (SELECT *
						   FROM sys.database_principals
						   WHERE [name] = ''' + @user + ''')
			BEGIN
				CREATE USER [' + @user + '] FOR LOGIN [' + @user + ']
			END;';

EXEC sp_MSforeachdb @sql;

-- Assign the role to the db user
SET @sql = 'USE ?;
			EXEC sp_addrolemember ''' + @role + ''', ''' + @user + ''';';

EXEC sp_MSforeachdb @sql;