set up user's in sql server 2000 having permission similar to dbo

Question

set up user's in sql server 2000 having permission similar to dbo

Betty Sun

SSCrazy

Points: 2442
More actions
March 5, 2008 at 10:29 am

#187287

Hi all,
Our new database administrator set up an user account for me. Whenever I create a table in the database, it labels betty.XXXXtablename. which I didn't like it since all the original tables are created by me and it labels dbo.XXXXtablename. I want all newly created tables in the database labeled the same as before--dbo.tablename.
I saw my permit in database role is already db_owner, but maybe the other permission set up is not the same as dbo?
One thing I noticed is I cannot modify table design in enterprise manager directly, but I only can do it in analyzer using tran-sql. what kind of set I need to ask my administrator that I want to have all that kind of permission?
Thanks
Betty

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 1

I guess you only have ddl_admin permission. When you only have ddl_admin permission, all tables you create will have the ownership under your account.

Check whether or not you have the permission to view all other tables. If not, that is the fact.

Vivien Xing SSChampion Points: 12297 More actions · Answer 2

Sounds like you are a developer. Are you supposed to create the table using Create Table DBO.tablename? Simply qualifying the object name with an owner name (DBO) is a good practice.

http://www.sqlservercentral.com/articles/Miscellaneous/worstpracticenotqualifyingobjectswiththeowner/1309/

Enterprise Manager is for DBA to admin the SQL Server, not for developers (I think). Query Analyzer is the developers' working area. I do see developers use EM, but I do not encourage them to do so. The more permission DBA gives out, the more DBA has to give out. Not mentioning that some features are different between EM and QA.

I am a DBA and stand on the DBA side. Less privilege. You only need what you need to do your work.

Betty Sun SSCrazy Points: 2442 More actions · Answer 3

thank you both for the replies. Yes I can view all the tables.

OK I will use full name to create a table.

The question is: is that possible, I change owner name to dbo instead of mine?

Thanks.

Betty

Vivien Xing SSChampion Points: 12297 More actions · Answer 4

"I change owner name to dbo instead of mine?" -- A good decision.

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 5

Betty Sun (3/7/2008)
thank you both for the replies. Yes I can view all the tables.
OK I will use full name to create a table.
The question is: is that possible, I change owner name to dbo instead of mine?
Thanks.
Betty

Something like

EXEC sp_changeobjectowner 'owner.mytableName', 'dbo'

Betty Sun SSCrazy Points: 2442 More actions · Answer 6

thank you so much for your reply. I didn't this stored procedure. I did it.

I am very appreciated.

Betty