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
Comments