Technical Article

Delete All Database User Accounts for a Given Server Login

,

Ever had the need to remove all database-level user accounts from SQL Server for a given server level login? Say an employee leaves your organization, and you need to remove her account from all databases. Do you do this manually? Do you use the GUI in SSMS? Do you script this out? What if the database level accounts do not have the same name as the login? What if the account name is different in every database? What if there are hundreds of databases on your server, making this a tedious exercise?


Fear not, here is a script that you can run on SQL Server that will drop all database-level user accounts for a specified login. Thanks to Jason Strate for this article which inspired me to create this script. Just set the variable @LoginName to the login for which you want accounts deleted, and execute.

/*===================================================================================================
2013/07/15 hakim.ali@SQLzen.com

SQL Server 2005 and higher.

Script to delete (drop) all database level user accounts for a given server login from all 
databases on a database server. This will drop users even if they have a different name from 
the server login, so long as the two are associated. This will not drop users from a database 
where there are schemas/roles owned by the user.

** USE ONLY IF YOU ARE AN EXPERIENCED DBA FAMILIAR WITH SERVER LOGINS, DATABASE USERS, ROLES, 
SCHEMAS ETC. USE AT YOUR OWN RISK. BACK UP ALL DATABASES BEFORE RUNNING. **
=====================================================================================================*/use [master]
go

--------------------------------------------------------------------------------------
-- Set the login name here for which you want to delete all database user accounts.
declare @LoginName nvarchar(200); set @LoginName = 'LOGIN_NAME_HERE'
--------------------------------------------------------------------------------------

declare @counter int
declare @sql nvarchar(1000)
declare @dbname nvarchar(200)

-- To allow for repeated running of this script in one session (for separate logins).
begin try drop table #DBUsers end try begin catch end catch

----------------------------------------------------------
-- Temp table to hold database user names for the login.
----------------------------------------------------------
create table #DBUsers
(IDint identity(1,1)
,LoginNamevarchar(200)
,DBvarchar(200)
,UserNamevarchar(200)
,Deletedbit
)

-- Add all user databases.
insert into #DBUsers
(LoginName
,DB
,Deleted
)
select@LoginName
,name
,1
fromsys.databases
wherename not in ('master','tempdb','model','msdb')
andis_read_only = 0
and[state] = 0 -- online
order byname

----------------------------------------------------------
-- Add database level users (if they exist) for the login.
----------------------------------------------------------
set @counter = (select min(ID) from #DBUsers)

while exists (select 1 from #DBUsers where ID >= @counter)
begin
set @dbname = (select db from #DBUsers where ID = @counter)
set @sql = '
updatetemp
settemp.UserName = users.name
fromsys.server_principalsas logins
inner join[' + @dbname + '].sys.database_principalsas users
on users.sid = logins.sid
and logins.name = ''' + @LoginName + '''
inner join#DBUsersas temp
on temp.DB = ''' + @dbname + ''''

exec sp_executesql @sql

set @counter = @counter + 1
end

-- Don't need databases where a login-corresponding user was not found.
delete#DBUsers
whereUserName is null

----------------------------------------------------------
-- Now drop the users.
----------------------------------------------------------
set @counter = (select min(ID) from #DBUsers)

while exists (select 1 from #DBUsers where ID >= @counter)
begin
select@sql = 'use [' + DB + ']; drop user [' + UserName + ']'
from#DBUsers
whereID = @counter

--select @sql
begin try exec sp_executesql @sql end try begin catch end catch
set @counter = @counter + 1
end

----------------------------------------------------------
-- Report on which users were/were not dropped.
----------------------------------------------------------
set @counter = (select min(ID) from #DBUsers)

while exists (select 1 from #DBUsers where ID >= @counter)
begin
set @dbname = (select db from #DBUsers where ID = @counter)
set @sql = '
updatetemp
settemp.Deleted = 0
fromsys.server_principalsas logins
inner join[' + @dbname + '].sys.database_principalsas users
on users.sid = logins.sid
and logins.name = ''' + @LoginName + '''
inner join#DBUsersas temp
on temp.DB = ''' + @dbname + ''''

exec sp_executesql @sql

set @counter = @counter + 1
end

-- This shows the users that were/were not dropped, and the database they belong to.
if exists (select 1 from #DBUsers)
begin
selectLoginName
,[Database]= DB
,UserName= UserName
,Deleted= case Deleted when 1 then 'Yes' else 'No !!!!!!' end
from#DBUsers
order byDB
end
else
begin
select [No Users Found] = 'No database-level users found on any database for the login "' + @LoginName + '".'
end

/*===================================================================================================
Not automatically dropping the login. If there are database level users that were not dropped, 
dropping the login will create orphaned users. Enable at your discretion.
=====================================================================================================*//*
set @sql = 'drop login [' + @LoginName + ']'
exec sp_executesql @sql
*/

Rate

4.1 (10)

Share

Share

Rate

4.1 (10)