InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 04:45:04 GMT20http://www.sqlservercentral.com/Forums/Topic946318-1526-1.aspxThanks.....I had managed to do something very similar once I understood the nature of the issue! :hehe:Tue, 22 Mar 2011 12:19:31 GMTboumerlinhttp://www.sqlservercentral.com/Forums/Topic946318-1526-1.aspxboumerlin,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 tableCREATE 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 tableINSERT INTO #orphy exec SP_MSforeachDB @sqlSELECT * 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 DatabaseNameTue, 22 Mar 2011 12:16:31 GMTJDeMosshttp://www.sqlservercentral.com/Forums/Topic946318-1526-1.aspxIt 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! :-PTue, 06 Jul 2010 10:07:01 GMTboumerlinhttp://www.sqlservercentral.com/Forums/Topic946318-1526-1.aspxIf 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.Sat, 03 Jul 2010 06:22:44 GMThappycat59http://www.sqlservercentral.com/Forums/Topic946318-1526-1.aspxBut 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.Fri, 02 Jul 2010 09:32:01 GMTboumerlinhttp://www.sqlservercentral.com/Forums/Topic946318-1526-1.aspxActive 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)Thu, 01 Jul 2010 22:24:18 GMThappycat59http://www.sqlservercentral.com/Forums/Topic946318-1526-1.aspxI 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.Thu, 01 Jul 2010 10:28:12 GMTboumerlin