Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ranjithkumar2319
Starting Member
9 Posts |
 Posted - 2007-10-30 : 02:54:50
|
| 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 to sort it out. I need user created tables as dbo owner not with his user name.Thanks in advance. |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-30 : 03:12:58
|
| CREATE TABLE dbo.MyTableName(...Can your user do that?Kristen |
 |
|
|
ranjithkumar2319
Starting Member
9 Posts |
Posted - 2007-10-30 : 19:21:16
|
| thanks Kristen, I want the user default schema to be dbo. I know how to do it in sql 2005, i need to know how to change the default schema for a user in sql server 2000.Thanks,Ranjith |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-10-30 : 19:29:36
|
| I think there's sp_Changeobjectowner. Check out books online for the proc and parameters.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-10-30 : 19:31:17
|
| Use [DatabaseName]exec sp_ChangedbOwner 'dbo'would be the syntax. |
|
|
|
ranjithkumar2319
Starting Member
9 Posts |
Posted - 2007-10-30 : 22:38:46
|
| thanks dinakar and dataguru1971. I actually want to set "dbo" as a default schema for a particular user. whenever that user creates a table it should come with dbo."tablename". At present it is coming as "username"."tablename", I already made user as db_owner, but still it comes with "username". I am using sql server 2000, I don't know how to set a default schema for a user. Can you please advise. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-30 : 23:32:25
|
| Only way is make that user as db owner with sp_changedbowner, it's different from member of db_owner role. |
|
|
|
ranjithkumar2319
Starting Member
9 Posts |
Posted - 2007-11-01 : 00:32:05
|
| thanks guys... I will use proc and change the owner name. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-01 : 08:00:35
|
| In SQL 2000 can't you set the user, i.e. attribute in the database, to be aliased to "dbo". So anything they create will be owned by dbo. Only any good for new objects though!Kristen |
|
|
|
|
|
|
table name prefix issue