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 [用户名]
新建帐户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
/* USE master GO CREATE DATABASE test1 GO USE test1 go CREATE TABLE test(id INT ) goUSE [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 testINSERT 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这些操作还是会报错,应该满足你的要求了
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 [用户名]
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
USE master
GO
CREATE DATABASE test1
GO
USE test1
go
CREATE TABLE test(id INT )
goUSE [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 testINSERT 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'.
*/