SQL Server with Mr. Denny

Jul 16 2009   11:00AM GMT

Non-sysadmins create tables under own schema



Posted by: Denny Cherry
Tags:
Enterprise Manager
SQL Server 2000
T/SQL
Tables

When using SQL Server 2000 and the user doesn’t have sysadmin rights, and their login isn’t mapped to the dbo user within the database all objects created will be, by default created under the user schema.

This is the normal behavior of SQL Server 2000.  In order to allow users who are not members of the sysadmin fixed server role to create objects under the dbo schema by default you have to map their login to the dbo user, even if they are a member of the dbo fixed database role.

To work around this, in the T/SQL code specify the owner of the database object.  If your developer is using Enterprise Manager to create the new tables before saving the table, click on the properties button in the upper left hand corner of the Enterprise Manager window (second from the left).  Then change the owner drop down from their username to dbo.  There is no way to default this setting to dbo so it will need to be changed for each new table being created.

Denny

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: