Brad McGehee’s “How to Become an Exceptional DBA” is one of the more well-thumbed books in my library, right next to Kalen Delaney’s “Inside SQL Server 2000”. Brad’s book is an excellent example of recommended reading for changing the way you think and work. I admit that, the first time I read it, I thought many of Brad’s suggestions were, quite frankly, utopian. But it turns out that the ‘utopia’ is actually a realizable goal.

Like many of us, I have been guilty of not seeing beyond the little world of my immediate professional actions, and of being confident that nothing beyond those actions was needed for me to do my job well. With hindsight, I will say that every word Brad writes his sensational handbook is perfectly applicable to any organization, be it small or large. What makes a difference is the scale of your thinking: Big or Small.

Brad’s lessons influenced me so much that, when I learned what little I know of PowerShell, I decided to turn his DBA checklists into PowerShell scripts, and I’d like to share some of these scripts (which I use daily) with you. To make sure this is not tiresome reading, I’ve divided my scripts into 3 parts, and the first is: Check/Fix the Orphan Users.

I actually decided to start with this example because it was an almost daily procedure for me (due to the circumstances at the time), and it took a considerable amount of time. As we are lovers of PowerShell, I also want to share with you how this fantastic shell solved my problem with a uniform, repeatable and consistent solution. We’ll cover more usual situations in the future articles in this little series

A Little Background

John IDontKnow, Robert IDontknowEither and You work as DBAs for a major data center in 3 daily shifts, of which yours is from 7:00 AM to 16:00 PM. All three of you have sufficient autonomy to do what you need, and you are responsible for the center’s top client, who has a complex evolving OLTP and OLAP infrastructure, and a ton of SQL Servers.

You have a large security issue that affects your work: For contractual reasons, some of your client’s employees have access to production servers, and for their own internal reasons they drop logins almost daily. And just for fun, some logins are recreated again at later dates, but not with the same SID, making them Orphan Users. As a result, one of your main routines in the morning (and it takes a reasonable amount of time) is to check for these orphaned users.

Your manager, a senior DBA in the company (*gasp* a tech-savvy manager) asks you to search for all the orphaned users in the environment in order to implement an appropriate security policy and send it to the client. “At last!” you happily respond, “OK, I’ll do it today“. Surely it would have been better to have answered: “If you want to see that data now, I have a process that runs every night looking for orphaned users, logging the data in a table” After all, you are an Exceptional DBA and you use PowerShell.

Check/Fix the Orphan Users

In this script, we will work with PowerShell 2.0 and error handling (in case the SQL Servers are offline or some other problem occurs), and we will also be using a function module (.psm1) to contain the functions that we’ll use in our scripts and make them easily available; It contains:

Save-MSSQLMsg : This function generates a log file for scripts (Success, error , or simply an information message)

Map-MSSQLUser This function maps the user with their Login – It only maps Users and Logins with the same name.

Drop-MSSQLUser This function drops any users who don’t have a Login (it checks for Logins and Users with the same name, again)

Get-MSSQLUser This function Returns all an object with information about users :
LineNumber, Date, ServerName, DatabaseName, UserName, CreateDate,
DateLastModified, AsymMetricKey, DefaultSchema, HasDBAccess,ID, LoginType, Login,
Orphan, LoginExists
The Orphan and LoginExists properties let you know whether a User is orphaned (i.e. does not have a Login) and whether there is Login exists with the same name as, but is not mapped to, this user.

The first thing to do is create the two folders for the server list file and the ps1 script files (and function.psm1 module) respectively. In this case:

• The servers list file is stored in C:\PS\servers\
• The log file is created in C:\TEMP, which is the default path from the Save-MSSQLMsg function. As this path is passed as a parameter, you can very simply change this to put in a new path where the function is called
• And ps1 scripts (and function.psm1 module) are in C:\PS\PSScripts

The flat file containing the list of servers will have to contain all SQL Servers in your environment, and if you have more than one instance that you want to monitor, each instance must also be listed. It should looks like this:

Server1
Server2
Server3\Instance1
Server3\Instance2

If you do not have SQL Server 2008 but still want to use the 2008 PowerShell Provider, then Tim Benninghoff has an excellent guide on how to make that possible. However, you will have take ‘Profiles’ into account if that is the case.

PowerShell Profiles

When you add aliases, functions, and variables, you are actually adding them only to the current Windows PowerShell session. If you exit the session or close Windows PowerShell, the changes are lost. To retain these changes, you can create a Windows PowerShell profile and add the aliases, functions, and variables to the profiles. The profile is loaded every time that Windows PowerShell starts.” Windows PowerShell Profiles.

So how are we going to use a CmdExec job with the invoke-cmd cmdlet when the SQL Server 2008 Provider isn’t installed? Simply adjust all user profiles to load the appropriate Snapin and provider from SQL Server 2008, and avoid the problem. We also load our function module (.psm1) into the profile So that all the required functions will be available when we start the Shell.To do that, open this file (or create it if it doesn’t already exist): %windir%\system32\WindowsPowerShell\v1.0\Microsoft.PowerShell_profile.ps1 …and type:

If you want to see the snap-ins registered, start a new PowerShell session, type:

… And the SQL Server 2008 should be there, as shown below :

We can see our functions module in the same way; just type:

And you should see this:

Now all shells and users will be able to use the SQL Server snap-in and our functions.

The Scripts

With that done, lets actually create that function script module (functions.psm1) that will do the work of generating logs and mapping or dropping logins.

This is saved in the Windows PowerShell Script Module format file (.psm1) under the file path for all of our scripts (C:\PS\PSScripts\Functions.psm1) . Some sample commands you could pass to this module include:

Now it’s time to look for those Orphaned Users.

How to Return Orphaned Users

The Orphan property tells us if a given user has a associated Login. If not, it’s a orphaned user – it’s that simple. We use it in conjunction with the hasdbaccess property too, to list only users with access to the database in question.

We can also list all users who are orphaned but share their name with a Server Login; we just need to add the LoginExists property to the conditions.

Any users this returns just need to be mapped with their logins.

What about outputting this data to a SQL Server table?<

First, create the necessary tblOrphanedUsers table in the data repository…

And then run this:

You can choose the properties, path and name of your XML File, and obviously you server and database repositories ( -ServerInstance and -database). But you want to get this information in a SQL table, yes? No problem, It’s a simple Xquery, and you can see the results below:

How to Fix Orphaned Users with PowerShell

In this case, we can fix these orphaned users in two ways. One is by mapping them to their Login (which has the same name) and another is by dropping the users who don’t share their names with a Login. Let’s take a look at these processes now.

Mapping Users with their Login

Nothing could be simpler. First, filter the objects by the orphan and LoginExists properties (the latter being ‘true’), and then call the Map-MSSQLUser function:

Dropping users without a Login

This is just as simple to do as the mapping; we just change the LoginExists filter to ‘false’, and the call the Drop-MSSQLUser function from our PowerShell module:

Bear in mind that if we omit the server path in the Get-MSSQLUser function call, the current server is used.

As I said earlier, for the purposes of keeping track of what’s going on, the Save-MSSQLMsg () function generates a log file of errors ,successes and even just status messages in the $PathLogFile folder (in this case we use default value c:\temp). To identify each log, it uses the name of the running script (passed as a parameter) plus the date and time, as the figure below shows:

This will make tracking down the details for any particular scheduled job incredibly easy, and as you can see, the log contents themselves are fairly well formatted:

Scheduling the Check.

When scheduling, we can simply take the examples of mapping and dropping users above, save them into .ps1 file and run it. Let’s say we save script to Map users (as seen above) into a file called MapOrphanedUsers.ps1 . If you are using SQL Server 2008, you would just need to then create a PowerShell Job and run the .Ps1 script by entering the storage path of your scripts into the ‘command’ field:

Alternatively, you can run the script with a cmdexec-type job, ensuring the command field contains:

After that, you just need to schedule the job, and you’re finished! Clearly, this is a very particular case where others users can drop logins behind your back, but let’s go to a more normal scenario:

Mapping Users after Restore

In this Datacenter, You have a Job that runs every Sunday and restores the production Databases to a development environment. So in this case, your production server X restores N databases to the development server D1, the production server Y restore N databases for development D2… Etc.

So , to make sure you can chose which file list the servers will be passing as parameters in your powershell script, you configure a flat ServersRestore.txt file especially for this scenario, and it looks like this :

The list format is, fairly obviously, Server Instance followed by individual databases to be restored, all separated by commas. With that done, you configure one last job to run at 6:00 AM (after all the other restore jobs have finished) to fix the inevitable orphaned users in all the servers and databases listed in ServersRestore.txt. In this script, we load this flat file of servers and databases to check, and then the process proceeds in a very similar way to what I’ve outlined above.

The Orphan property tells us the relevant status of a user and LoginExists will let us decide which action needs to be taken; if LoginExists is false, we drop this user, and if it’s True we map them.

(This script as available for download at the top of this article.)

If you want to log all the completed transactions, you just need to add the Save-MSSQLMsg function after each action (drop or map), and then if some problem occurs when trying to Map or Drop, the error message is already logged and ready for inspection.

You can easily apply these functions to all databases in your environment, too. “Why would I want to do that?”, you ask. How about a migration of servers using backup and restore? Regardless of when and where you use these scripts, they can be scheduled in exactly the same way as I demonstrated earlier.

That’s everything for this first part of my script-sharing, and this is how I check and fix orphaned users. You can find a complete set of example scripts available for download at the top right of this article. Later on I will be showing you more daily checks, making you an Exceptional DBA, and using PowerShell.