DBA > Articles

Exceptional PowerShell DBA Pt1 - Orphaned Users

By: Laerte Junior
To read more DBA articles, visit http://dba.fyicenter.com/article/

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 (!(Get-PSSnapin -registered | ?{$_.name -eq 'SqlServerProviderSnapin100'}))
{
add-pssnapin SqlServerProviderSnapin100
add-pssnapin SqlServerCmdletSnapin100
}


Import-Module -Name'C:\PS\PSScripts\Functions.psm1' -WarningAction SilentlyContinue -ErrorAction Stop

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

Get-PSSnapin Registered

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

Full article...


Other Related Articles

... to read more DBA articles, visit http://dba.fyicenter.com/article/