Creating custom database-roles in SQL Server by schema

Question

We separate features in our database using schemas. For example, all Create & Maintain Value (CMV) objects live within the CMV schema. Of course, each schema uses objects like stored procedures, views, functions etc.

I want to create a set of database roles by schema to use for security purposes. For example:

db_datareader 
db_datawriter 
cmv_executor //for stored procs and functions
cmv_viewer   //for views

So my question is:

How do I GRANT EXECUTE (or whatever) for various views, functions and stored procedures (etc.) by schema and bundle them up into a single role without running each GRANT by hand?

Bryan · Accepted Answer · 2012-03-15 15:42:12Z

1

Schema permissions

-- execute permission on programmability objects in CMV schema
GRANT EXECUTE ON SCHEMA::CMV TO cmv_executor
-- DML permissions on tables/views in CMV schema
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::CMV TO cmv_viewer

This should eliminate the need for adding users to db_datareader and db_datawriter roles unless users need read/write for objects in schemas other than CMV.

See here for more information on granting schema permissions.

Object permissions

-- use output of this query to grant permissions for individual objects
SELECT
    'GRANT SELECT ON ' +
    QUOTENAME(SCHEMA_NAME(schema_id)) + '.' +
    QUOTENAME(name) + ' TO [cmv_viewer];'
FROM
    sys.objects
WHERE
    SCHEMA_NAME(schema_id) = 'CMV'
        AND
    type_desc = 'VIEW'
UNION
SELECT
    'GRANT EXECUTE ON ' +
    QUOTENAME(SCHEMA_NAME(schema_id)) + '.' +
    QUOTENAME(name) + ' TO [cmv_executor];'
FROM
    sys.objects
WHERE
    SCHEMA_NAME(schema_id) = 'CMV'
        AND
    type_desc IN ('SQL_STORED_PROCEDURE', 'SQL_SCALAR_FUNCTION',
                  'SQL_INLINE_TABLE_VALUED_FUNCTION');

I didn't include CLR functions or procedures, so add those if needed. Valid values for the type_desc column found here.

edited Mar 15, 2012 at 15:42

answered Mar 15, 2012 at 14:13

Bryan

17.7k7 gold badges59 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Prisoner ZERO Over a year ago

Does EXECUTE mean for procedures AND for functions? How does the other one grant permissions against the VIEWS?

Aaron Bertrand Over a year ago

Wouldn't views fall under GRANT SELECT?

Bryan Over a year ago

@PrisonerZERO Take a look at this page for information on permissions and how they apply to the various object types. msdn.microsoft.com/en-us/library/ms191291.aspx

Prisoner ZERO Over a year ago

We need to restrict access to the tables and go through views & functiosn ONLY!

Bryan Over a year ago

@PrisonerZERO If you want to restrict access to views and functions only, you can only grant permissions by schema if you put the views and functions in a separate schema from the base CMV. If the views/functions stay in CMV, you need a separate GRANT for each object.

|

Pondlife · Accepted Answer · 2012-03-15 15:25:15Z

There are several options. If permissions are the same for all objects in the schema the you can do this:

GRANT SELECT ON SCHEMA::CMV TO cmv_reader

If permissions are per-object, or your requirements are more complex, you can either maintain a permissions script in source control and add new objects to it manually, or generate the script from metadata and then execute it, e.g.:

select 
    'GRANT SELECT ON cmv.' + object_name(object_id) + ' TO cmv_reader;'
from 
    sys.tables 
where 
    schema_id = schema_id('cmv') and 
    name like 'A%' -- or whatever

And depending on what toolset you're using for development, there may be other options. It really comes down to how complex your requirements are, but generating your permissions scripts from metadata is a common, flexible solution.

Collectives™ on Stack Overflow

Creating custom database-roles in SQL Server by schema

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related