MSSQL Promotion Account Privilege

Question

Post reply

MSSQL Promotion Account Privilege

jl-820778

SSC Rookie

Points: 32
More actions
January 4, 2013 at 11:33 am

#268458

Hi,
I need to set up an account to promote database change(s) from QA to Production. I could give the account SysAdmin privilege. However, it will give access to other databases on the SQL server.
What privilege(s)/role(s) should I give to ensure all promotions will be done successfully on the specific database?
Would you please share some of your knowledge?
Thanks in advance.

Viewing 8 posts - 1 through 7 (of 7 total)

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

e4d4 SSCertifiable Points: 5777 More actions · Answer 1

DB Owner dbo http://msdn.microsoft.com/en-us/library/ms178630.aspx.

DBO is like sysadmin on instance level, but only on a particular database level.

jl-820778 SSC Rookie Points: 32 More actions · Answer 2

Thanks for your quick reply.

So, DBO would have absolute power to change anything within specific database even schema, trigger and etc?

Is there any limitation for dbo within database?

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

What you want to do is add the Database User as a member of the db_owner Fixed Database Role:

USE YourDatabase;

EXEC sys.sp_addrolemember

@rolename = N'db_owner',

@membername = N'database_user_name';

As an aside, so we're not confusing things, the Database User named dbo is a completely different concept from members of the db_owner Role and the Database Owner (the Server Login that owns the database) is a third distinct concept still.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

jl-820778 (1/4/2013)
Thanks for your quick reply.
So, DBO would have absolute power to change anything within specific database even schema, trigger and etc?
Is there any limitation for dbo within database?

dbo is not the same a the Database Owner which is not necessarily the same as being a member of the db_owner Role.

Being a member of the db_owner Role affords the User all rights in the database, including the ability to drop it.

Permissions of Fixed Database Roles

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

jl-820778 SSC Rookie Points: 32 More actions · Answer 5

Thank you for your clarification and link. It's very helpful.

Now, I'm getting convinced that db_owner is the right one to give but still have some dilemma. From time to time(but rare), we have to create/modify a scheduled job or create an login account as part of promotion.

Do you think it's a good idea to add more server role(s) to the account so the account is able to accomplish such promotion or is it a good idea to have a separate account to manage "Server Level" promotion?

What's common practice in your organization?

Thanks again.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

jl-820778 (1/4/2013)
Thank you for your clarification and link. It's very helpful.
Now, I'm getting convinced that db_owner is the right one to give but still have some dilemma. From time to time(but rare), we have to create/modify a scheduled job or create an login account as part of promotion.
Do you think it's a good idea to add more server role(s) to the account so the account is able to accomplish such promotion or is it a good idea to have a separate account to manage "Server Level" promotion?
What's common practice in your organization?
Thanks again.

There are security roles that will allow people to do what you are asking.

For enabling someone to manage SQL Agent jobs check out the SQL Server Agent Fixed Database Roles.

For creating new logins checkout securityadmin in Permissions of Fixed Server Roles. Just be careful when adding people to the securityadmin role. Members of securityadmin can essentially create logins and add database users. It's not quite the same as having membership in sysadmin, but it's pretty darn close when it comes to accessing data that they may not be allowed to access.

In most of the shops I have worked in these tasks were handled by a DBA that had membership in sysadmin so it was never really a situation where it was a consideration. In fact, I have never even seen anyone in the securityadmin Server Role on a live server. If you were trusted enough to manage logins then you pretty much were given sysadmin.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

jl-820778 SSC Rookie Points: 32 More actions · Answer 7

I think i'm gonna have to delegate one account for promotion(s) within specific DB and another account for server level promotion such as creating logins or scheduled jobs and etc. The account to promote within db will be shared by serveral users with temp password which will expired in certain time period. This is why I cannot assign Sysadmin role.

Please feel free if anyone of you have better idea to enlighten me.

Thank you for all of your help.