I am attempting to restore a database over an existing one and I get an error stating:
System.Data.SqlClient.SqlException: User does not have permission to RESTORE database
The code that generates this is:
Dim rst As New Restore
rst.Action = RestoreActionType.Database
rst.Database = Databasename
Dim bdi As BackupDeviceItem
'Declare a BackupDeviceItem .
bdi = New BackupDeviceItem(openBackupDialog.FileName, DeviceType.File)
'Add the device to the restore.
rst.Devices.Add(bdi)
'Set other properties.
rst.ReplaceDatabase = True
'Run SQLRestore
rst.SqlRestore(srv)
The connection login properties are for a user with db_owner membership and is the same one that is used to create the backup originally.
Any help is greatly appreciated.
Thanks,
Lee.
SMO Restore Permissions
PeteJS
Hi Lee,
I was looking into this issue myself, and here's what I found in MSDN:
If the database being restored does not exist, the user must have CREATE DATABASE permissions to be able to execute RESTORE. If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database.
RESTORE permissions are given to roles in which membership information is always readily available to the server. Because fixed database role membership can be checked only when the database is accessible and undamaged, which is not always the case when RESTORE is executed, members of the db_owner fixed database role do not have RESTORE permissions.