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 TO [impersonator]
GO

If you are not using the QueryWorks VM that was created in Appendix B, Creating a SQL Server VM, you must change the names, members, and permissions in this recipe based on what's available in your development environment.

How...