SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


user mapped as dbo


user mapped as dbo

Author
Message
sierra4
sierra4
Mr or Mrs. 500
Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)

Group: General Forum Members
Points: 515 Visits: 700
hi,

we are trying to drop a login that happens to be mapped/aliased as 'dbo'.
it is not the owner of the db.

so in the db itself i don't see that login name, only the user 'dbo'.
how can i remove the login mapping to the database without affecting the dbo schema?
this is sql server 2008.


thank you
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91823 Visits: 17971
Initially I would have said the login owns the database, double check and confirm this is not the case.

Has this database come from sql2000 at some point?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
SQL Surfer '66
SQL Surfer '66
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2274 Visits: 345
If I understand the question correctly you have to transfer the ownership to another login before you drop the login:

USE [Your database]
GO
ALTER AUTHORIZATION ON SCHEMA::[dbo] TO [Another login]
GO


Orlando Colamatteo
Orlando Colamatteo
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69476 Visits: 14491
In 2005 and above you cannot remap the dbo user to a login other than sa, and you cannot drop sa.

I too would like to know if this was once a SQL 2000 database.

What does this return?

USE [your_database];
GO
SELECT SUSER_NAME(principal_id)
FROM sys.database_principals
WHERE name = 'dbo';
GO



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
sierra4
sierra4
Mr or Mrs. 500
Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)

Group: General Forum Members
Points: 515 Visits: 700
unfortunately i don't know the history of this db.

i was trying to recreate this scenario on a test server but i was unable to map/alias a user as dbo since that account exists in all of the dbs (sql 2008), so i too assume that the problem db may have been upgraded from older versions.

transferring dbo schema ownership as suggested above is also an option, and i wanted to transfer it to a 'dbo' user account as this seems to be the sql 2008 standard, but this user account is already aliased as the dbo, so i'm looking for a way to unmap it.

i'm looking at trying drop alias but don't want to risk losing the dbo schema.
Orlando Colamatteo
Orlando Colamatteo
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69476 Visits: 14491
sage8 (8/15/2012)
unfortunately i don't know the history of this db.

What is the value of dbi_createVersion when you run this? The number will reveal on which version of SQL Server the database was originally created.

DBCC TRACEON (3604);
GO
DBCC DBINFO ('your_database');
GO



i was trying to recreate this scenario on a test server but i was unable to map/alias a user as dbo since that account exists in all of the dbs (sql 2008), so i too assume that the problem db may have been upgraded from older versions.

sa is mapped to dbo and you cannot change that in SQL 2005 and above, and a user can only be mapped to one login.

transferring dbo schema ownership as suggested above is also an option, and i wanted to transfer it to a 'dbo' user account as this seems to be the sql 2008 standard, but this user account is already aliased as the dbo, so i'm looking for a way to unmap it.

i'm looking at trying drop alias but don't want to risk losing the dbo schema.

You cannot drop the dbo user.

What was the result of the query in my previous post?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Orlando Colamatteo
Orlando Colamatteo
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69476 Visits: 14491
How is it going?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91823 Visits: 17971
Correct you can't drop the user, you change the mapping.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Orlando Colamatteo
Orlando Colamatteo
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69476 Visits: 14491
Perry Whittle (8/16/2012)
Correct you can't drop the user, you change the mapping.

You cannot change the login mapped to the dbo user either...at least I could not on 2005, 2008 R2 or 2012.

Issuing this:

USE [SomeDatabase];
ALTER USER [dbo] WITH LOGIN = [SomeLogin];



On all three versions I get this:

Msg 15150, Level 16, State 1, Line 1
Cannot alter the user 'dbo'.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
sierra4
sierra4
Mr or Mrs. 500
Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)

Group: General Forum Members
Points: 515 Visits: 700
The result for:
USE [your_database];
GO
SELECT SUSER_NAME(principal_id)
FROM sys.database_principals
WHERE name = 'dbo';
GO

is 'sa'
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search