link of dbo to database user in sql2005

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.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

link of dbo to database user in sql2005

Author

Topic

amirs
Constraint Violating Yak Guru

260 Posts

Posted - 2011-02-21 : 06:08:13

Dear Member
i have restored the database of sql2000 backup file in sql2005 to the of version is 9.0.4035.
We create table ,delete table all ddl tranction are through application.
All these activities are posible when u have dbo right to database user 'dbo' to using my application.
We have to link this user to sql logins and mapping this user to link of dbo to following process
In Right click to
User>>Properties >>User Mapping >> add the dbo to user colomn to my database

when i click ok button then display following error

User, group, or role 'dbo' already exists in the current database. (Microsoft SQL Server, Error: 15023)

TITLE: Microsoft SQL Server Management Studio
------------------------------
Rename failed for User 'xyz'. (Microsoft.SqlServer.Smo)

but i have check there is already user dbo existing in the database . But this user 'dbo' is not linked to any login . This was not the case in SQL 2000.
Now I want to change the user logins to dbo. It's not possible . How can i go ahead.

amirs
Constraint Violating Yak Guru

260 Posts

Posted - 2011-02-22 : 01:08:21

sp_change_users_login report

gives following result

dbo 0xDAFF6970E459E4499116812B0DC42FD3

In database --> security --> users , the login field is empty for user dbo . User dbo owns all the schema objects of the db.

Now when we try to link the database and users to login and specify the user as dbo , we get following error .

Create failed for User 'dbo'. (Microsoft.SqlServer.Smo)
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
User, group, or role 'dbo' already exists in the current database. (Microsoft SQL Server, Error: 15023)

I understand we can use sp_change_users_login with either update_one or autofix argument . But I also read this can not be done for dbo user.

Can anybody guide me how can I go ahead in this circumstances .

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources