March 31, 2010 at 4:06 am
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
March 31, 2010 at 4:31 am
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;-)
March 31, 2010 at 4:40 am
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/
March 31, 2010 at 7:49 am
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
March 31, 2010 at 8:30 am
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
April 1, 2010 at 4:18 am
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 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
June 7, 2011 at 1:09 pm
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
June 30, 2014 at 12:42 pm
This script works good! Thank you!!
June 30, 2014 at 12:47 pm
For Richard: This script works good! Thank you!!
October 21, 2015 at 1:55 pm
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. !
November 3, 2015 at 10:13 pm
use [master]
go
create proc dbo.sp_Drop_OrphanedUsers
as
begin
set nocount on
-- get orphaned users
declare
@user varchar(max)
declare c_orphaned_user cursor for
select name
from sys.database_principals
where type in ('G','S','U')
and authentication_type<>2 -- Use this filter only if you are running on SQL Server 2012 and major versions and you have "contained databases"
and [sid] not in ( select [sid] from sys.server_principals where type in ('G','S','U') )
and name not in ('dbo','guest','INFORMATION_SCHEMA','sys','MS_DataCollectorInternalUser') open c_orphaned_user
fetch next from c_orphaned_user into @user
while(@@FETCH_STATUS=0)
begin
-- alter schemas for user
declare @schema_name varchar(max)
declare c_schema cursor for
select name from sys.schemas where USER_NAME(principal_id)=@user
open c_schema
fetch next from c_schema into @schema_name
while (@@FETCH_STATUS=0)
begin
declare @sql_schema varchar(max)
select @sql_schema='ALTER AUTHORIZATION ON SCHEMA::['+@schema_name+ '] TO [dbo]'
print @sql_schema
exec(@sql_schema)
fetch next from c_schema into @schema_name
end
close c_schema
deallocate c_schema
-- alter roles for user
declare @dp_name varchar(max)
declare c_database_principal cursor for
select name from sys.database_principals
where type='R' and user_name(owning_principal_id)=@user
open c_database_principal
fetch next from c_database_principal into @dp_name
while (@@FETCH_STATUS=0)
begin
declare @sql_database_principal varchar(max)
select @sql_database_principal ='ALTER AUTHORIZATION ON ROLE::['+@dp_name+ '] TO [dbo]'
print @sql_database_principal
exec(@sql_database_principal )
fetch next from c_database_principal into @dp_name
end
close c_database_principal
deallocate c_database_principal
-- drop roles for user
declare @role_name varchar(max)
declare c_role cursor for
select dp.name--,USER_NAME(member_principal_id)
from sys.database_role_members drm
inner join sys.database_principals dp
on dp.principal_id= drm.role_principal_id
where USER_NAME(member_principal_id)=@user
open c_role
fetch next from c_role into @role_name
while (@@FETCH_STATUS=0)
begin
declare @sql_role varchar(max)
select @sql_role='EXEC sp_droprolemember N'''+@role_name+''', N'''+@user+''''
print @sql_role
exec (@sql_role)
fetch next from c_role into @role_name
end
close c_role
deallocate c_role
-- drop user
declare @sql_user varchar(max)
set @sql_user='DROP USER ['+@user +']'
print @sql_user
exec (@sql_user)
fetch next from c_orphaned_user into @user
end
close c_orphaned_user
deallocate c_orphaned_user
set nocount off
end
go
-- mark stored procedure as a system stored procedure
exec sys.sp_MS_marksystemobject sp_Drop_OrphanedUsers
go
execute the stored procedure for specific database
USE [MyTestDB]
GO
EXEC sp_Drop_OrphanedUsers
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply