CREATE ROLE — Defines a role and the permissions associated with that role.
CREATE ROLE role-name [WITH permission [,...]]
The CREATE ROLE statement defines a named role that can be used to assign access rights to specific procedures and functions. When security is enabled in the deployment file, the permissions assigned in the CREATE ROLE and CREATE PROCEDURE statements specify which users can access which functions.
Use the CREATE PROCEDURE statement to assign permissions to named roles for accessing specific stored procedures. The CREATE ROLE statement lets you assign certain generic permissions. The permissions that can be assigned by the WITH clause are:
Allows access to ad hoc queries (through the @AdHoc system procedure and sqlcmd command)
Allows access to the default procedures for all tables
Allows access to all system procedures
The generic permissions are denied by default. So you must explicitly enable them for those roles that need them. For example, if users assigned to the "interactive" role need to run ad hoc queries, you must explicitly assign that permission in the CREATE ROLE statement:
CREATE ROLE interactive WITH adhoc;
Also note that the permissions are additive. So if a user is assigned to one role that allows access to adhoc but not sysproc, but that user also is assigned to another role that allows sysproc, the user has both permissions.