February 3, 2010 at 1:45 pm
We have a database, TeamTest, that lists my fellow dba, 'domain\useraaa', as the owner under the Properties (right click database --> Properties).
When I go to SSMS --> databases --> TeamTest --> Security --> Users, my User ID is not listed.
However, when I go to SSMS --> Security --> Logins --> right click on my UID (domain\useryyy) --> User Mapping-->
TeamTest is mapped with user: dbo and schema: dbo. The database role membership section at the bottom has both public and db_owner checked.
My fellow dba, domain\useraaa who is listed as the Properties database owner, is not mapped to the database.
When I use SSMS --> databases --> TeamTest --> Security -->Users --> right click New User
I get an error when I try to add my userid:
Create failed for User 'domain\useryyy'. (Microsoft.SqlServer.Smo)
The login already has an account under a different user name. (Microsoft SQL Server, Error: 15063)
When I run this query:
Use TeamTest
SELECT db_name(db_id()), sp.name as sysdb_owner, sp2.name as sysdbprincipals_owner
FROM sys.database_principals dp
JOIN sys.databases d
ON dp.sid <> d.owner_sid
JOIN sys.server_principals sp
ON sp.sid = d.owner_sid
JOIN sys.server_principals sp2
ON sp2.sid = dp.sid
WHERE dp.name = 'dbo' AND d.name = db_name(db_id())
I get the following:
sysdb_owner - - - sys.dbprincipals_owner
domain\useraaa - - - domain\useryyy
We have 2 other db's that have this same sid mismatch.
And we have about 10 other db's that have this mismatch:
sysdb_owner - - - sysdbprincipals_owner
domain\useraaa- - - sa
How could there be these db ownership mismatches? Some of these databases were restored from another, but I'm pretty sure that some of the db's were created from scratch.
Should I change the SID in the database_principals to my teammate's SID? If so, is there some kind of sp that does this?
Any insight would be greatly appreciated.
February 4, 2010 at 1:32 pm
When you restore a database from another system, the following things happen:
1) the owner is set to the user that is running the restore
a) this is why your coworker is the owner of the database, and does not have a user account in that database.
2) the users where the login SID and the user SID in the restored database match are linked. Where the user and login SID do not match, you end up with orphaned users.
a) this is what has happened to your account.
To correct, I recommend changing the owner of the database to the 'sa' account. Then, you run the procedure 'sp_change_users_login' with the parameter 'Report'. This will give you a list of orphaned users. You then use the same procedure to link those orphaned users to their appropriate login on that system.
Lookup that procedure in books online for how to fix orphaned users.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 4, 2010 at 2:27 pm
Thanks for the reply, Jeffrey.
I just changed the database owner to sa. That matched up the sids in sys.database_principals and sys.databases.
There were no orphans, since neither of us had ever been an actual db user in the first place.
Even though I thought some of the db's were created from scratch, they must have been restored from ones where I was the owner.
I appreciate your assistance.
February 4, 2010 at 4:00 pm
Great - glad I could help.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply