Assigning permissions and roles to a login
This recipe shows you how to assign permissions and roles to a login by using PowerShell and SMO.
Getting ready
If you haven't already
done so in the Creating a login recipe, create a SQL login name eric
. We will be assigning the dbcreator
and setupadmin
server role to this login, as well as granting ALTER
permissions to any setting or database. The T-SQL equivalent of what we are trying to accomplish is:
ALTER SERVER ROLE [dbcreator] ADD MEMBER [eric] GO ALTER SERVER ROLE [setupadmin] ADD MEMBER [eric] GO GRANT ALTER ANY DATABASE, ALTER SETTINGS TO [eric]
How to do it...
Open the PowerShell console by going to Start | Accessories | Windows PowerShell | Windows PowerShell ISE.
Import the
SQLPS
module and create a new SMO Server object as follows:#import SQL Server module Import-Module SQLPS -DisableNameChecking #replace this with your instance name $instanceName = "KERRIGAN" $server = New-Object -TypeName Microsoft.SqlServer.Management...