SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Orphaned Users


Orphaned Users

Author
Message
boumerlin
boumerlin
SSC-Addicted
SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)

Group: General Forum Members
Points: 409 Visits: 680
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.



happycat59
happycat59
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8499 Visits: 3281
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)



boumerlin
boumerlin
SSC-Addicted
SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)

Group: General Forum Members
Points: 409 Visits: 680
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.



happycat59
happycat59
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8499 Visits: 3281
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.



boumerlin
boumerlin
SSC-Addicted
SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)

Group: General Forum Members
Points: 409 Visits: 680
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! :-P



JDeMoss
JDeMoss
Mr or Mrs. 500
Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)

Group: General Forum Members
Points: 503 Visits: 495
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
boumerlin
boumerlin
SSC-Addicted
SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)

Group: General Forum Members
Points: 409 Visits: 680
Thanks.....I had managed to do something very similar once I understood the nature of the issue! Hehe



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search