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


user mapped as dbo


user mapped as dbo

Author
Message
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15315 Visits: 14396
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
sierra4
sierra4
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 648
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
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15315 Visits: 14396
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
sierra4
sierra4
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 648
I have attached both the server-level and database-level properties of the login.

thank you
Attachments
customer mapped as dbo 1.jpg (142 views, 84.00 KB)
customer mapped as dbo 2.jpg (123 views, 105.00 KB)
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15315 Visits: 14396
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
sierra4
sierra4
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 648
This is not the database owner, another account is (but not sa).
I will try to change ownership to sa.
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15315 Visits: 14396
sage8 (8/20/2012)
This is not the database owner, another account is (but not sa).

That does not add up.

I will try to change ownership to sa.

How did it go?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
sierra4
sierra4
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 648
Changing the database ownership to 'sa' fixed it, the dbo account at the database level automatically mapped to sa.
The user account we wanted to remove were no longer mapped to that db and we were able to drop that login.

Thanks to everyone for their help and input!
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15315 Visits: 14396
Excellent. Thanks for posting back that you were able to resolve the issue.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Arun S Prasad
Arun S Prasad
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 134
Hi All,

When I changed the db owner to SA , I could delete the login.

The below code was helpful.. Thank you

USE [your_database];
GO
SELECT SUSER_NAME(principal_id)
FROM sys.database_principals
WHERE name = 'dbo';
GO
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