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


Script to delete orphaned users


Script to delete orphaned users

Author
Message
river1
river1
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2352 Visits: 1366
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
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6954 Visits: 4076
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;-)
Adi Cohn
Adi Cohn
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4503 Visits: 6515
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/
river1
river1
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2352 Visits: 1366
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
Richard M.
Richard M.
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1584 Visits: 2683
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
river1
river1
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2352 Visits: 1366
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
Sreejith M Nair
Sreejith M Nair
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 156
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
mcarter1027
mcarter1027
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 194
This script works good! Thank you!!
mcarter1027
mcarter1027
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 194
For Richard: This script works good! Thank you!!
erishi
erishi
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 244
Hi,

We have the same requirement but the questions I have is I do not want to delete the schema I just want to transfer the ownership to dbo so that I can delete the user in case it owns the schema.

How would I do that. identify the user. delete the user, if the database principle owns any data change it to DBO and delete. I need to do this on all the databases.

Any help on this is appreciated. !
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