InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 03:24:44 GMT20http://www.sqlservercentral.com/Forums/Topic893598-146-1.aspxYou can also use the below : Here it lists all Orphaned users on existing DB and delete Ids and associated Schema .DECLARE @UserName varchar(100), @SQL as varchar(max)create table ##orphans (orphan varchar(128))-- 1)if substring(@@version, 1, 26) not in ('Microsoft SQL Server 2000', 'Microsoft SQL Server 2000') insert into ##orphans select u.name as 'Orphaned Users' from sys.server_principals l right join sys.database_principals u onl.sid = u.sid where l.sid is null and u.type not in ('A','R') and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest' andu.name <> 'system_function_schema' and u.name <> 'sys' and not db_name() + '.' + u.name= 'msdb.MS_DataCollectorInternalUser') elseselect u.name as 'Orphaned Users' from master..sysxlogins l right join sysusers u on l.sid = u.sid where l.sid is null andissqlrole <> 1 and isapprole <> 1 and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest' and u.name <> 'system_function_schema')DECLARE mycurs CURSOR FOR SELECT orphan FROM ##orphans OPEN mycursFETCH NEXT FROM mycurs INTO @usernameWHILE @@FETCH_STATUS = 0BEGIN -- 2) BEGIN TRY EXEC sp_change_users_login @Action='update_one', @UserNamePattern=@UserName, @LoginName=@UserName END TRY BEGIN CATCH print @Username + ' does not exist as a Login. Deleting. ' -- 3) BEGIN TRY SET @SQL = 'DROP SCHEMA [' + @UserName +']' EXEC (@SQL) END TRY BEGIN CATCH END CATCH -- 4) SET @SQL = 'DROP USER [' + @UserName + ']' Print @SQL EXEC (@SQL) END CATCH FETCH NEXT FROM mycurs INTO @usernameENDCLOSE mycursDEALLOCATE mycursDROP TABLE ##orphansTue, 07 Jun 2011 13:09:03 GMTSreejith M Nairhttp://www.sqlservercentral.com/Forums/Topic893598-146-1.aspxThank you all for your replys.I will use this one:SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER Procedure [dbo].[DropOrphanUserAccounts]asBEGIN set nocount on create table #UserAccountsToDelete ( ID int identity, DB varchar(100), [User] varchar(100) ) DECLARE @DatabaseName nvarchar(100) DECLARE my_DBs CURSOR FAST_FORWARD FOR select [name] from master.sys.databases WHERE [NAME] NOT IN ('MASTER','MODEL','TEMPDB','MSDB','ADVENTUREWORKS') OPEN my_DBs FETCH NEXT FROM my_DBs INTO @databasename WHILE @@FETCH_STATUS = 0 Begin insert #UserAccountsToDelete exec('select '''+@databasename+''',sdp.name from '+@DatabaseName+'.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'')') FETCH NEXT FROM my_DBs INTO @databasename END CLOSE my_DBs DEALLOCATE my_DBs declare @contador int declare @ciclo as int declare @BD varchar(100) declare @User varchar(100) set @ciclo =1 set @contador =(select max(id) from #UserAccountsToDelete) WHILE(@ciclo < = @contador ) begin set @BD = (select db from #UserAccountsToDelete where id =@ciclo) set @User = (select [user] from #UserAccountsToDelete where id =@ciclo) begin try exec ('use '+@bd+ ' drop schema ['+@user+']') end try begin catch end catch exec ('use '+@bd+ ' drop user ['+@user+']') set @ciclo =@ciclo +1 end drop table #UserAccountsToDeleteENDThu, 01 Apr 2010 04:18:30 GMTriver1http://www.sqlservercentral.com/Forums/Topic893598-146-1.aspxHere's the solution we use:[code="sql"]-- 1) List all Orphaned users on existing DB-- 2) Associate the DB User with the server Login, if existing-- 3) If 2) fails, try to delete associated DB Schema-- 4) Drop UserDECLARE @UserName varchar(100), @SQL as varchar(max)CREATE TABLE #users ( Username varchar(100), UserSID varbinary (85) )-- 1)INSERT INTO #users exec sp_change_users_login @Action='Report'DECLARE mycurs CURSOR FOR SELECT Username FROM #users OPEN mycursFETCH NEXT FROM mycurs INTO @usernameWHILE @@FETCH_STATUS = 0BEGIN -- 2) BEGIN TRY EXEC sp_change_users_login @Action='update_one', @UserNamePattern=@UserName, @LoginName=@UserName END TRY BEGIN CATCH print @Username + ' does not exist as a Login. Deleting. ' -- 3) BEGIN TRY SET @SQL = 'DROP SCHEMA ' + @UserName EXEC (@SQL) END TRY BEGIN CATCH END CATCH -- 4) SET @SQL = 'DROP USER ' + @UserName EXEC (@SQL) END CATCH FETCH NEXT FROM mycurs INTO @usernameENDCLOSE mycursDEALLOCATE mycursDROP TABLE #users[/code]Wed, 31 Mar 2010 08:30:40 GMTRichard M.http://www.sqlservercentral.com/Forums/Topic893598-146-1.aspxBhuvnesh your solution is not what i asked for.Your solution is not to delete User accounts that are orphan because the login was deleted...But thanks.I will try the other solutionWed, 31 Mar 2010 07:49:38 GMTriver1http://www.sqlservercentral.com/Forums/Topic893598-146-1.aspxHere is one way of doing so. The script bellow creates a script that deletes all orphaned users from each database. Notice that the script is using an undocumented procedure (sp_MSforeachdb) that could be modified in the future by Microsoft without any warning, so using it in a user application is something that you shouldn’t do (I only use it in some script that only me or another DBA is using). Also you should check the script that is created for other users that might have been created in the database, and that you don’t want to drop them.[code]exec sp_MSforeachdb 'use ?; select ''use ? '' + ''go'' + ''drop user '' + sdp.name from sys.database_principals sdp left join sys.server_principals ssp on sdp.sid = ssp.sidwhere ssp.sid is null and sdp.type in (''S'',''U'',''G'')and sdp.name not in (''guest'', ''INFORMATION_SCHEMA'', ''sys'', ''BROKER_USER'', ''dbo'')'[/code]AdiWed, 31 Mar 2010 04:40:30 GMTAdi Cohn-120898http://www.sqlservercentral.com/Forums/Topic893598-146-1.aspxits working in my side , please test before run it ; [b]"PRINT @cmd"[/b]for it [code="sql"]USE [master]GO SET NOCOUNT ON DECLARE @cmd varchar(4000) BEGIN TRY Create table #Orphan_User_Tbl ( [Database_Name] sysname , [Orphaned_User] sysname ) SET NOCOUNT ON Create table #Windows_Auth_Orphan_User ( [Str] nvarchar(300) ) DECLARE @DBCount INT,@MaxCount INT, @Qry nvarchar(4000), @DBName sysname DECLARE @db_list table (dbname nvarchar(100),ID int identity) SET @Qry = '' SET @DBCount = 1 INSERT INTO @db_list(dbname ) SELECT name FROM sys.sysdatabases WHERE dbid > 4 SELECT @MaxCount = MAX(ID) FROM @db_list WHILE(@DBCount < = @MaxCount ) BEGIN SELECT @DBName = dbname FROM @db_list WHERE id = @DBCount SET @Qry = ' SELECT ''' + @DBName + ''' AS [Database Name], CAST(su.name AS sysname) AS [Orphaned User] FROM ' + QUOTENAME(@DBName) + '..sysusers su inner join master..syslogins b on su.name=b.name where su.sid is not null and su.sid not in (0x00,0x01) and su.sid <> b.sid' INSERT INTO #Orphan_User_Tbl EXEC (@Qry) SET @DBCount = @DBCount + 1 END DECLARE MC CURSOR READ_ONLY FOR SELECT [Database_Name]+ '..sp_change_users_login ''UPDATE_ONE'' , ''' + Orphaned_User + ''' ,''' + Orphaned_User + ''';' FROM #Orphan_User_Tbl ORDER BY [Database_Name], [Orphaned_User] OPEN MC FETCH NEXT FROM MC INTO @cmd WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN TRY PRINT @cmd Execute (@cmd) END TRY BEGIN CATCH SELECT ERROR_NUMBER()ErrorNumber,ERROR_LINE() AS ErrorLine,ERROR_MESSAGE() AS ErrorMessage FETCH NEXT FROM MC INTO @cmd CONTINUE; END CATCH FETCH NEXT FROM MC INTO @cmd END CLOSE MC DEALLOCATE MC --------------------------------------------------------------------------------------------------------- ----Fixing Windows autheticated user----------------- SET @DBCount = 1 WHILE(@DBCount < = @MaxCount ) BEGIN SELECT @DBName = ''+ dbname + '' FROM @db_list WHERE id = @DBCount SET @Qry = 'SELECT '' USE [' + @DBName + ' ]; ALTER USER ['' + NAME + ''] WITH LOGIN = [ '' + NAME + '']'' FROM msdb.sys.database_principals WHERE ( type_desc = ''WINDOWS_GROUP'' OR type_desc = ''WINDOWS_USER'' ) AND name NOT like ''%dbo%'' AND name NOT LIKE ''%#%''' INSERT INTO #Windows_Auth_Orphan_User EXEC (@Qry) SET @DBCount = @DBCount + 1 END --SELECT * FROM #Windows_Auth_Orphan_User DECLARE MC CURSOR READ_ONLY FOR SELECT [Str]FROM #Windows_Auth_Orphan_User OPEN MC FETCH NEXT FROM MC INTO @cmd WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN TRY PRINT @cmd Execute (@cmd) END TRY BEGIN CATCH SELECT ERROR_NUMBER()ErrorNumber,ERROR_LINE() AS ErrorLine,ERROR_MESSAGE() AS ErrorMessage FETCH NEXT FROM MC INTO @cmd CONTINUE; END CATCH FETCH NEXT FROM MC INTO @cmd END CLOSE MC DEALLOCATE MC DROP Table #Orphan_User_Tbl Drop table #Windows_Auth_Orphan_User END TRY BEGIN CATCH SELECT ERROR_NUMBER()ErrorNumber, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage END CATCH[/code]Wed, 31 Mar 2010 04:31:42 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic893598-146-1.aspxHi, I have deleted some logins from my SQL Server 2005 servers, now i need a script to delete the users of this logins from my user databases does sameone have a script like this?Thank youWed, 31 Mar 2010 04:06:29 GMTriver1