请教一个sql server 用户读写权限控制的问题

[davy150484671]

作为一个DBA的角色， 我想控制一个用户对数据库的表只有读取功能。。。并且实现通过SP来做insert&update&delete 操作。。。请问怎样让用户调用SP时，可以对某个表有insert&update&delete操作的权限呢。。。用 with execute as 可以实现？ 具体怎样写呢？

[發糞塗牆]

设定用户在数据库级别只有DB_Datareader权限，然后授予exec的权限，试试这样够了没？

[Neo_whl]

sp_addrolemember 'db_datareader','user_name' grant insert,update,delete on table_name to user_name

[唐诗三百首]

-- 1.授予只读权限
use [数据库名]
go
exec sp_addrolemember N'db_datareader', N'[用户名]'

-- 2.创建存储过程
create proc [存储过程名]
with execute as 'dbo'
as
begin
 [存储过程语句]
end

-- 3.授权执行存储过程
grant exec on [存储过程名] to [用户名]

[Andy__Huang]

新建帐户appuser，密码为163.com，并允许该用户对appdb数据库的test表有select,insert,update权限，那么依次执行 
exec sp_addlogin 'appuser','163.com','TEST' 
go 

use TEST 
exec sp_grantdbaccess 'appuser' 
go 

use TEST 
grant select,insert,update 
on test 
to appuser 
go

[Q315054403]

Grant Select,Execute To User1

[davy150484671]

引用 7 楼 DBA_Huangzj 的回复:

你是在sp里面insert、update、delete都要去掉？

是这个SP中有insert,update,delete这个表的操作，并用该user调用SP做这些操作时没有权限限制。这样可以控制user只能通过SP来对该表有增，删，改的动作。。

[發糞塗牆]

/*
USE master
GO
CREATE DATABASE test1
GO
USE test1
go
CREATE TABLE test(id INT )
go

USE [master]
GO
CREATE LOGIN [test] WITH PASSWORD=N'', DEFAULT_DATABASE=[test1], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [test1]
GO
CREATE USER [test] FOR LOGIN [test]
GO
USE [test1]
GO
ALTER ROLE [db_datareader] ADD MEMBER [test]
GO


SELECT * FROM test

INSERT INTO test VALUES(1)
/*
The INSERT permission was denied on the object 'test', database 'test1', schema 'dbo'.

*/
DELETE FROM test
/*
Msg 229, Level 14, State 5, Line 8
The DELETE permission was denied on the object 'test', database 'test1', schema 'dbo'.
*/
UPDATE test SET id=1
/*
Msg 229, Level 14, State 5, Line 13
The UPDATE permission was denied on the object 'test', database 'test1', schema 'dbo'.
*/

*/
--用高权限创建存储过程
ALTER PROC testsp
WITH EXECUTE AS OWNER 
AS 
	INSERT INTO test VALUES(1)

--用test来执行可以看到
EXEC testsp
/*
(1 row(s) affected)
*/
--但是执行UPDATE test SET id=1
--还是会报错
/*
Msg 229, Level 14, State 5, Line 13
The UPDATE permission was denied on the object 'test', database 'test1', schema 'dbo'.
*/

[發糞塗牆]

在整个过程中，你需要用两个帐号来操作，一个是高权限帐号，我用了sa，来创建登录名test ，并仅授予这个帐号db_datareader的数据库级别角色，这个角色只能读不能改（包括增删改），所以如果你用test登录，是只能select，其他操作会报错，然后我用sa创建一个存储过程，然后加上with execute as owner，这样我再用test登录，然后执行，就可以进行修改操作，但是单纯执行update这些操作还是会报错，应该满足你的要求了

[davy150484671]

以上的都应该是不行的。。。我的要求是user对某个表只有只读的权限。。。 但是在执行的SP中，对这个表有insert&update&delete 的功能。。

[發糞塗牆]

你是在sp里面insert、update、delete都要去掉？

[davy150484671]

非常不错。万分感谢。。