Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Best Practice for Database Owner?


Best Practice for Database Owner?

Author
Message
ntran777
ntran777
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 Visits: 384
I see your point. Thanks for the reply!
Shane Gibson
Shane Gibson
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 115
This does matter for ownership chains (Impersonation. etc). For example if you attempt EXECUTE AS SELF, the dbowner will be used to execute. If the call is cross database, the dbowner may not have permissions in the target DB...broken wonership chain.
kevaburg
kevaburg
Mr or Mrs. 500
Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)

Group: General Forum Members
Points: 586 Visits: 923
Hi!

Our practice is to create a service user/group in AD and assign that as the database owner. I have never been a fan of using the 'sa' account for anything other than emergency admin at the instance level and considering the level of authority the account has should IMHO not be used.

The other reason, and something that applies to us with the amount of databases we have, is that when I see a list of database users/groups, it would be nice to be able to associate them easier with the database. To that end we use AD Resource Groups and assign users to them. These users then have automatic db_owner on the database. Users of the database are then assigned to other reource groups with the appropriate permissions.

I think to say, as I have read in this post, that the owner is irrelevant is perhaps a little dangerous. The owner of the database automatically has (as expected) the right DB_OWNER which is the equivalent of God at the database level so some control over the owner needs to be had. Using 'sa' is also dangerous because, as expected, this is a SYSADMIN account with authority at the Instance-level! It doesn't take much to open a database query and run a query inside MASTER for example!

As far as possible, the rule of minimum privilege should be applied and Active Directory gives us the perfect tools to do that.

I hope that helped.....
david.alcock
david.alcock
SSC Veteran
SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)

Group: General Forum Members
Points: 261 Visits: 1159
Not a database owner issue but I did run into problems when a SQL job owner had their accoutn disabled which in turn caused lots of problems. If I remember rightly it wasnt an issue in executing the job as obviously that is under the service account but there were permission issues for sure.

Apologies for no exact but I would give a little planning regarding the owner of objects, and as previous poster, my preference would be to reserve sa for admin duties.

'Only he who wanders finds new paths'
kevaburg
kevaburg
Mr or Mrs. 500
Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)

Group: General Forum Members
Points: 586 Visits: 923
I agree with that 100%. The owner should be a service account that can only access the resources it needs to fulfil its job.

For me personally, the use of the 'sa' account is a huge no-go!
david.alcock
david.alcock
SSC Veteran
SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)

Group: General Forum Members
Points: 261 Visits: 1159
Absolutely, I never use sa unless its a in case of emergency etc...

However I would necessarily use a service account for an owner, rather I was saying the Agent jobs ran under that. In all honesty I have mostly found an administrative type user specified as the db_owner.

'Only he who wanders finds new paths'
Rich Mechaber
Rich Mechaber
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1089 Visits: 3661
This thread is almost 4 years old -- you might want to start a new one.
Rich
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search