New User-defined Server Roles.

Applies to: Microsoft SQL Server 2012.

SQL Server 2012 now allows you to create a user-defined server role and configure server –level permissions for it.

On previous versions of SQL Server, if you wanted to delegate a junior DBA permission to perform specific administrative tasks you probably ended up granting that junior DBA sysadmin access to the instance, providing that person more permissions than needed and creating potential risks. Starting SQL Server 2012, user-defined server roles can be created and configured with specific permissions as required by the business needs.

However, one limitation of the user-defined server roles is that they cannot be granted permission on database level securables.

The following script demonstrates how to create a server role named ServerAudit_Admins, configures the role with permission to modify server audits only. Members of this role will be able to modify existent server audits, but won’t be able to create nor drop a server audit.

 

CREATE SERVER ROLE [ServerAudit_Admins] AUTHORIZATION [sa];

GRANT ALTER  ANY SERVER AUDIT TO [ServerAudit_Admins];

ALTER SERVER ROLE [ServerAudit_Admins] ADD MEMBER [jperez];

References.

Create role (Transact-Role).

.Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.