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

Script to delete orphaned users Expand / Collapse
Author
Message
Posted Wednesday, March 31, 2010 4:06 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 744, Visits: 1,056
Hi,

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 you
Post #893598
Posted Wednesday, March 31, 2010 4:31 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
its working in my side , please test before run it ; "PRINT @cmd"
for it
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










-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #893608
Posted Wednesday, March 31, 2010 4:40 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:01 AM
Points: 2,117, Visits: 5,440
Here 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.
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.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'')'

Adi


--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #893613
Posted Wednesday, March 31, 2010 7:49 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 744, Visits: 1,056
Bhuvnesh 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 solution
Post #893767
Posted Wednesday, March 31, 2010 8:30 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 22, 2014 4:56 AM
Points: 1,093, Visits: 2,617
Here's the solution we use:
-- 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 User

DECLARE @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 mycurs
FETCH NEXT FROM mycurs
INTO @username

WHILE @@FETCH_STATUS = 0
BEGIN

-- 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 @username
END

CLOSE mycurs
DEALLOCATE mycurs

DROP TABLE #users





_______________________________________________________________________
For better assistance in answering your questions, click here
Post #893824
Posted Thursday, April 1, 2010 4:18 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 744, Visits: 1,056
Thank you all for your replys.

I will use this one:


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER Procedure [dbo].[DropOrphanUserAccounts]
as
BEGIN

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 #UserAccountsToDelete
END
Post #894568
Posted Tuesday, June 7, 2011 1:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 7, 2013 9:02 AM
Points: 2, Visits: 102
You 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 on

l.sid = u.sid where l.sid is null and u.type not in ('A','R') and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest' and

u.name <> 'system_function_schema' and u.name <> 'sys' and not db_name() + '.' + u.name= 'msdb.MS_DataCollectorInternalUser')
else

select u.name as 'Orphaned Users' from master..sysxlogins l right join sysusers u on l.sid = u.sid where l.sid is null and

issqlrole <> 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 mycurs
FETCH NEXT FROM mycurs
INTO @username

WHILE @@FETCH_STATUS = 0
BEGIN

-- 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 @username
END

CLOSE mycurs
DEALLOCATE mycurs

DROP TABLE ##orphans
Post #1121311
Posted Monday, June 30, 2014 12:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 12:25 PM
Points: 2, Visits: 88
This script works good! Thank you!!
Post #1587750
Posted Monday, June 30, 2014 12:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 12:25 PM
Points: 2, Visits: 88
For Richard: This script works good! Thank you!!
Post #1587752
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse