October 30, 2007 at 1:10 am
Please help... or give me ref where i can find the information on it. I browsed all around but couldn't find relating.
One of the user in a database is given db_owner role, but when he creates the table in a database, it is created with his user name as owner of the table but not as a dbo. Can someone please help me, I need user created tables as dbo owner not with his user name.
Thanks in advance.
October 30, 2007 at 1:25 am
http://msdn2.microsoft.com/en-us/library/aa258255(SQL.80).aspx --- try this link. Might be useful for your problem
October 30, 2007 at 1:25 am
Since you are using 2005 what i suggest that you can do is add the user to have dbo as his default schema. so that whatever objects he creates will be created under the schema named dbo instead of his username hope things has been clarified.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
October 30, 2007 at 1:29 am
VAIYDEYANATHAN.V.S (10/30/2007)
http://msdn2.microsoft.com/en-us/library/aa258255(SQL.80).aspx --- try this link. Might be useful for your problem
Vaidey, This is a SQL 2005 forum. The link that you have given is for SQL 2000. Please check and make changes if you feel
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
October 30, 2007 at 4:36 pm
SSCrazy, thanks for your suggestion. I think I have posted the problem at wrong place.I haven't mentioned it, database is in sql server 2000. How to set default schema to dbo in sql server 2000
October 31, 2007 at 7:30 am
No probs. Since you say that its 2000 you have to educate your users to create object with prefix dbo. so that the object is created under the dbo as owner. else you as the DBA has to execute the command sp_changeobject owner to change the owner of the object.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
November 1, 2007 at 3:25 am
In SQL 2000 you can set up one account per database so that the objects it creates will automatically have the prefix dbo.
To do this, first remove the account from the database if it is already a database user.
Next, use sp_changedbowner to set the database ownership to the erquired account. This will map the SQL Login name to the dbo alias within the database, which is what causes the objects to be created with dbo.
Some applications require that you do this for their service account if the application is to work correctly. e.g. the Remedy Help Desk application creates objects using CREATE TABLE FRED, but accesses them using SELECT x FROM dbo.FRED. Without the service accountr mapping the appilcation does not work correctly.
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
November 1, 2007 at 9:55 pm
Best answer in this case is some education, best practice is to specify object owner pretty much all the time, saves SQL server from having to "find" the right object and keeps you out of the rename business.
Rather than "create table x..." it should be "create table dbo.x..." same goes for select statements (e.g. select column1, column2 from dbo.x).
If you're feeling really persnickity ask them to specify the complete name (e.g. databasename.owner.object)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply