Having created Windows Users or SQL Users using the last two days posts, today we shall add them to a role on a database.
As I discussed previously I believe that to follow good practice I try to ensure that database permissions are granted by role membership and each role is created with the minimum amount of permissions required for successful execution of the task involved.
So with each database having the correct roles created and the users created we just need to add the user to the database and to the role. This is easily done with PowerShell.
The Add-UserToRole function takes four parameters Server,Database,User and Role and does a series of error checks.
With these functions you can easily create a number of Users and add them to database roles quickly and easily and repeatedly.
If the test team come to you and require 10 Test Users and 3 Test Administrators adding to the test database. I create 2 notepad files
and use them with the Add-SQLAccountToSQLRole and Add-UserToRole functions to create the users
Here are the results in PowerShell
and in SSMS
The Code is here Add-UserToRole
Another post in the PowerShell Box of Tricks series.
In yesterdays post Creating a Windows User and Adding to SQL Role we created a Windows User, today it’s a SQL User. Again it is nice and simple and allows you to pipe input from other sources enabling you to easily and quickly repeat any process that needs SQL Users.
It is pretty similar as you would expect. We create a Login Object, set the Logintype to SqlLogin add the Password and create it with the Create Method. It is then added to the Role Specified
The same error checking is performed as the Windows Login function. 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
The function is called as follows.
Add-SQLAccountToSQLRole FADE2BLACK Test Password01 dbcreator
The code can be found here Add-SQLAccountToSQLRole