{"id":738,"date":"2009-11-26T00:00:00","date_gmt":"2009-11-26T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/exceptional-powershell-dba-pt1-orphaned-users\/"},"modified":"2021-08-24T13:40:34","modified_gmt":"2021-08-24T13:40:34","slug":"exceptional-powershell-dba-pt1-orphaned-users","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/exceptional-powershell-dba-pt1-orphaned-users\/","title":{"rendered":"Exceptional PowerShell DBA Pt1 – Orphaned Users"},"content":{"rendered":"
\n

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<\/em> 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.<\/p>\n

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<\/em>: Big or Small.<\/p>\n

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<\/em><\/strong>.<\/p>\n

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<\/em> situations in the future articles in this little series<\/p>\n

A Little Background<\/h2>\n

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.<\/p>\n

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.<\/p>\n

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<\/em>!” you happily respond, “OK, I’ll do it today<\/em>“. 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”<\/em> After all, you are an Exceptional DBA and you use PowerShell.<\/p>\n

Check\/Fix the Orphan Users<\/h1>\n

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:<\/p>\n

Save-MSSQLMsg<\/strong> : This function generates a log file for scripts (Success, error , or simply an information message)<\/p>\n

Map-MSSQLUser<\/strong> This function maps the user with their Login – It only maps Users and Logins with the same name.<\/p>\n

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

Get-MSSQLUser<\/strong> This function Returns all an object with information about users :
\n LineNumber, Date, ServerName, DatabaseName, UserName, CreateDate,
\n DateLastModified, AsymMetricKey, DefaultSchema, HasDBAccess,ID, LoginType, Login,
\n Orphan, LoginExists
\n <\/code> 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.<\/p>\n

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:<\/p>\n