日期:2014-05-17 浏览次数:20585 次
-- 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'.
*/