2013/07/17

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
(                       ID          int identity(1,1)
                ,       LoginName   varchar(200)
                ,       DB          varchar(200)
                ,       UserName    varchar(200)
                ,       Deleted     bit
)

-- Add all user databases.
insert into #DBUsers
(                       LoginName
                ,       DB
                ,       Deleted
)
select                  @LoginName
                ,       name
                ,       1
from            sys.databases
where           name not in ('master','tempdb','model','msdb')
and             is_read_only = 0
and             [state] = -- online
order by        name

----------------------------------------------------------
-- 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 = '
        update          temp
        set             temp.UserName = users.name
        from            sys.server_principals                      as logins
        inner join      [' + @dbname + '].sys.database_principals  as users
                        on users.sid = logins.sid
                        and logins.name = ''' + @LoginName + '''
        inner join      #DBUsers                                   as 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
where           UserName 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
        where   ID = @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 = '
        update          temp
        set             temp.Deleted = 0
        from            sys.server_principals                       as logins
        inner join      [' + @dbname + '].sys.database_principals   as users
                        on users.sid = logins.sid
                        and logins.name = ''' + @LoginName + '''
        inner join      #DBUsers                                    as 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
        select                  LoginName
                        ,       [Database]     = DB
                        ,       UserName       = UserName
                        ,       Deleted        = case Deleted when 1 then 'Yes' else 'No !!!!!!' end
        from            #DBUsers
        order by        DB
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
*/



No comments:

Post a Comment