Add User Role

Roles can be created inside a database that encapsulate permissions. These permissions can be granular (as in denying read permissions to one table), or they can be global (as in being able to write to all tables).

Using the CREATE ROLE state we will walk through some typical examples.

First of all, we need to create an empty shell of a role that will not have any securables
[cc lang=”sql”]
CREATE ROLE [ExecDBO] AUTHORIZATION [dbo]
[/cc]
The code above did not define any permissions, it only created an empty role. The authorization keyword defines the dbo user as being the “owner” of this role.

Now we will define some securables for this role
[cc lang=”sql”]
GRANT EXECUTE ON SCHEMA::dbo TO [ExecDBO];
GRANT SELECT ON SCHEMA::dbo TO [ExecDBO];
[/cc]
Above we have just granted read and execute on the entire ‘dbo’ schema.