July 1, 2010 at 10:28 am
I have a question. I have seen this before and I was wondering why when doing a DB migration to a new server, the domain accounts don't show up as orphaned users. I can capture the orphaned SQL accounts running sp_change_users_login @Action='Report', but not the domain accounts.
It's not a big deal......I just don't understand why and I thought maybe somebody could explain.
July 1, 2010 at 10:24 pm
Active Directory associated a unique identifier with each user and group. This is stored by SQL Server with the login (in column SID of syslogins). In each user database, the "foreign key" relationship to syslogins is via the column SID in sysusers.
So, when you restore a database onto another server in the same domain, SQL can ask AD who the user is and get a valid answer.
For SQL Server logins, SQL Server creates a SID for the login and stores that SID in syslogins. When you restore a database with users that relate to SQL Logins, there is an issue if you did not create the logins with the same SID on each database server (this is an option that you can specify when using the CREATE LOGIN command)
July 2, 2010 at 9:32 am
But shouldn't sp_change_users_login @Action='Report' be returning to me everything in sys.database_principals that does not join to syslogins? I have 257 database logins, but only 26 of them join to syslogins at this point in time, based on a join between SID.
July 3, 2010 at 6:22 am
If the database principal was related to a login that is a windows user, there is no need to "fix" them. The function of sp_change_user_login is to align users to logins where the SID will never, ever match. If you add the windows user or group to SQL Server as a login, there is no need to do anything more since the SID from AD/Windows will be the correct SID and you are done - not need to fix anything with sp_change_user_login. Remember - AD/windows assigns the SID to the user and SQL simply uses that SID.
For SQL Server logins, the SIDs are assigned by SQL Server and issuing the same sp_addlogin/CREATE LOGIN command on different instances of SQL Server will result in a different SID (assuming that you do not specify value for the SID). This is why sp_change_user_login exists.
July 6, 2010 at 10:07 am
It makes sense with the use of the AD SID, though technically without a server login the users are orphaned. Thanks for explaining that. At least I know I'm not going mad! 😛
March 22, 2011 at 12:16 pm
boumerlin,
I have the same issue as you stated in this thread. The difference is that I want to drop all of these "orphaned" users that exist in AD, but still are considered orphaned since they are not linked to a defined login.
Here is the script that I used to this.
-- Return database users (for each db) orphaned from any login.
-- results table
CREATE TABLE #orphy (DatabaseName NVARCHAR(128), UserName NVARCHAR(128))
declare @sql nvarchar(500)
SET @sql=
'Select ''?'' as DBName, sdp.name as UserName
From [?].sys.database_principals sdp
Left Join [?].sys.server_principals ssp On sdp.sid = ssp.sid
Where ssp.sid is null
and sdp.type in (''S'',''U'',''G'')
and sdp.name not in
(''guest'', ''INFORMATION_SCHEMA'', ''sys'', ''BROKER_USER'', ''dbo'')'
--insert the results from each database to temp table
INSERT INTO #orphy exec SP_MSforeachDB @sql
SELECT * FROM #orphy
-- Use this to generate the Drop schema and drop user; (results to text)
Select 'USE [' + DatabaseName + ']' + char(13) + char(10) + 'GO' + char(13) + char(10)
+ 'IF EXISTS (SELECT * FROM sys.schemas WHERE name = N' + '''' + UserName + ''')' + char(13) + char(10)
+ 'DROP SCHEMA [' + UserName + ']' + char(13) + char(10) + 'GO' + char(13) + char(10)
+ 'DROP USER [' + UserName + ']' + char(13) + char(10) + 'GO'
from #orphy Order by DatabaseName
March 22, 2011 at 12:19 pm
Thanks.....I had managed to do something very similar once I understood the nature of the issue! :hehe:
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply