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. Alternatively, choose another login in your system that you want to use for this recipe. We will be assigning the dbcreator and setupadmin server roles to this login, as well as granting ALTER permissions to any setting or database. Here's the T-SQL equivalent of what we are trying to accomplish:

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...

Let's list the steps required to complete the task:

1. Open PowerShell ISE as an administrator.

2. 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 = "localhost...