user mapped as dbo

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » Security (SS2K8) » user mapped as dbo

19 posts, Page 1 of 2

1 2 »»

user mapped as dbo

Rate Topic

Display Mode

Topic Options

Author

Message

sierra4

Posted Tuesday, August 14, 2012 7:13 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 17, 2013 6:36 AM
Points: 34, Visits: 416

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

Post #1345103

Perry Whittle

Posted Tuesday, August 14, 2012 11:58 PM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 2:33 PM
Points: 5,201, Visits: 11,150

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" Wink

Post #1345135

SQL Surfer '66

Posted Wednesday, August 15, 2012 3:10 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, February 22, 2013 2:56 AM
Points: 225, Visits: 178

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

Post #1345179

opc.three

Posted Wednesday, August 15, 2012 12:37 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 7:01 AM
Points: 6,696, Visits: 11,717

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1345464

sierra4

Posted Wednesday, August 15, 2012 1:03 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 17, 2013 6:36 AM
Points: 34, Visits: 416

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.

Post #1345491

opc.three

Posted Wednesday, August 15, 2012 1:16 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 7:01 AM
Points: 6,696, Visits: 11,717

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1345499

opc.three

Posted Thursday, August 16, 2012 3:58 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 7:01 AM
Points: 6,696, Visits: 11,717

How is it going?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1346282

Perry Whittle

Posted Thursday, August 16, 2012 5:44 PM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 2:33 PM
Points: 5,201, Visits: 11,150

Correct you can't drop the user, you change the mapping.

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

"Ya can't make an omelette without breaking just a few eggs" Wink

Post #1346295

opc.three

Posted Thursday, August 16, 2012 9:19 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 7:01 AM
Points: 6,696, Visits: 11,717

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'.

Post #1346320

sierra4

Posted Monday, August 20, 2012 4:11 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 17, 2013 6:36 AM
Points: 34, Visits: 416

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

is 'sa'

Post #1347058

« Prev Topic | Next Topic »

19 posts, Page 1 of 2

1 2 »»

Permissions