link of dbo to database user in sql2005

Question

link of dbo to database user in sql2005

amirs-717937

Old Hand

Points: 366
More actions
February 21, 2011 at 8:42 pm

#232430

Dear Member
i have restore the database of sql2000 backup file in sql2005 to the of version is 9.0.4035.
we are use one erp application of front end to this database. we create table ,delete table all ddl tranction are in front end.
when all these tranction are posible when u have dbo right to database user to using my application.
so i have create the user in sql logins like xyz 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 their no link of any to dispaly in error. other users are present in database but their is no link to dbo.
At present user colomn display of this user(xyz) of my database and i have change this user to dbo
so please tell anybody do this activity. send any link or information

Viewing 5 posts - 1 through 4 (of 4 total)

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

Steve Jones - SSC Editor SSC Guru Points: 737047 More actions · Answer 1

I'm not sure what you mean with user column. You add a user to your database. You can then assign this user rights, or map them. In SQL 2000, you might have aliased a user as dbo. In SQL 2005, if this is needed, you can assign the db_owner role to the user in the database.

John.Liu SSCommitted Points: 1606 More actions · Answer 2

If I understand your requirement correctly, you are trying to give that user the dbo permission. DBO is a database role. To grant someone dbo permission, simply add them into the db_owner database role. In the User Mapping, select your db, then tick the db_owner in the database role membership section. Or run this script:

USE <your db>

EXEC sp_addrolemember 'db_owner','your user login'

amirs-717937 Old Hand Points: 366 More actions · Answer 3

thanks to replay and understand my problem

your script i have already execute. i have already db_owner right to this user.

i have check login status to execute following query

EXEC sp_change_users_login 'Report'

but result is dispaly only dbo user not my user.

i have add only dbo in user colom to user mapping so how can i do this ?

John.Liu SSCommitted Points: 1606 More actions · Answer 4

amirs-717937 (2/21/2011)
thanks to replay and understand my problem
your script i have already execute. i have already db_owner right to this user.
i have check login status to execute following query
EXEC sp_change_users_login 'Report'
but result is dispaly only dbo user not my user.
i have add only dbo in user colom to user mapping so how can i do this ?

You can't. The user colum you mentioned is a alias name or user name in that database. As each database has already have a default dbo user, you can't add another user with name "dbo". User name is unique in a database. You already give the user dbo permission, and what you are now trying to do is just give that login a "dbo" label (user name is just a label). In fact, you can name it with anything and that has no impact to the access permission.