March 10, 2010 at 3:28 pm
I have created some databases on my SQL Server 2008 SP1 Standard installation by restoring them from a SQL Server 2000 instance. After restoring them, I updated the compatibility level to 100 and the next thing I did was run the sp_changedbowner command to set the db owner to another login, besides myself. Now, when I look at the list of databases in SSMS 2008, I am still listed as the owner of the database in some places and not in others. When I look at the users in the database, I see that the login associated with dbo is the login I changed it to using the sp_changedbowner.
Here's how it breaks down. First I run the following two queries for comparison:
SELECT *
FROM master.sys.databases
SELECT *
FROM master.sys.server_principals
When I look at the owner_sid from the first query, I see an SID that does not even show up in the second query. (This may be due to the fact that the account I am using to login with (windows authentication) has access through a Windows group that is setup in the SYSADMIN server role.)
Next I run the following query from within one of these databases:
SELECT *
FROM sys.database_principals
In the results from this query the owner_sid again matches the login that I setup using the sp_changedbowner.
So, to recap - the owner shows up wrong in the database listing on SSMS and when querying sys.databases and it shows up properly when looking at the users in SSMS and when querying sys.database_principals.
This doesn't seem to be causing any problems because all access seems to be working properly, I'm just confused why these show up differently and I was wondering if anyone else has seen this?
Thanks.
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
March 10, 2010 at 4:18 pm
Sounds like SSMS may be caching that. Run through the proc again and verify that it remains unchanged, log out of SSMS and back in and check again.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 10, 2010 at 4:33 pm
Caching sounds plausible but both these databases have been out there for at least a week and during that time both the server and my machine have been restarted. I have even looked through SSMS on other machines and it is still the same.
I didn't think about it why I was writing but I believe SSMS gets it's owner information from the sys.databases table. That is why I see myself as the owner both in SSMS and this table. When I look at the database, both gui and sys.database_principals, everything looks fine. I just can't figure out why the sys.databases table didn't get updated.
I wonder if I could force that table to update by updating the owner_sid in sys.databases with the SID of the proper login? (I'll have to try it on a test box.)
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
March 10, 2010 at 4:39 pm
Another option would be to change it directly from the GUI. Run a trace while changing and see if there is something else besides the proc that the GUI does to change the Owner.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply