请教一个sql server 用户读写权限控制的问题
作为一个DBA的角色， 我想控制一个用户对数据库的表只有读取功能。。。并且实现通过SP来做insert&update&delete 操作。。。请问怎样让用户调用SP时，可以对某个表有insert&update&delete操作的权限呢。。。用 with execute as 可以实现？ 具体怎样写呢？
[解决办法]
设定用户在数据库级别只有DB_Datareader权限，然后授予exec的权限，试试这样够了没？
[解决办法]
-- 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 [用户名]
/*
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'.
*/
