user mapped as dbo

  • inund8ed

    Ten Centuries

    Points: 1337

    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

    SSC Guru

    Points: 233859

    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

    SSCertifiable

    Points: 5159

    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

    SSC Guru

    Points: 182269

    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

  • inund8ed

    Ten Centuries

    Points: 1337

    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

    SSC Guru

    Points: 182269

    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

    SSC Guru

    Points: 182269

    How is it going?

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

  • Perry Whittle

    SSC Guru

    Points: 233859

    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

    SSC Guru

    Points: 182269

    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

  • inund8ed

    Ten Centuries

    Points: 1337

    The result for:

    USE [your_database];

    GO

    SELECT SUSER_NAME(principal_id)

    FROM sys.database_principals

    WHERE name = 'dbo';

    GO

    is 'sa'

  • Orlando Colamatteo

    SSC Guru

    Points: 182269

    sage8 (8/20/2012)


    The result for:

    USE [your_database];

    GO

    SELECT SUSER_NAME(principal_id)

    FROM sys.database_principals

    WHERE name = 'dbo';

    GO

    is 'sa'

    OK, that is what it should be. Nothing to be concerned about. Since SQL 2005 (at least) dbo will exist as a user in every database and be mapped to the sa login. From some of your earlier posts it sounded like a login other than sa was mapped to dbo which could have become an issue. Something like that could have been the bad outcome from upgrading a SQL 2000 database that had had its system tables manually updated.

    When the sa login, or any login in the sysadmin Role, enters a database they enter as the dbo User. That is just how it works. Test it while logged in using:

    SELECT USER_NAME();

    If the user is in the sysadmin Role, even if they have a Database User in the database for their Login, they'll still be mapped into the database as the dbo User.

    edit: spelling

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

  • inund8ed

    Ten Centuries

    Points: 1337

    The output for

    DBCC TRACEON (3604);

    GO

    DBCC DBINFO ('TRACKIT80_DATA2007');

    GO

    is:

    DBINFO STRUCTURE:

    DBINFO @0x000000004329E480

    dbi_dbid = 10 dbi_status = 65544 dbi_nextid = 1933301997

    dbi_dbname = TRACKIT80_2007 dbi_maxDbTimestamp = 1129100 dbi_version = 655

    dbi_createVersion = 539 dbi_ESVersion = 0

    dbi_nextseqnum = 1900-01-01 00:00:00.000 dbi_crdate = 2011-05-11 08:58:49.677

    dbi_filegeneration = 0

    dbi_checkptLSN

    I presume this was a SQL 6.5 db originally?

    I am logged in with a sysadmin role (not 'sa') so "SELECT USER_NAME();" is dbo.

    In Enterprise Manager, when I expand this database, go to Security, I do not see the login of the user that we want to drop.

    But when i select the properties of dbo, it shows that the login name is the user account to be dropped.

    It is also a member role of db_owner.

    Any ideas how to remove the mapping?

    thank you

  • Orlando Colamatteo

    SSC Guru

    Points: 182269

    Things are not adding up. Can you post a screenshot of what you're seeing?

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

  • inund8ed

    Ten Centuries

    Points: 1337

    I have attached both the server-level and database-level properties of the login.

    thank you

  • Orlando Colamatteo

    SSC Guru

    Points: 182269

    That happens when you change the db owner.

    What does this return:

    SELECT SUSER_SNAME(owner_sid)

    FROM sys.databases

    WHERE name = 'yourDatabase';

    I think you'll find that it returns CUSTOMERCARE\dscott, which is probably the person who created the database. By default the db creator is made to be the owner.

    Change the DB owner and then I think you'll be able to drop the login. I default to having all my databases owned by sa unless I have a specific security requirement that compels me to use a different login.

    ALTER AUTHORIZATION ON DATABASE::yourDatabase TO sa;

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

Viewing 15 posts - 1 through 15 (of 22 total)

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