Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

Has anyone ever seen anything like this before? I have a WINDOWS_LOGIN AD user that has a record in sys.server_principals with a SID and can login fine. But in a few databases, they have a record in sys.database_principals with a NULL SID.

I have scoured Google trying to find out what's causing this as it's causing problems with my SQL Server auditing tools but have had no luck.

The first query is sys.server_principals and the second is sys.database_principals.

share|improve this question
Sounds like an orphaned user... were these databases restored from somewhere else? – Aaron Bertrand♦ Aug 1 '12 at 15:44
How can it be orphaned if it's a windows login type and that user has a record in the sys.server_principals table? In addition running EXEC sp_change_users_login 'Report' doesn't report it... – Brad Aug 1 '12 at 15:55
Again, where did the database come from? Was it originally on another server? If so, the sid for that server_principal on the other server does not match the sid for the same windows login on the new server. – Aaron Bertrand♦ Aug 1 '12 at 16:12
The database may have been restored from another server, but that other server would still be on the same domain and all the IDs would be associated with that domain. So this user, DOMAIN\myUser has the same SID on all our servers. Yet in some databases, there is a entry for this user without a SID. There are only 20 instances of this happening across 15 servers. – Brad Aug 1 '12 at 16:20
1  
I'm not saying any of these things ARE THE ANSWER (this is why they are comments, not an answer). I'm just poking at things to look at. The first clue seems to have been a good hunch: these databases did not originate on the server they're on. It's possible when you restored the database to the current server, the user was not validated correctly because the SIDs didn't match, and who knows, maybe that causes SQL to make the SID NULL. Sorry, I don't have much else to offer. – Aaron Bertrand♦ Aug 1 '12 at 16:39
show 3 more comments

migrated from stackoverflow.com Aug 1 '12 at 15:00

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.