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 the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.