When setting up databases for end users, what's the best practice regarding who's the dbo for each individual database - the user itself or a sysadmin?
Does it really have any importance at all who the owner (as defined by 'dbo') is ?I'd strongly recommend leaving sa as dbo, and if need be then making the user a member of the db_owner role if you need that.
-PatP|||Thanks.
The issue was raised when I noticed that for older user databases, someone had assigned a system admin as the dbo by his own, personal user name. When than person then left, and his user was removed, those user databases became orphans.|||You can assign db_ddladmin.
db_ddladmin act same as dbo but it has limited rights comparing db_owner.|||I suspect that Coolberg's problem wasn't one of permission level (they want the user to be equivalent to dbo), but one of ownership (they don't want the login to "own" the database).
There are two issues here that are tightly intertwined, and often confused.
A login is what gives a person access to SQL Server. Logins exist at the server level, and can be either SQL Authenticated or Windows Authenticated. Logins are what "own" a database.
A User is what gives a person permissions inside a SQL Server database. Users exist only inside a database, and are logically tied to exactly one login on the server.
I think that Coolberg wants to keep the ownership of the database limited to an administrative login. I strongly recommend using sa (because you just about can't delete that login), but I agree with the general idea regardless of what login you use.
By using this strategy, you can keep the database ownership limited to an administrative login, but still make any database users memebers of the db_owner role (giving them exactly the same permissions as dbo).
-PatP|||Thanks.
Yes, I'll go for the sa user.
My main goal is to avoid getting orphanized databases when users are leaving in the future.
No comments:
Post a Comment