仅创建过程权限

首先，我将使用角色而不是直接向用户授予访问权限。您可能已经在这样做了，但是我想我会提一下。

好的，这里的问题是将ALTER授予架构，这意味着被授予者可以ALTER访问架构中的所有对象类型。不幸的是，据我所知，没有办法授予特定对象类型的权限，因此它是全部或全部。相反，您不能将ALTER授予所有对象，然后拒绝ALTER特定对象类型。

我发现执行此 *** 作的唯一方法是将ALTER授予架构，然后使用DDL触发器来控制角色可以执行的 *** 作。

这是演示示例的示例的更新版本：

--** Create a Developer RoleCREATE ROLE [Developer] AUTHORIZATION db_securityadmin;GO--** Grant view and execute on all SPs to Devloper--GRANT VIEW DEFINITION ON SCHEMA::dbo TO [Developer];GRANT CREATE PROCEDURE TO [Developer];GRANT SELECT, INSERT, UPDATE, DELETE, ALTER, EXECUTE, VIEW DEFINITION ON SCHEMA::dbo TO [Developer]--** Create user and login for testdev and add to the Developer roleCREATE LOGIN testdev WITH PASSWORD = 'sldkjlkjlkj987kj' CREATE USER testdev EXEC sp_addrolemember @rolename = 'Developer', @membername = 'testdev';GO--** Create DDL trigger to deny drop and alter to the Developer roleCREATE TRIGGER tr_db_DenyDropAlterTable_Dev ON DATAbase FOR DROP_TABLE, ALTER_TABLE AS BEGIN    IF IS_MEMBER('Developer') = 1    BEGIN        PRINT 'You are not authorized to alter or drop a table.';        ROLLBACK TRAN;    END; END; GO--** TestingCREATE TABLE mysig (a int NOT NULL) ;EXECUTE AS USER = 'testdev'; GOCREATE PROCEDURE slaskis AS PRINT 12; GOCREATE TABLE hoppsan(a int NOT NULL); -- FAILS! GOINSERT mysig (a) VALUES(123); GOALTER TABLE mysig ADD test INT; --** This will fail tooGOREVERT; GODROP PROCEDURE slaskis ;DROP TABLE mysig ;DROP USER testdev;DROP LOGIN testdev;DROP ROLE [Developer];DROP TRIGGER tr_db_DenyDropAlterTable_Dev on DATAbase;