Book Image

SQL Server 2014 with PowerShell v5 Cookbook

By : Donabel Santos
Book Image

SQL Server 2014 with PowerShell v5 Cookbook

By: Donabel Santos

Overview of this book

Table of Contents (21 chapters)
SQL Server 2014 with PowerShell v5 Cookbook
Credits
About the Author
Acknowledgments
About the Reviewers
www.PacktPub.com
Preface
Index

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