CODEWITHSUNDEEP
sqlsql-serverroles
Prisoner ZERO
Prisoner ZERO

Reputation: 14166

Creating custom database-roles in SQL Server by schema

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?

Upvotes: 0

Views: 4743

Answers (2)

Bryan
Bryan

Reputation: 17693

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.

Upvotes: 1

Pondlife
Pondlife

Reputation: 16240

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.

Upvotes: 0

Related Questions