Sql-server – How to create a role to create,update,delete stored procedures and views

permissionsrolesql serversql-server-2012

what permissions do I need to give in order to achieve the above task to get users the capacity to create,update,delete stored procedures and views to a database role.

Best Answer

The easiest way is to add a user to the db_ddladmin role.

-- Older method
EXEC sp_addrolemember 'db_ddladmin','username';
-- New method
ALTER ROLE db_ddladmin ADD MEMBER username;

This will give the user the ability to create/modify/drop any object in any schema. Unfortunately that will include objects you probably don't want them to change (tables for example).

A more precise method is to use the CREATE permissions. There is a small trick to using them however. You will also need to grant ALTER on the schema where they can create/alter the objects. So to grant the ability to create procedures and views in the dbo schema you might do this.

GRANT ALTER ON SCHEMA::[dbo] TO [UserName];
GRANT CREATE PROCEDURE TO [UserName];
GRANT CREATE VIEW TO [UserName];

Same with CREATE TABLE, CREATE FUNCTION etc. The reason you need both is this: CREATE PROCEDURE gives you the ability to create the procedure, ALTER schema gives you a place to put the procedure as you create it.

I should also point out that the CREATE permissions also grant the ability to alter, and drop.

Related Solutions

Sql-server – Need help figuring out correct permissions for SQL Server Users

With (1) you should be good giving them db_datareader and db_datawriter.

On (2) I would probably create a role and then grant that role execute permissions on all the stored procedures, in place of granting it to each individual user. Then just add those users to the role. If you have all the stored procedures under a schema name you should be able to grant execute to that schema. That will save time and not have to worry about new procedures being added to the database.

Sql-server – Granting Execute Permissions on Stored Procedures, Functions and Views

It is true that you cannot grant EXEC permissions on a function that returns a table. This type of function is effectively more of a view than a function. You need to grant SELECT instead, e.g.:

GRANT SELECT ON dbo.Table_Valued_Function TO [testuser];

So your script would look more like this (sorry, but I absolutely loathe INFORMATION_SCHEMA and much prefer to use the catalog views, which also don't need functions like OBJECTPROPERTY):

DECLARE 
    @sql      NVARCHAR(MAX) = N'',
    @username VARCHAR(255)  = 'testuser';

SELECT @sql += CHAR(13) + CHAR(10) + N'GRANT ' + CASE 
    WHEN type_desc LIKE 'SQL_%TABLE_VALUED_FUNCTION'
      OR type_desc = 'VIEW'
    THEN ' SELECT ' ELSE ' EXEC ' END 
    + ' ON ' + QUOTENAME(SCHEMA_NAME([schema_id])) 
    + '.' + QUOTENAME(name) 
    + ' TO ' + @username + ';'
 FROM sys.all_objects
WHERE is_ms_shipped = 0 AND
( 
    type_desc LIKE '%PROCEDURE' 
    OR type_desc LIKE '%FUNCTION'
    OR type_desc = 'VIEW'
);

PRINT @sql;
-- EXEC sp_executesql @sql;

Now you can grant EXEC on a schema, and always create these procedures in that schema (actually one of the purposes of schemas!), which @jgardner04 already suggested, however in order for this solution to apply to table-valued functions as well, you'd also have to grant SELECT. Which is okay if you're not storing any data in tables in that schema (or at least that you want to hide from them), but it will apply to any tables and views as well, which might not be your intention.

Another idea (e.g. if you can't, or don't want to, use schemas) is to write a DDL Trigger that captures the CREATE_PROCEDURE, CREATE_FUNCTION and CREATE_VIEW events, and grants permissions to a user (or a set of users, if you want to store them in a table):

CREATE TRIGGER ApplyPermissionsToAllProceduressAndFunctions -- be more creative!
    ON DATABASE FOR CREATE_PROCEDURE, CREATE_FUNCTION, CREATE_VIEW
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE
        @sql       NVARCHAR(MAX),
        @EventData XML = EVENTDATA();

    ;WITH x ( sch, obj ) 
    AS
    (
        SELECT
          @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'), 
          @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)')
    )
    SELECT @sql = N'GRANT ' + CASE 
      WHEN o.type_desc LIKE 'SQL_%TABLE_VALUED_FUNCTION' 
        OR o.type_desc = 'VIEW'
      THEN ' SELECT ' 
      ELSE ' EXEC ' END 
        + ' ON ' + QUOTENAME(x.sch) 
        + '.' + QUOTENAME(x.obj) 
        + ' TO testuser;' -- hard-code this, use a variable, or store in a table
    FROM x
    INNER JOIN sys.objects AS o
    ON o.[object_id] = OBJECT_ID(QUOTENAME(x.sch) + '.' + QUOTENAME(x.obj));

    EXEC sp_executesql @sql;
END
GO

The drawback I find with DDL Triggers is that you can quickly forget that they're there. So a year down the road when you decide to stop granting these permissions to all new objects, it might take a while to troubleshoot why it's still happening. At my last job we logged all actions invoked by DDL triggers to a central "event log" of sorts, and that was our go-to place for tracking down any actions that happened on the server that nobody seems to remember (and it was a DDL trigger about half the time). So you may consider adding some additional logic that will help with that.

EDIT

Adding code for schema-based, and I'll mention again that this will grant permissions on any procedures, functions and tables created in the foo schema.

CREATE SCHEMA foo;
GRANT EXEC, SELECT ON SCHEMA::foo TO testuser;

Now if you create the following procedure, testuser will be able to execute:

CREATE PROCEDURE foo.proc1
AS 
BEGIN
    SET NOCOUNT ON;
    SELECT 1;
END
GO

Best Answer

Related Solutions

Sql-server – Need help figuring out correct permissions for SQL Server Users

Sql-server – Granting Execute Permissions on Stored Procedures, Functions and Views

Related Question