November 3, 2010 at 6:47 am
We use Netbackup for our SQL servers to backup and restore databases. I would like the service account used by Netbackup to have as limited permissions as possible. The account should be able to backup and restore a db without being able to read any of the content. Right now the account jobs fail if the service account is not in the sysadmin role.
I removed the account from sysadmin and limited it to dbcreator and public but the job fail.
Any idea how to setup an account so that people who know the service account password can't log in with that account and read db information?
Thanks!
November 3, 2010 at 7:18 am
From BOL:
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
RESTORE
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).
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.
-- Gianluca Sartori
November 3, 2010 at 7:18 am
For backing up the database, the login must be part of db_backupoperator fixed database role. More here
For restoring the database, the login must be part of sysadmin fixed server role. More here
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
November 3, 2010 at 8:35 am
I did some testing in SQL 2008 and found a way around the sysadmin role. Here is the minimal permissions needed for backup and restores.
Server Level Permissions:
*dbcreator
*public
Database Level Permissions:
*db_backupoperator
*db_denydatareader
*public
Here is the next step: How do you automatically give the Database level permissions to any new databases created by another team? In SQL 2008 I can used server level triggers but 2005 and 2000 will be a challenge.
November 3, 2010 at 9:32 am
smitty-1088185 (11/3/2010)
How do you automatically give the Database level permissions to any new databases created by another team?
add them to the model database on that instance
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 3, 2010 at 9:35 am
It couldn't be that simple, could it???? @:-)
November 3, 2010 at 9:43 am
yes!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 3, 2010 at 3:57 pm
Here is the script I came up with to finalize the permissions on all of our servers. If someone would like to add some error handling please do so.
EXEC master..sp_dropsrvrolemember @loginame = N'Domain\ServiceAccount', @rolename = N'sysadmin'
GO
USE [model]
GO
CREATE USER [Domain\ServiceAccount] FOR LOGIN [Domain\ServiceAccount]
USE [model]
GO
EXEC sp_addrolemember N'db_backupoperator', N'Domain\ServiceAccount'
GO
USE [model]
GO
EXEC sp_addrolemember N'db_denydatareader', N'Domain\ServiceAccount'
GO
EXEC master..sp_addsrvrolemember @loginame = N'Domain\ServiceAccount', @rolename = N'dbcreator'
GO
exec sp_msforeachdb 'use [?];
create user [Domain\ServiceAccount];
exec sp_addrolemember ''db_backupoperator'', ''Domain\ServiceAccount'';
exec sp_addrolemember ''db_denydatareader'', ''Domain\ServiceAccount'';'
GO
November 4, 2010 at 1:53 am
Thanks for the feedback, very useful.
-- Gianluca Sartori
September 9, 2011 at 6:13 am
Hi All, can i just ask, i have been challenged by my network manager that i need to be mapped to the actual database i am backing up whilst also being in the sysadmin role. Ive checked all over and it doesnt look like this is the case.
Does anyone else know different?
thanks in advance.
September 12, 2011 at 11:11 am
there's a problem when you set permissions to backup operator. The user can't backup from managment studio, only by commando line o query. That's because MS perform some select to show dropbox and the user will no have permision to that tables.
April 11, 2013 at 10:19 am
Extra info.
I granted CREATE ANY DATABASE & ALTER ANY DATABASE permissions to a user but that did not allow him to restore databases.
However, adding him to dbcreator did.
System stored proc sp_srvrolepermission returns the following rights for this role ;
Add member to dbcreator
ALTER DATABASE
CREATE DATABASE
DROP DATABASE
Extend database
RESTORE DATABASE
RESTORE LOG
sp_renamedb
so I would have thought CREATE ANY DATABASE & ALTER ANY DATABASE permissions would have worked. BOL does point out that sp_srvrolepermission does not take into account the permissions hierarchy from 2005 onwards.
I have since read in BOL (since the original post); "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). " That's why the individual permissions method does not work.
Does anybody know of any reference that explicity states what each of the fixed roles allows you to do ? i.e. a sp_srvrolepermission equivalent for 2005 onwards. i.e. if I were to recreate a fixed role using individual permissions (CREATE ANY DATABASE, CONTROL SERVER etc) which permissions constitute those roles ?
BOL is not explicit enough.
June 24, 2015 at 1:36 pm
Perry Whittle (11/3/2010)
smitty-1088185 (11/3/2010)
How do you automatically give the Database level permissions to any new databases created by another team?add them to the model database on that instance
Wow, just a super late thank you now that I stumbled across this thread. That is brilliant in its simplicity!
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply