Using PowerShell to Add a Group of Users to a SQL Database

ScriptingGuy1

  Summary: Learn how to use Windows PowerShell to add a group of users to a SQL Server database.   Today is the final day of SQL guest blogger week. It was great to enter the SQL side of life. Sean McCown is our guest blogger for today. Sean McCown holds a double MCITP in SQL Server 2008 for both Administration and Development. He is also a SQL Server MVP with over 15 years of experience in databases. Sean is a Contributing Editor with InfoWorld Magazine and a frequent contributor to many community sites and forums. Sean is founder and co-owner of the renowned website MidnightDBA.com where he records free SQL Server training videos and co-hosts the popular webshow, DBAs@Midnight. Sean has created content for TrainingSpot.com, TrainSignal and moderates the Petri IT Knowledgebase SQL forums. Sean also speaks at various SQLSaturday events and is a board member of his local user group, NTSSUG. Without further delay, I will turn the keyboard over to Sean.   In my last post I talked about how to add a single user to a group of SQL databases (DB’s). However, this time I am going to talk about how to add a group of users to a single database (as promised). Now, at first this does not sound like a big deal. In addition, depending on your situation, this may not even be relevant for you, but you never know what you will need in the future. Moreover, there are situations where this kind of thing is very useful and I will discuss those at the end. Before getting into code, I want to say that there are several ways to tackle a problem such as this, and the method that you select is really dictated by the situation you find yourself in. We will use the scenario that you were given a spreadsheet with usernames and groups. This is not an unrealistic scenario at all if you look at the initial rollout of an application with the loading of all the users. Here is an example of what the Excel Worksheet could resemble:  

  First things first, we have a list of users and groups. But we have to create the logins on the server before we can add them to the application database. Again, we are assuming that this is a new application so nothing at all is setup yet.  We will load the Excel Worksheet into a database table first.  I know it sounds like an extra, unnecessary step. However, it is easier to work with database tables in Windows PowerShell than it is to work with Excel Worksheets so this may be an additional step, but to me it is one that is not only very fast and easy, but it will save me some coding and troubleshooting downstream. I am not going to dictate how you move the data into the table, but I would use SSIS if I were you. Now that the data is in a table, we can work with it very easily. We will first query the table and load the result set into a variable. Here is how we will pull the data into a variable in Windows PowerShell: 

$a = (Invoke-sqlcmd -ServerInstance localhost -Database “UserLoadDB” -Query “select * from dbo.UserTable”)   To this point, it looks surprisingly like the query we ran in the last post doesn’t it? Do you see why I wanted to put the Excel data into a table now? Because we already know how to work with data such as this, so there is nothing new to learn. As I said before, there are several ways we could do this. I am going to build the lines of code and save them to a file and then execute that file as a complete script. The reason is that if I have to go back and see what I did, or if I have to produce it on another box, it is much easier to do it if I already have the script. In addition, someone may have to add the script to a ticketing system as part of a record for either a change control or a compliance audit. Therefore, I think it is just best to have it in a file first. Here is the code that will make that possible:

$a | % {

$UserName = $_.UserName;

$Group = $_.Group;

“CREATE LOGIN [$UserName] FROM WINDOWS” | out-file c:Logins.sql -append;

“GO” | out-file c:Logins.sql -append;

“EXEC sp_grantdbaccess ‘$UserName’, ‘$UserName’ ” | out-file c:DBAccess.sql -append;

“GO” | out-file c:DBAccess.sql -append;

“EXEC sp_addrolemember ‘$Group’, ‘$UserName'” | out-file c:AddDBRole.sql -append;

“GO” | out-file c:AddDBRole.sql -append;

}   Notice that I put each set of SQL scripts into its own file. I just did this to show you how it could go. You can put all the SQL commands in a single file if you want. But it can be useful to have them separated.  You may have to create the logins on a different server, but not put them in a particular database; or if the logins already exist and you just have to add the users to a database. Either way it can be useful to have them separated, but do it the way that you want.  Now let me explain just a bit of what I have done here. First I put the list of users into a variable ($a) from the table I populated from the Excel Worksheet. Then I looped through each value and for each one, I assigned each column to a variable ($UserAccess, and $Group). You do not have to assign these to variables, but I think it makes the script more readable and if you have to change something later, just change how the variable gets its value and nothing downstream has to change. If you do not want to assign the variables however, instead of this:

 “CREATE LOGIN [$UserName] FROM WINDOWS”   You would have something such as this:

 “CREATE LOGIN [$_.UserName] FROM WINDOWS”   Next, I built three SQL statements and saved them to a file for each row in $a. I created a login, granted database access, and added the user to a role. All I have to do now is run the scripts against the database. The commands to run the three SQL scripts are shown here.

Invoke-sqlcmd -ServerInstance localhost -Database “MyDB” -InputFile “C:Logins.sql”; 

Invoke-sqlcmd -ServerInstance localhost -Database “MyDB” -InputFile “C:DBAccess.sql”; 

Invoke-sqlcmd -ServerInstance localhost -Database “MyDB” -InputFile “C:AddDBRole.sql”;    Again, notice how we are building on skills we learned in the last post. This is the point. Although learning new stuff is cool, you do not want to have to learn something new every time that you sit down to do a task. I want to talk for a minute about using files instead of just running the scripts against the server, which we could have easily done. Putting the code into files first is really a good idea for several reasons. I already mentioned a couple, but even getting away from compliance or running them on different boxes, you have more control. Here is a list of things that you could do with the text file method (not exhaustive):

1. Add USE Database statements.
2. Add IF EXISTS logic.
3. Create header comments to document the script.
4. Pass the script on to another team to do something with.
5. Troubleshoot an error because you have the script to run again.
6. Use the script as a template so that you can do additional manipulation if you have to.

  I really like numbers 4 and 5.  I am a big fan of code that writes code for both of those reasons. Very frequently I’ll write a Windows PowerShell script that does nothing but produce a .sql file for me so I can then run it myself so in case anything happens I’m looking at it now and can easily jump to the trouble spot. Sometimes working with Windows PowerShell in SQL Server is not about having it do everything for you. Sometimes it is about using it to get you most of the way there and then finishing up manually. It is nothing to create a user on a server and add him to a database.  We do that all the time. However, adding 200 users can be a bit of a chore. Therefore, you can use Windows PowerShell to create the scripts for you and then run them yourself. It is not Las Vegas; what is in Windows PowerShell does not have to stay in Windows PowerShell. Here is a quick note on situations where this skill can be useful. I have already covered the easy one where you are setting up a new application and loading all the users at the same time. That is definitely a good one, but it is not very common. What is more common however is that you have to create a disaster recovery (DR) plan for all the boxes in your environment. Very frequently, when you create DR plans one of the requirements is to be able to build a server from scratch and that includes permissions. So being able to run a script against a server and have it script your logins and database permissions is very handy. You can even schedule it as a job and have it run every day/week/month to keep your DR scripts current. In addition, whereas the specifics of this script will change somewhat, the theory stays the same. This kind of script can also be useful when you are building a development (dev) or a quality assurance (QA) server and you have to have things to mirror the production environment. With some minor modifications, you could have this script point at the production server, read the login information out of the system table, build the script and run it. Moreover, this is the kind of thing that you can keep generic enough that you can do it from any source server to any target server and you never have to worry about whether it is right or not-after all, it is always the same script.   Well, this concludes SQL guest blogger week. Thanks Sean for today’s post. Join me tomorrow for the Weekend Scripter as I delve into the mysteries of a Windows PowerShell ISE module. I would love you to follow me on Twitter or Facebook. If you have any questions, send email to me at scripter@microsoft.com or post them on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.  

Ed Wilson, Microsoft Scripting Guy

ScriptingGuy1

Follow