3

How can I grant only CREATE/ALTER permission for Procedures/Functions only, to specific users of database (Developers). I don't want to allow them to Create/Alter/Drop tables or any other functionality. Is this possible ?

Doonie Darkoo
  • 1,475
  • 2
  • 14
  • 33
  • https://learn.microsoft.com/en-us/sql/t-sql/statements/grant-database-permissions-transact-sql?view=sql-server-2017 – Ivan Starostin Aug 27 '18 at 10:43
  • Possible duplicate of [SQL Server 2005 - Granting permission to create stored procedures (but no other objects)](https://stackoverflow.com/questions/226811/sql-server-2005-granting-permission-to-create-stored-procedures-but-no-other) – WynDiesel Aug 27 '18 at 10:44

2 Answers2

0

you try below way

Create a Role

    CREATE ROLE [user_dev] AUTHORIZATION db_securityadmin;
    GO



    GRANT CREATE PROCEDURE TO [user_dev];
    GRANT SELECT, INSERT, UPDATE, DELETE, ALTER, EXECUTE, VIEW DEFINITION ON SCHEMA::dbo TO [user_dev]

Create user and login for test and add to the user_dev role

    CREATE LOGIN test WITH PASSWORD = 'dfgdfg' 
    CREATE USER test 
    EXEC sp_addrolemember @rolename = 'user_dev', @membername = 'test';
    GO

Then create a trigger to restrict user_dev role from creating table and drop

    CREATE TRIGGER tr_db_DenyDropAlterTable
    ON DATABASE 
    FOR DROP_TABLE, ALTER_TABLE 
    AS 
    BEGIN 
       IF IS_MEMBER('user_dev') = 1 
       BEGIN 
           PRINT 'Not allowed'; 
           ROLLBACK TRAN; 
       END; 
    END; 
    GO
Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63
  • 1
    It is _never_ safe to allow a non-admin user to create objects in a schema owned by another user. EG for a stored procedure the user could then create a stored procedure with EXECUTE AS OWNER and run code as schema owner. – David Browne - Microsoft Aug 27 '18 at 11:53
-1

try this solution open the database security from msql server management studio enter image description here

then

enter image description here

Youssri Abo Elseod
  • 671
  • 1
  • 9
  • 23