Create backup operator login

 1
 2PRINT '-- Create backup operator login'
 3
 4DECLARE @loginName as NVARCHAR(256) = N'UserDbBackupOperator'
 5DECLARE @password as nvarchar(128) = N'MyDbBackupStrongPassword'
 6
 7DECLARE @sql AS NVARCHAR(MAX)
 8If not Exists (select loginname from master.dbo.syslogins where name = @loginName )
 9Begin
10    Select @SQL = 'CREATE LOGIN ' + QUOTENAME(@loginName) + 'WITH PASSWORD=N'''+@password+''', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF';
11    EXEC sp_executesql @sql
12End
13select @sql = 'USE [?];IF (NOT EXISTS(SELECT * FROM [?].sys.database_principals WHERE name = '''+@loginName+''')) BEGIN CREATE USER ['+@loginName+'] FOR LOGIN ['+@loginName+'];ALTER ROLE [db_backupoperator] ADD MEMBER ['+@loginName+']; END';
14EXEC sp_MSforeachdb @sql
15go

Just add backup operator login over all databases

1
2PRINT '-- Add backup operator login over all databases'
3
4DECLARE @loginName as NVARCHAR(256) = N'UserDbBackupOperator'
5DECLARE @sql AS NVARCHAR(MAX)
6select @sql = 'USE [?];IF (NOT EXISTS(SELECT * FROM [?].sys.database_principals WHERE name = '''+@loginName+''')) BEGIN CREATE USER ['+@loginName+'] FOR LOGIN ['+@loginName+'];ALTER ROLE [db_backupoperator] ADD MEMBER ['+@loginName+']; END';
7EXEC sp_MSforeachdb @sql
8go