March 6, 2008 at 9:00 am
If there is a user that was assigned the db_owner role in a database how can you default him to the "dbo" schema. Right now if he creates a table without specifying the schema it will be created like: domain\user.tablename, with his windows account. The user was added with windows authentication
Don't just give the hungry man a fish, teach him how to catch it as well.
the sqlist
March 6, 2008 at 10:27 am
To be honest, I can't think of a way. I'm sure I will get corrected on this, but I think the concept of a "schema" in SQL 2000 is not really there as it treats users and schemas as the same thing. So the object owner, by default is the user that created the object.
The only three ways I can think of:
- specify the owner when you create the table
- make the user the database owner (sp_changedbowner() - not recommended, unless it's the main user for that database).
- add the user to the local Admin group for the machine, as that group usually links into the dbo user of each db (again, not recommended as they will have full rights in every database, not to mention over the entire machine)
Alternatively, you could use a procedure in a scheduled job to frequently check the database for objects owned by a particular user and reset those owners to dbo. For the procedure look here:
http://support.microsoft.com/kb/275312
--------
[font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]
March 9, 2008 at 8:14 pm
Agree with this suggestion: "- specify the owner when you create the table".
Simply qualifying the object name with an owner name (DBO), for example, create table DBO.tablename.
Refer to this post:
http://www.sqlservercentral.com/Forums/Topic464605-149-1.aspx#bm466185
March 9, 2008 at 9:00 pm
I don't think that you get this capability until SQL 2005.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 9, 2008 at 11:25 pm
If you want to change table owner other than dbo you can use the command sp_changeobjectowner
Example:
sp_changeobjectowner 'lester.table','dbo'
"-=Still Learning=-"
Lester Policarpio
March 10, 2008 at 9:18 am
Thank you all for your posts!
I think that the easyest workaround for this problem would be indeed to explicitly specfy the owner at the creation of the table. I knew you could change the owner but the problem was at the creation.
Thanks again.
Don't just give the hungry man a fish, teach him how to catch it as well.
the sqlist
March 10, 2008 at 9:29 am
Check today's post: Why Object Qualification is important. A good one.
March 10, 2008 at 9:30 am
You could alias the user as 'dbo' although this isn't a best practice and MS discourages it.
i.e.
sp_addalias 'testuser', 'dbo'
Tommy
Follow @sqlscribeMarch 11, 2008 at 4:13 am
Tommy has given 1 way to do this. I can give another way that Microsoft does not object to.
The SQL Login that is the database owner (as shown in database properties) is always mapped to the dbo account within the database. Whenever this login creates an object without giving it a qualifier, it will get a default qualifier of dbo.
You can change database ownership with sp_changedbowner. Therefore to set things up so that a specific login is known as dbo within the database:
1) If the required login is already a user within the database, remove them.
2) Exec mydb.sp_changedbowner 'new owner login'
At my old place, we needed to do this for a few databases. We had some vendor appilcations that ran under a service account and created tables while they were running. The application sql had 'CREATE TABLE xxx' and 'SELECT FROM dbo.xxx'. We could not change the application, so making the appilcation service account the dbo using sp_changedbowner got everything working OK.
Note that the target login used by sp_changedbowner must be a Windows login or SQL login. It cannot be a Windows group.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply