Welcome to Day 12 of my “A Month of PowerShell” series. This series will use the series landing page on this blog at http://blog.waynesheffield.com/wayne/a-month-of-powershell/. Please refer to this page to see all of the posts in this series, and to quickly go to them.

Creating Logins

One of the first steps that you’ll probably want to do after adding a database is to add users to this database. This requires that SQL Server logins be established (unless this is a contained database), so let’s work through this process. First, check to see if the login exists, and create it if necessary:

Creating Users

Next, get the database, check to see if this user exists for the database, and add it if necessary:

Creating Database Roles and assigning users or roles to that role

Let’s create a couple of database roles, add the user to one, and add that role to the other role:

Assigning Logins to Server Roles

Okay, we’ve got database roles… let’s add this user to a server role also… and since everybody always needs sa access, let’s make our login a sysadmin:

Checking Role Memberships

Hopefully you got that I’m just making fun of a common request… every login should not be a sysadmin. However, this does bring up an interesting question: if you’ve inherited a SQL Server, how do you determine who all is a sysadmin on it? Let’s find out:

And to check the members of the database roles created:

These methods can be called also at the container level, which will give you all of the members of all of the roles. However, it doesn’t tell you what roles they are: