Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Orphaned Users Expand / Collapse
Author
Message
Posted Thursday, July 1, 2010 10:28 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, November 20, 2014 10:47 AM
Points: 88, Visits: 586
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.



Post #946318
Posted Thursday, July 1, 2010 10:24 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 7:40 PM
Points: 3,062, Visits: 2,673
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)



Post #946576
Posted Friday, July 2, 2010 9:32 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, November 20, 2014 10:47 AM
Points: 88, Visits: 586
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.


Post #946936
Posted Saturday, July 3, 2010 6:22 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 7:40 PM
Points: 3,062, Visits: 2,673
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.



Post #947192
Posted Tuesday, July 6, 2010 10:07 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, November 20, 2014 10:47 AM
Points: 88, Visits: 586
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!


Post #948005
Posted Tuesday, March 22, 2011 12:16 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 2:52 PM
Points: 489, Visits: 431
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
Post #1082224
Posted Tuesday, March 22, 2011 12:19 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, November 20, 2014 10:47 AM
Points: 88, Visits: 586
Thanks.....I had managed to do something very similar once I understood the nature of the issue!


Post #1082229
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse