Create backup operator

Create backup operator login

PRINT '-- Create backup operator login'

DECLARE @loginName as NVARCHAR(256) = N'UserDbBackupOperator'
DECLARE @password as nvarchar(128) = N'MyDbBackupStrongPassword'

DECLARE @sql AS NVARCHAR(MAX)
If not Exists (select loginname from master.dbo.syslogins where name = @loginName )
Begin
    Select @SQL = 'CREATE LOGIN ' + QUOTENAME(@loginName) + 'WITH PASSWORD=N'''+@password+''', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF';
    EXEC sp_executesql @sql
End
select @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';
EXEC sp_MSforeachdb @sql
go

Just add backup operator login over all databases

PRINT '-- Add backup operator login over all databases'

DECLARE @loginName as NVARCHAR(256) = N'UserDbBackupOperator'
DECLARE @sql AS NVARCHAR(MAX)
select @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';
EXEC sp_MSforeachdb @sql
go