Grant permission - backup and restore
Question
Monday, March 2, 2015 8:57 PM
Hello Everyone,
Please help. I would like to give permission for the domain user to a particular database in SQL Server 2014 so that it can perform backup and restore it. User should take SQL Management Studio (local) to perform backup and restore.
My user is granted the following roles: dbcreator, public, sysadmin (I know it is weak), and has privileges on the SQL Server backup directory. What should be improved?
Thank you for your help.
All replies (5)
Monday, March 2, 2015 9:12 PM âś…Answered
>> I would like to give permission for the domain user to a particular database
You can't control the permission level once the user granted higher permission in security hierarchy.
Backup
BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.
https://msdn.microsoft.com/en-us/library/ms186865.aspx
Restore
https://msdn.microsoft.com/en-us/library/ms186858.aspx
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 (for the FROM DATABASE_SNAPSHOT option, the database always exists).
--Prashanth
Monday, March 2, 2015 9:18 PM
Hi,
User needs to be a part of db_backupoperator role for performing backups of Data & Log files. You can use the following T-SQL to Grant this permission
USE [Database_name]
GO
EXEC sp_addrolemember N'db_backupoperator', N'UserName'
GO
If the database doesn't exist while performing a RESTORE user needs CREATE DATABASE permission to be able to perform a 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 (for the FROM DATABASE_SNAPSHOT option, the database always exists).
Hope This helps
Thanks
Bhanu
Monday, March 2, 2015 9:39 PM
If you are granting Sysadmin privilege then this server role can perform any activity in the server.If you want to give permission only for taking backup then you can grant db_backupoperator privilege so that the database role will be able to take backup.If dbcreator role is granted then u will be able to restore db.
Tuesday, March 3, 2015 8:02 AM
Be careful with dbcreator role
Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/
MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence
Tuesday, March 3, 2015 9:23 AM
User should take SQL Management Studio (local) to perform backup and restore.
My user is granted the following roles: dbcreator, public, sysadmin (I know it is weak), and has privileges on the SQL Server backup directory. What should be improved?
Thank you for your help.
Hi,
I would say its more secured to create a job and schedule the job instead of giving db creator or db owner role. Why would you take hassle of creating login and granting privileges
Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it