March 27, 2013 at 9:34 am
I had recently move my data and log files to different locations on the server box to keep them all in the same location (house keeping maintenace). The databases owner used to be the previous DBA and when I reattached the database my domain account became the owner. I ran a query on sys.databases and the owner_sid has my domain account's SID, however, when I ran a SELECT [name], SUSER_NAME([owner_sid]) AS OWNERS_ID,[owner_sid], * FROM master.[sys].[databases] my databases except of model, msdb, and master have NULL for SUSER_NAME([owner_sid]). I can change the db owner to sa but I cannot change the owner to another domain account even though I know they exist. This discrepencies are pretty strange and I don't think there's any orphan users since the SID actually match.
Any ideas on what's going on?
March 27, 2013 at 10:39 am
I can believe I'm posting to my own question, but I think I find the problem.
If I ran the following sql statement:
SELECT SUSER_NAME([owner_sid]), SUSER_SNAME([owner_sid]) FROM [sys].[databases]
I get two columns, the first column shows NULL values for my databases if the owner is other than the sa account. The second column actually shows the domain account that it should. I guess that's answer to my question, I needed to use SUSER_SNAME even though MS states: "SUSER_NAME always returns NULL when used in Microsoft® SQL Server™ 2000. This system built-in function is included only for backward compatibility. Use SUSER_SNAME instead." I'm running MS SQL 2008R2, I guess SUSER_NAME also returns NULL with this version.
March 27, 2013 at 1:24 pm
If you gain access to the instance via a Windows Group and you do not have an explicit Login on the instance for your Windows User that can also explain why SUSER_NAME returns NULL but SUSER_SNAME returns the proper answer.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply