SQL Server 2014 with PowerShell v5 Cookbook

Creating a user-defined server role

This recipe walks you through how to create a user-defined server role in SQL Server.

Getting ready

In this recipe, we will create a user-defined server role called impersonator and add QUERYWORKS\tstark as a member. We are also going to assign this new role permissions to impersonate any login and grant permissions to any unsafe assembly.

The T-SQL equivalent of what we are going to accomplish in this recipe is as follows:

-- create custom server role CREATE SERVER ROLE [impersonator] AUTHORIZATION [QUERYWORKS\Administrator] GO -- add member ALTER SERVER ROLE [impersonator] ADD MEMBER [QUERYWORKS\tstark] GO -- add permissions for custom role GRANT IMPERSONATE ANY LOGIN TO [impersonator] GO GRANT UNSAFE ASSEMBLY ...