Dropping SQL Users with PowerShell

As you may have noticed, I love PowerShell!

I have developed a series of functions over time which save me time and effort whilst still enabling me to provide a good service to my customers. I keep them all in a functions folder and call them whenever. I call it my PowerShell Box of Tricks

I am going to write a short post about each one over the next few weeks as I write my presentation on the same subject which I will be presenting to SQL User Groups.

Todays post is not about a question but about a routine task DBAs do. Dropping Logins

Whilst best practice says add users to active directory groups, add the group to roles and give the roles the correct permissions there are many situations where this is not done and DBAs are required to manually remove logins. This can be a time consuming task but one that is essential. There was a time at MyWork when this was achieved via a script that identified which servers had a users login and the task was to connect to each server in SSMS and remove the user from each database and then drop the server login. As you can imagine it was not done diligently. Prior to an audit I was tasked with ensuring that users that had left MyWork did not have logins to any databases. It was this that lead to the Checking for SQL Logins script and to this one

It starts exactly the same as the Checking for SQL Logins script by grabbing the list of SQL Servers from the text file and creating an array of user names including all the domains as I work in a multi-domain environment

image

Then iterate through each database ignoring those that may need special actions due to the application and call the drop method

image

Repeat the process for the servers and send or save the report as required. Simple and easy and has undoubtedly saved me many hours compared to the previous way of doing things 🙂

image

IMPORTANT NOTE

This script will not delete logins if they have granted permissions to other users. I always recommend running the Checking for SQL Logins script after running this script to ensure all logins have been dropped

This script can be found Drop-SQLUsers

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s