Wednesday, August 15, 2012

Grant SQL Server user the needed permissions to backup and restore his database

PROBLEM:
You want to grant SQL Server user the needed permissions to backup and restore his database. Not other databases

SOLUTION:
This script gives 'UserName' the ability to backup and restore only 'TheDatabase' or other databases he created.
When restoring, the user should give direct path to the BAK file. SSMS will not open the default location and will show the following error:

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup
Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists..
USE [TheDatabase]
GO
CREATE USER [UserName] FOR LOGIN [UserName]
GO
EXEC sp_addrolemember N'db_backupoperator', N'UserName'
GO
USE [master]
EXEC master..sp_addsrvrolemember @loginame = N'UserName', @rolename = N'dbcreator'
GO

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

No comments:

Post a Comment