Creating a Windows User and adding to a SQL Server Role with PowerShell

Another post in the PowerShell Box of Tricks series.

In a previous post Checking SQL Server User Role Membership we showed how to check which roles users were added to. This function allows you to add Windows Users to Server Roles. A nice simple function which can easily be piped into to allow users to be added form a list in a text file, csv file or even from Active Directory. This makes it easy to recreate Dev and Test environments and can be added to Disaster Recovery processes.

We create a Login Object, set the Logintype and create it with the Create Method. It is then added to the Role specified.

image

The function does some simple error checking. If the login already exists on the server it will just add it to the role and if the role has been mistyped it will let you know. It does this by checking if the Role object is Null for the Roles and the Contains Method for the Logins

image

The function is called as follows. To just create a login I add the user to the public role

Add-WindowsAccountToSQLRole FADE2BLACK ‘FADE2BLACK\Test’ public

The code can be found here

 

Tomorrow we will create and add a SQL Authenticated User

Please feel free to comment on this post. All comments are moderated first before appearing on the site