SQL 仅创建过程权限
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3131160/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create Procedure Permission ONLY
提问by mengchew0113
I have a requirement in SQL Server 2008 in development database
我在开发数据库中对 SQL Server 2008 有要求
- Only DBA's ( who are database owners ) can create, alter tables . Developer's should not create or alter tables .
- Developers can create/alter Stored Procedure/User Defined functions in dbo schema and can execute SP/UDF.
- Developers should have SELECT,INSERT,DELETE,UPDATE on tables ( tables in dbo schema
- 只有 DBA（他们是数据库所有者）才能创建、更改表。开发人员不应创建或更改表。
- 开发人员可以在 dbo 模式中创建/更改存储过程/用户定义函数，并且可以执行 SP/UDF。
- 开发人员应该对表（dbo 模式中的表）进行 SELECT、INSERT、DELETE、UPDATE
How to achieve this using GRANT statement
如何使用 GRANT 语句实现此目的
Found a sample solution from Google, but still have issue
从 Google 找到了一个示例解决方案，但仍有问题
CREATE LOGIN testdev WITH PASSWORD = 'sldkjlkjlkj 987kj//'
CREATE USER testdev
GRANT ALTER ON SCHEMA::dbo TO testdev
GRANT CREATE PROCEDURE TO testdev
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO testdev
CREATE TABLE mysig (a int NOT NULL)
EXECUTE AS USER = 'testdev'
go
CREATE PROCEDURE slaskis AS PRINT 12
go
CREATE TABLE hoppsan(a int NOT NULL) -- FAILS!
go
INSERT mysig (a) VALUES(123)
go
REVERT
go
DROP PROCEDURE slaskis
DROP TABLE mysig
DROP USER testdev
DROP LOGIN testdev
The syntax above able to block developer to create table but cant block developer to use SSMS design and alter the table.
上面的语法可以阻止开发人员创建表，但不能阻止开发人员使用 SSMS 设计和更改表。
Thanks.
谢谢。
回答by JonPayne
First of all I would use Roles instead of granting access directly to users. You may be already doing this, but I thought I would mention it.
首先，我会使用角色而不是直接向用户授予访问权限。你可能已经这样做了，但我想我会提到它。
Okay, the problem here is granting ALTER to the Schema means that the grantee has ALTER access to all object types in the schema. Unfortunately, as far as I know, there is no way to grant permissions to specific object types so it is all or nothing. Conversely, you cannot grant ALTER to all objects and then deny ALTER to specific object types.
好的，这里的问题是将 ALTER 授予架构意味着被授予者对架构中的所有对象类型具有 ALTER 访问权限。不幸的是，据我所知，没有办法授予特定对象类型的权限，所以要么全有要么全无。相反，您不能将 ALTER 授予所有对象，然后拒绝特定对象类型的 ALTER。
The only way I have found to do this is to grant ALTER to the schema and then used a DDL Trigger to control what the role can do.
我发现这样做的唯一方法是将 ALTER 授予架构，然后使用 DDL 触发器来控制角色可以做什么。
Here is an updated version of your example demonstrating the principle:
这是您的示例的更新版本，演示了该原理：
--** Create a Developer Role
CREATE 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 role
CREATE 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 role
CREATE 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
--** Testing
CREATE TABLE mysig (a int NOT NULL) ;
EXECUTE AS USER = 'testdev';
GO
CREATE PROCEDURE slaskis AS PRINT 12;
GO
CREATE TABLE hoppsan(a int NOT NULL); -- FAILS!
GO
INSERT mysig (a) VALUES(123);
GO
ALTER TABLE mysig ADD test INT; --** This will fail too
GO
REVERT;
GO
DROP PROCEDURE slaskis ;
DROP TABLE mysig ;
DROP USER testdev;
DROP LOGIN testdev;
DROP ROLE [Developer];
DROP TRIGGER tr_db_DenyDropAlterTable_Dev on DATABASE;
回答by Ismayil S
The following Code will Create the user and role in sql server 2012 :
以下代码将在 sql server 2012 中创建用户和角色：
USE PermissionDB;
---Select Your database
CREATE ROLE Employee;
---Create role
CREATE USER Employee1 Without Login;
---Create User
----Execute the Above query
EXEC sp_addrolemember @rolename = 'Employee', @membername = 'Employee1';
Hope This would be helpful....
希望这会有所帮助....
