SQL Server 2014 with PowerShell v5 Cookbook

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