Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Script to drop SQL Server login and all owned objects using PowerShell


By:   |   Last Updated: 2014-08-13   |   Comments (2)   |   Related Tips: More > 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:

  1. In each SQL instance, search whether the SQL Server login exists. If no, exit.
  2. 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.
  3. If yes, search whether there are database roles or schemas owned by the database users. If no, drop database users, drop login and exit.
  4. 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.
  5. 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='(.+?)'[email protected]='(.+?)'") { $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).

  1. 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


  2. /*
    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
  3. After running the T-SQL script, three SQL Server logins and their "dependent" objects are created, you can verify via SSMS.


  4. 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.


  5. [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)

drop_login_result

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


Last Updated: 2014-08-13


get scripts

next tip button



About the author
MSSQLTips author Jeffrey Yao 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.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, August 14, 2014 - 12:56:42 PM - jeff_yao Back To Top

Thanks @Sri.


Wednesday, August 13, 2014 - 5:57:01 PM - Sri Back To Top

Good one!!


Learn more about SQL Server tools