Creating databases under MS SQL 2005 Server (SP-2): dbo user role

Question

Good Day,

 

  I am having a problem with creating databases in MS SQL 2005 Server.  When I attempt to assign an User Mapping for my SQL user account to the newly created database, the "dbo" user and schema is already assigned.  When I try to drop this user in favor of my own, I receive an error message:  Cannot alter the user "dbo" (Microsoft SQL Server, Error: 15150).  I am connected to my database engine through the "sa" account.

 

Regards,

Keith

Answer 1

Hi Keith,

 

The login that creates the database is mapped to a special user called dbo.  The dbo is the owner of the database and can not be dropped.  The dbo schema is associated with the dbo user so it also can not be dropped.

 

The sa account will represent itself as dbo in all databases.

 

to check for yourself: select user_name() when logged in as sa.

 

Hope this helps,

 

-Steven Gott

SDE/T

SQL Server

Answer 2

I have a database that must have a unique schema and user account assigned.  I want to exchange the default "dbo" user with my own.

 

Regards,

Keith

Answer 3

Create a new schema and assign the schema owner as whatever user you want to use:

CREATE SCHEMA YourSchemaName AUTHORIZATION YourUserName

 

If there are already objects in the dbo schema that you need to be in YourSchema, you change the schema with:

ALTER SCHEMA YourSchemaName TRANSFER dbo.ObjectNameToTransfer

 

You can then assign users the default schema of YourSchemaName. You can change the default schema for exsiting users with:

ALTER USER YourUserName WITH DEFAULT_SCHEMA = YourSchemaName

 

-Sue

Answer 4

Here is the error that I receive when trying to create a new schema for my database:

 

Msg 15151, Level 16, State 1, Line 1
Cannot find the user 'cqadmin', because it does not exist or you do not have permission.
Msg 2759, Level 16, State 0, Line 1
CREATE SCHEMA failed due to previous errors.

 

My user, "cqadmin" has public access of the database in question.

Answer 5

Hi Steve


                I had the same problem and i could to resolve it with the help of your post,

Thanks


Murale