http://www.sqlservercentral.com/blogs/discussionofsqlserver/2013/02/12/a-month-of-powershell-day-12-logins-users-and-roles/

Printed 2014/09/20 08:21PM

A Month of PowerShell – Day 12 (Logins, Users and Roles)

By Wayne Sheffield, 2013/02/12

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:

Source code   
#Assign variables
$Instance   = "localhost\SQL2012"
$LoginName  = "MyNewLogin"
$Password   = "weakpassword"
$DBName     = "PoSh"
 
#Get the server object
$Server = New-Object ("Microsoft.SqlServer.Management.SMO.Server") $instance
 
#Get the login object if it exists
$Login = $Server.Logins.Item($LoginName)
 
IF (!($Login))  #check to see if login already exists
{
    #it doesn't, so instantiate a new login object
    $Login = New-Object ("Microsoft.SqlServer.Management.SMO.Login") ($Server, $LoginName)
 
    #make it a SQL Login
    $Login.LoginType = [Microsoft.SqlServer.Management.Smo.LoginType]::SqlLogin
 
    #Create it on the server with the specified password
    $Login.Create($Password)
}

Creating Users

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

Source code   
#Get the database object
$DB = $Server.Databases[$DBName]
 
#Get the user object if it exists
$User = $DB.Users[$LoginName]
 
if (!($User)) # check to see if the user is already in the database
{
    #it doesn't, so add it
    $User = New-Object ("Microsoft.SqlServer.Management.SMO.User") ($DB, $LoginName)
    $User.Login = $LoginName
    $User.Create()
}

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:

Source code   
$RoleName   = "NewRole"
 
#Get the role object if it exists
$Role1 = $DB.Roles[$RoleName]
 
if (!($Role1)) #Check to see if the role already exists in the database
{
    #it doesn't, so add it
    $Role1 = New-Object ("Microsoft.SqlServer.Management.SMO.DatabaseRole") ($DB, $RoleName)
    $Role1.Create()
}
 
#Get another role
$RoleName = $RoleName + "2"
#Get the role object if it exists
$Role2 = $DB.Roles[$RoleName]
 
if (!($Role2)) #Check to see if the role already exists in the database
{
    #it doesn't, so add it
    $Role2 = New-Object ("Microsoft.SqlServer.Management.SMO.DatabaseRole") ($DB, $RoleName)
    $Role2.Create()
}
 
# Add the user to role 2
$Role2.AddMember($User.Name)
 
#Add role 2 to role 1
$Role1.AddMember($Role2.Name)

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:

Source code   
#Add this login to the sysadmin role also... just 'cause everyone always needs it
$Role = $Server.Roles["sysadmin"]
$Role.AddMember($LoginName)

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:

Source code   
#Get the members of the sysadmin role
#Note that the ServerRole class documentation at
 
http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.serverrole.aspx
 
# says that EnumServerRoleMembers is obsolete and to use EnumMemberNames.
# However, I get a “method doesn't exist” error when trying to use it.
$Server.Roles["sysadmin"].EnumServerRoleMembers()

And to check the members of the database roles created:

Source code   
#Check membership of the roles created
$DB.Roles["NewRole"].EnumMembers()
$DB.Roles["NewRole2"].EnumMembers()

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:

Source code   
#Check members of all roles:
$Server.Roles.EnumServerRoleMembers()
$DB.Roles.EnumMembers()

Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.