locked
how to define a role with create and alter procedure rights but can't create and delete table? RRS feed

  • Question

  • hi, experts

    I try to create a role with create and alter procedure without create and delete table rights, but failed , how to do it ? Thanks.

    Regards

    Garey

    • Moved by Tom Phillips Monday, December 17, 2012 3:49 PM Security question (From:SQL Server Database Engine)
    Monday, December 17, 2012 3:29 AM

Answers

  • hi, experts

    I try to create a role with create and alter procedure without create and delete table rights, but failed , how to do it ? Thanks.

    Regards

    Garey

    Below is an example for a specific schema:

    USE MyDatabase;
    GO
    CREATE ROLE ProcCreatorRole;
    CREATE LOGIN MyLogin WITH PASSWORD='MyLoginP@ssW0Rd';
    CREATE USER MyLogin;
    EXEC sp_addrolemember N'ProcCreatorRole', N'MyLogin';
    GRANT CREATE PROCEDURE TO ProcCreatorRole;
    GO
    
    GRANT CONTROL ON SCHEMA::dbo TO ProcCreatorRole;
    GO
    GRANT CREATE PROCEDURE TO ProcCreatorRole;
    GO
    
    EXECUTE AS LOGIN = 'MyLogin';
    GO
    --this succeeds
    CREATE PROC dbo.usp_test AS SELECT 1 AS test;
    GO
    ALTER PROC dbo.usp_test AS SELECT 2 AS test;
    GO
    --this fails with CREATE TABLE permission denied in database 'Test'.
    CREATE TABLE dbo.test(col1 int);
    GO
    REVERT;
    GO


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/


    • Edited by Dan GuzmanMVP Monday, December 17, 2012 5:08 AM
    • Proposed as answer by Uri DimantMVP Monday, December 17, 2012 6:07 AM
    • Marked as answer by Old Garey Monday, December 17, 2012 9:22 AM
    Monday, December 17, 2012 5:07 AM