February 16, 2003 at 2:24 pm
We've created a 'sandbox' database for power users, wherein we have granted "CREATE TABLE" to all Domain Users. This allows them to create tables, which will have their user name attached, and they can manage it and drop it when done.
Works great so far.
However, we have a few other users who happen to be Domain Admins. Domain Admins are in the Local Administrator group, and they are then System Administrators (server role) and so become dbo.
When a Domain Admin creates a table its owner is "dbo".
Worse, there does not seem to be a way to create a able under their explicit user name. For example, I cannot (under my domain admin account) say something like:
create table [Chaslevy\Ferguson].x (a int)
fails with
Specified owner name 'Chaslevy\Ferguson' either does not exist or you do not have permission to use it.
However, doing the same with a regular windows user account that is not a dbo works just fine (from my dbo account).
It appears it is simply not possible for a person with dbo privileges to create a table under their own name. I wanted to do that, because I wanted to use the owner as a way to track usage. Note that I am not trying to REQUIRE that a dbo use their own name, I realize once someone is a dbo they can do pretty much whatever they want. But I'd like to allow them to.
Is it possible for someone who has the dbo role to create a table under their own windows user name as owner?
February 18, 2003 at 8:23 am
The problem you are running into is a distinction between the login (server level) and the user (database level).
When a login is made the owner of a database, the user that is mapped to that login is dbo. The login isn't mapped as Ferguson or even Chaslevy\Ferguson, meaning no user exists in the database under either of those names. Since database objects have to be owned by database users, you're in a catch-22 if you don't want objects owned by dbo.
One of the easiest ways around this is to make the sa login the owner of the database. Then map your login into the database as you normally would and make it a member of the db_owner role. It'll have all the rights of a database owner within the database, but you won't have the issue about not being able to create objects under the username.
BTW, if you are a member of the sysadmin fixed server role, you'll continue to come into the database as dbo, but the database user matching your login will be there. Therefore, you can still create objects belonging to that user account.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply