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

Get SQL Server 2014 with PowerShell v5 Cookbook now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.