By: Jeffrey Yao | Comments (2) | Related: > Security
Problem
It's a common task that DBAs need to drop SQL Server logins after a user leaves a company or some business scenario changes. Usually this is not a big task, but in a worst case scenario the SQL Server login may have database user accounts in multiple databases with roles and schemas owned by the user in some databases and the schemas may own tables / views / stored procedures. To make this even more complex, the SQL Server logins may exist in multiple SQL instances. So how do you safely and easily delete SQL Server logins and all of the objects they may own?
Solution
I created a PowerShell script (requires version 3 or later) to do this and it can be run to either drop the objects and login or if run in debug mode to just output a report of SQL commands that should be run.
These are the steps I came up with to do this:
- In each SQL instance, search whether the SQL Server login exists. If no, exit.
- If yes, search each database in the SQL Server instance to check whether there are database users belonging to the SQL login. If no, drop login and exit.
- If yes, search whether there are database roles or schemas owned by the database users. If no, drop database users, drop login and exit.
- If yes for database roles, drop roles, if yes for schemas, search whether there are tables/views/stored procedures/functions owned by the schema. If no, drop schemas, drop database users, drop login and exit.
- If yes, drop the corresponding tables/views/stored procedures/functions, drop schemas, drop database users, drop login and exit.
Note: in step 4 and 5, for simplicity, I just drop the schemas and objects. But more often, you may want to change the owners of the affected schemas and objects before dropping.
I will use PowerShell and SMO for the solution, which to me is more elegant than using lengthy dynamic T-SQL and the undocumented sp_MSForEachDB to achieve the same result.
# this is to drop logins and their corresponding db users / schemas / objects
#requires -version 3.0
add-type -AssemblyName "microsoft.sqlserver.smo, version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" #version=11.0.0.0 means sql2012, 10.0.0.0 means sql2008/R2
[string[]]$server_list='tp_w520'# can have multiple server instances separated by ","
[string[]]$login_list = 'tp_w520\jeff_yao', 'mytest', 'XYZ' # change to your own list
[boolean]$debug = $true; # $true = printing out the t-sql; $false = drop objects directly inside PS
foreach ($svr in $server_list)
{
$s = New-Object "microsoft.sqlserver.management.smo.server" $svr
if ($debug) {":connect $svr";}
$logins = $s.logins | ? { $_.name -in $login_list }
if ($logins.count -gt 0)
{
foreach ($db in $s.databases)
{
$users = $db.users | ? {$_.login -in $login_list};
if ($users.name -contains 'dbo')
{write-error -message "The database [$($db.name)] is owned by $($db.users['dbo'].login), please change db owner to sa before continuing..";
return;
}
$roles = $db.roles | ? {$_.owner -in $users.name};
if ($roles.count -gt 0)
{
if ($debug) {"use $db ";}
$roles | % { if ($debug) {"drop role [$($_.name)];"} else {$_.drop()}; }
if ($debug) {"go";}
}
if ($users.count -gt 0)
{
if($debug) {"use $db";}
$schemas = $db.schemas | ? {$_.owner -in $users.name };
if ($schemas.count -gt 0)
{
foreach ($sm in $schemas )
{
$sm.enumownedobjects() |
foreach { if($_.xpathexpression.item(2) -match "@name='(.+?)'.+?@schema='(.+?)'") { $sc_name=$matches[2]; $obj_name=$matches[1]; };
if ($debug)
{
switch ($_.xpathexpression.item(2).name)
{ 'Table' {"drop table [$sc_name].[$obj_name]";break;}
'View' {"drop view [$sc_name].[$obj_name];";break;}
'StoredProcedure' {"drop proc [$sc_name].[$obj_name];";break;}
'UserDefinedFunction' {"drop function [$sc_name].[$obj_name];"; break;}
'UserDefinedDataType' {"drop type [$sc_name].[$obj_name];"; break;}
}
}
else
{ #depending on the owned object type, we need to drop them in a native way
switch ($_.xpathexpression.item(2).name )
{ 'Table' {$t = $db.tables.Item($obj_name, $sc_name); $t.drop();break;}
'View' {$v=$db.views.item($obj_name, $sc_name); $v.drop();break;}
'StoredProcedure' {$p=$db.StoredProcedures.Item($obj_name, $sc_name); $p.drop();break;}
'UserDefinedFunction' {$f=$db.UserDefinedFunctions.Item($obj_name, $sc_name); $f.drop(); break;}
'UserDefinedDataType' {$f=$db.UserDefinedDataTypes.Item($obj_name, $sc_name); $f.drop(); break;}
}
}
}
if($debug) {"drop schema [$($sm.name)];"} else{ $sm.drop();}
}#drop schemas
}#schemas.count -gt 0
$users| % {if ($debug) {"drop user $_;"} else {$_.drop();} };
if ($users.count -gt 0) {if ($debug) {"go"}}
}#users.count -gt 0
}#if $users.count -gt 0
if ($debug) {"use master"}
$logins | % { if($debug) {"drop login $_;"} else {$_.drop();} }
if ($debug) {"go"}
}#if $logins.count -gt 0
}
Fig - 1
Demo to Drop SQL Server Logins and All Associated Database Objects
I will do a quick demo by first creating a few SQL Server logins / users and related objects including tables, views, stored procedures and a user defined functions using the following T-SQL code (you may need to make changes according to your environment, such as database name or domain account name).
- Open SQL Server Management Studio (SSMS), connect to a SQL Server instance, open a new query window and copy and paste the following code into the new window. Make any necessary changes (database names or login names) and then execute the script
- After running the T-SQL script, three SQL Server logins and their "dependent" objects are created, you can verify via SSMS.
- Start the PowerShell ISE, copy and paste the PowerShell script in Fig -1, make any necessary changes to the first three lines and the execute the script in ISE.
/*
we create 3 logins and various users in either AdventureWorks2012 or AdventureWorksDW2012
and we also create roles / schema related to these users plus objects (table, view, SP, function, type) under the schemas
*/
use master
create login [TP_W520\jeff_yao] from windows; -- change to a domain account of your choice
create login [XYZ] with password='xYz111', check_policy=off;
create login MyTest with password='h$ll0W0rl$', check_policy=off;
go
use adventureworks2012
create user [TP_W520\jeff_yao] for login [TP_W520\jeff_yao]
go
create schema jyao authorization [TP_W520\jeff_yao]
go
create table jyao.tblTest (a int)
go
create view jyao.vwTest as select * from jyao.tblTest
go
create type jyao.SSN from char(9);
create user MyTest2 for login [MyTest];
go
CREATE ROLE [test] authorization [tp_w520\jeff_yao]
go
use AdventureWorksDW2012
create user [XYZ] for login [XYZ];
go
create role GoodXYZ authorization [XYZ];
go
create schema myXYZ authorization [XYZ];
go
create proc myXYZ.uspA as raiserror ('hello world', 10, 1)
go
create user MyTest3 for login [MyTest]
go
create schema t authorization MyTest3
go
create function t.ufMytest3 (@mydate datetime) returns int as begin return datepart(second, @mydate) end;
go
create user [TP_W520\jeff_yao] for login [TP_W520\jeff_yao]
go
create schema my authorization [TP_W520\jeff_yao]
go
[string[]]$server_list='tp_w520'# multiple server instances, separated by comma , [string[]]$login_list = 'tp_w520\jeff_yao', 'mytest', 'XYZ' # the sql logins to be dropped [boolean]$debug = $true; #$true means printing out t-sql; $false means drop objects directly
In my PowerShell ISE (version 4.0), I get the following output when I use $debug = $true. (If $debug = $false, the logins and related objects will be dropped)
If you use the debug mode, you can copy and paste the output to an SSMS window and then run the script line by line, or review what will be dropped and make necessary changes (such as changing the schema of tables, views, etc.) before running it.
Note: if there are Foreign Key relationships between tables, the script may not drop the tables in the desired sequence, i.e. drop the child tables first before dropping the parent tables. You can run the script several times to try to drop all tables, but there may be instances where you will need to deal with this manually by removing the Foreign Key constraints.
Next Steps
- It's easy to modify the PowerShell script to be a cmdlet which can accept parameters to do the login cleanup, but it is interesting to run the script directly inside the PowerShell IDE to see the results interactively after adjusting parameter values.
- This solution was tested in SQL Server 2012 and should be applicable for SQL Server 2005 and above.
- Read more tips related to this type of work
About the author
Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
