The User Defined Server Roles in SQL 2012

The new user defined server role is a secure and new feature added in SQL Server 2012. In versions before SQL Server 2012, we only had fixed server roles in SQL Server.

Some stored procedures used in fixed server role are not valid anymore and will be removed and replaced to support this new feature in the future.

Sample code:

USE [master]
GO
CREATE SERVER ROLE [customServerRole] AUTHORIZATION [sa]

ALTER SERVER ROLE [customServerRole] ADD MEMBER [<user name>]


ALTER SERVER ROLE [customServerRole] DROP MEMBER [<user name>]


ALTER SERVER ROLE [setupadmin] ADD MEMBER [customServerRole]