Welcome to Day 12 of my “A Month of PowerShell” series. This series will use the series landing page on this blog at //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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
#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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
#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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
$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:
|
1 2 3 |
#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:
|
1 2 3 4 5 6 |
#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:
|
1 2 3 |
#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:
|
1 2 3 |
#Check members of all roles: $Server.Roles.EnumServerRoleMembers() $DB.Roles.EnumMembers() |
