1

What are the user's (SQL) roles needed to take a SQL Server database backup using the SMO Backup class in C# as shown below? Does it use Windows authentication?

 Server myServer = new Server(servername);

 Backup bkp = new Backup();
 bkp.Action = BackupActionType.Database;

 Database myDatabase = myServer.Databases[database];

 bkp.Database = myDatabase.Name;
 bkp.Devices.AddDevice(path, DeviceType.File);
 bkp.BackupSetName = string.Format("{0} database Backup", database);
 bkp.BackupSetDescription=string.Format("{0} database - Full Backup", database);
 bkp.Initialize = true;

 bkp.SqlBackup(myServer);
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
xyz
  • 762
  • 7
  • 24

1 Answers1

1

To be able to run BACKUP DATABASE and BACKUP LOG user must be members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles. From MSDN

gofr1
  • 15,741
  • 11
  • 42
  • 52
  • Is it possible to take backup without having db_owner and sysadmin roles by using sql script or any c# code? – xyz May 30 '16 at 13:05
  • No, backups is a delicate thing, anybody could not have access to that kind of stuff. The backups are usually run by `sa`. – gofr1 May 30 '16 at 13:13
  • yes it not delicate thing.But there is a separate role called "db_backupoperator" – xyz May 30 '16 at 13:39
  • yes, it grants access to the database to make a backup, but not grant any rights to the server's file structure, which are needed to make the backup file. one of the workaround are mentioned here https://stackoverflow.com/questions/10366676/backup-permissions – gofr1 May 30 '16 at 13:48
  • I'v read this post. But does not a user need a sysadmin role to create a new user? – xyz May 30 '16 at 14:43
  • As I remember user with sysasmin role can create new users. – gofr1 May 30 '16 at 14:53