db_datareader和db_datawriter已经赋予了用户对所有表的完全DML权限。对任何存储过程授予执行权限将不会增加任何其他权限。create table dbo.SO (PK int identity constraint SO_PK primary key
, SomeData varchar(1000)
)
接下来创建一些基本的DML存储过程：
create procedure dbo.InsertSO (@SomeData varchar(1000)) as
begin
insert into dbo.SO (SomeData) values (@SomeData)
return SCOPE_IDENTITY()
end
go
create procedure dbo.SelectSO (@PK int=null) as
begin
if @PK is not null
select PK, SomeData from dbo.SO where PK = @PK
else
select PK, SomeData from dbo.SO
end
go
create procedure dbo.CountSO as
begin
select COUNT(*) as CountSO from SO
end
go
create procedure dbo.DeleteSO (@PK int=null ) as
begin
if @PK is not null
delete dbo.SO where PK = @PK
else
delete dbo.SO
end
go
create procedure dbo.UpdateSO (@PK int, @NewSomeData varchar(1000)) as
begin`
update dbo.SO
set SomeData = @NewSomeData
where PK = @PK
end
go
create procedure dbo.TruncateSO as
begin
truncate table dbo.SO
end
go
declare @PK_to_update int
insert into dbo.SO (SomeData) values ('Hello world!')
set @PK_to_update = SCOPE_IDENTITY()
declare @PK_to_delete int
insert into dbo.SO (SomeData) values ('Goodbye cruel world!')
set @PK_to_delete = SCOPE_IDENTITY()
insert into dbo.SO (SomeData) values ('Four score and seven years ago...')
select PK, SomeData
from dbo.SO
delete dbo.so
where PK = @PK_to_delete
update dbo.SO
set SomeData = 'Hello Milky Way!'
where PK = @PK_to_update
select PK, SomeData
from dbo.SO
truncate table dbo.SO
select COUNT(*) as CountSO from dbo.SO
或者通过存储过程来完成相应的操作
go
declare @PK_to_update int
exec @PK_to_update = dbo.InsertSO 'Hello world!'
declare @PK_to_delete int
exec @PK_to_delete = dbo.InsertSO 'Goodbye cruel world!'
exec dbo.InsertSO 'Four score and seven years ago...'
exec dbo.SelectSO
exec dbo.DeleteSO @PK_to_delete
exec dbo.UpdateSO @PK_to_update, 'Hello Milky Way!'
exec dbo.SelectSO
exec dbo.TruncateSO
exec dbo.CountSO
create procedure dbo.DropSO as
begin
drop table dbo.SO
end
go
begin transaction
select TABLE_NAME from INFORMATION_SCHEMA.TABLES
where TABLE_NAME = 'SO'
exec dbo.DropSO
select TABLE_NAME from INFORMATION_SCHEMA.TABLES
where TABLE_NAME = 'SO'
rollback transaction
现在创建另一个用户，并授予所有存储过程的执行权限。不要授予任何其他权限。（假设公共用户没有额外的权限和混合模式身份验证。不建议使用混合模式身份验证，但可以使测试权限处理更容易。）
exec sp_addlogin @loginame = 'SoLogin' , @passwd = 'notsecure', @defdb = 'Scratch'
exec sp_adduser @loginame = 'SoLogin', @name_in_db = 'SoUser'
go
grant execute on dbo.InsertSo to SoUser
grant execute on dbo.InsertSO to SoUser
grant execute on dbo.SelectSO to SoUser
grant execute on dbo.CountSO to SoUser
grant execute on dbo.DeleteSO to SoUser
grant execute on dbo.UpdateSO to SoUser
grant execute on dbo.TruncateSO to SoUser
grant execute on dbo.DropSO to SoUser
以SoLogin身份登录。尝试使用DML：
declare @PK_to_update int
insert into dbo.SO (SomeData) values ('Hello world!')
set @PK_to_update = SCOPE_IDENTITY()
declare @PK_to_delete int
insert into dbo.SO (SomeData) values ('Goodbye cruel world!')
set @PK_to_delete = SCOPE_IDENTITY()
insert into dbo.SO (SomeData) values ('Four score and seven years ago...')
select PK, SomeData
from dbo.SO
delete dbo.so
where PK = @PK_to_delete
update dbo.SO
set SomeData = 'Hello Milky Way!'
where PK = @PK_to_update
select PK, SomeData
from dbo.SO
truncate table dbo.SO
go
select COUNT(*) as CountSO from dbo.SO
go
drop table dbo.so
只有错误：
Msg 229, Level 14, State 5, Line 2
The INSERT permission was denied on the object 'SO', database 'Scratch', schema 'dbo'.
Msg 229, Level 14, State 5, Line 6
The INSERT permission was denied on the object 'SO', database 'Scratch', schema 'dbo'.
Msg 229, Level 14, State 5, Line 9
The INSERT permission was denied on the object 'SO', database 'Scratch', schema 'dbo'.
Msg 229, Level 14, State 5, Line 11
The SELECT permission was denied on the object 'SO', database 'Scratch', schema 'dbo'.
Msg 229, Level 14, State 5, Line 14
The SELECT permission was denied on the object 'SO', database 'Scratch', schema 'dbo'.
Msg 229, Level 14, State 5, Line 14
The DELETE permission was denied on the object 'SO', database 'Scratch', schema 'dbo'.
Msg 229, Level 14, State 5, Line 17
The SELECT permission was denied on the object 'SO', database 'Scratch', schema 'dbo'.
Msg 229, Level 14, State 5, Line 17
The UPDATE permission was denied on the object 'SO', database 'Scratch', schema 'dbo'.
Msg 229, Level 14, State 5, Line 21
The SELECT permission was denied on the object 'SO', database 'Scratch', schema 'dbo'.
Msg 1088, Level 16, State 7, Line 24
Cannot find the object "SO" because it does not exist or you do not have permissions.
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'SO', database 'Scratch', schema 'dbo'.
Msg 3701, Level 14, State 20, Line 2
Cannot drop the table 'SO', because it does not exist or you do not have permission.
尝试基本的DML存储过程：
declare @PK_to_update int
exec @PK_to_update = dbo.InsertSO 'Hello world!'
declare @PK_to_delete int
exec @PK_to_delete = dbo.InsertSO 'Goodbye cruel world!'
exec dbo.InsertSO 'Four score and seven years ago...'
exec dbo.SelectSO
exec dbo.DeleteSO @PK_to_delete
exec dbo.UpdateSO @PK_to_update, 'Hello Milky Way!'
exec dbo.SelectSO
他们起作用是因为SP的所有者拥有正确的权限，即使SoUser没有。
尝试使用截断或删除存储过程：
exec dbo.TruncateSO
go
exec dbo.DropSO
再次出现错误：
Msg 1088, Level 16, State 7, Procedure TruncateSO, Line 4
Cannot find the object "SO" because it does not exist or you do not have permissions.
Msg 3701, Level 14, State 20, Procedure DropSO, Line 4
Cannot drop the table 'SO', because it does not exist or you do not have permission.
你需要的概念是 "所有权链"
基本上，存储过程使用的同一架构（例如dbo）中的对象不会进行权限检查。除了：拒绝权限始终被检查。
因此，如果存储过程dbo.uspDoStuff使用表dbo.Parent和dbo.Child，则不需要对表进行权限控制，它就可以正常工作。除非你运行了"DENY SELECT ON dbo.Parent to MyUser"。
注意：通常你会执行"CREATE ROLE MyRole"，将用户添加到角色中，并授予角色权限。db_datareader只是一个特殊的保留角色。
授予执行权限将允许该人在存储过程的上下文中执行存储过程所执行的任何操作（因此，如果存储过程删除表，则用户将能够执行存储过程以删除该表）。
编辑，我刚刚检查发现我错了。拒绝访问不会撤销对存储过程中执行操作的能力。
以下是 MSDN 上的文章，指定拒绝访问对存储过程不会产生影响。
http://msdn.microsoft.com/en-us/library/bb669058.aspx
更新： 您可能能够通过在存储过程中使用sp_executeSQL执行一个drop table命令，并拒绝用户删除表的权限。这应该可以防止存储过程成功执行该命令（除非用户有权限这样做），因为要使用sp_executesql，用户需要具有执行sql操作而不仅仅是访问存储过程的权限。