What SQL permission to grant

Question

What SQL permission to grant

smile_netz

Ten Centuries

Points: 1018
More actions
September 26, 2011 at 11:31 pm

#257976

I created a new user in the server.
This user will be responsible for backup and restore of all user databases.
This user also needs to create and drop users as well as grant role members to certain users.
I have granted its login a dbcreator and the user to have db_backupoperator and db_securityadmin. Then tried to tick the SecurityAdmin on the server role. When the user tries to run the create/drop user and sp_addrolemember, it gets the message "User does not have permission to perform this action."
I ticked the db_owner and it worked. The only problem with this is when the user restores the database from another server, the db_owner becomes unticked because this particular user is not a db_owner in this server.
Could someone advice please?
Thank you.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ratheesh@SQL Grasshopper Points: 19 More actions · Answer 1

db_owner permission will be specific to the assigned databases. If you want the user to have full permission in the server level then you can assign him as sysadmin

smile_netz Ten Centuries Points: 1018 More actions · Answer 2

I tested it in one database and only then that it allowed to do the requirements.

If I want the user to do all of the user databases, I need to assign it as Sys Admin.

I don't want to do this because the user should only do the specific tasks I mentioned earlier.

So does this mean that only the SysAdmin could do the sp_addrolemember?

Thanks.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 3

to be able to map a user to a sql server database you need DB_AccessAdmin role in that specific database. DB_SecurityAdmin is for managing permissions and role memberships

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉