If the owner of the stored procedure has the rights to select, insert, update or delete against a table then select, insert, update and delete statements inside the stored procedure will execute as long as the caller has execute rights on the stored procedure, even if the caller does not have rights to directly perform select, insert, update or delete against the table.
However a stored procedure can not perform DDL unless the caller has rights to perform DDL even if the owner of the stored procedure has DDL rights. Note this also applies to truncate table.
Answer: In your case granting db_datareader and db_datawriter to a user already gives the user full DML on all tables. Granting execute on any stored procedure will not give any additional rights.
Stored procedures can be used to increase data integrity by providing a gate through which all external programs must go. Do not grant insert, delete or update, but create SPs that do the work and enforce the appropriate rules about the data. (Above and beyond what can be done with constraints.) And as Joe Kuemerle points out, stored procedures can be used to increase security.
I have observed this behavior while developing an application on SQL Server 2000 and this even re-tested on SQL Server 2008 and found the same behavior. I have not been able to find documentation on this behavior.
Logged in as DBO and SA create a table:
create table dbo.SO (PK int identity constraint SO_PK primary key
, SomeData varchar(1000)
)
Then create some stored procedures for basic 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
As dbo, we can run the following SQL statements:
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
Or do the equivalent via the stored procedures
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
Now, create a DDL stored procedure and test:
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
And now create another user and grant execute rights to all the stored procedure. Do not grant any other rights. (Assumes public does not have extra rights and mixed mode authentication. Mixed mode authentication is not recommended, but makes testing how rights are handled easier.)
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
Login in as SoLogin. Try the 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
Nothing but errors:
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.
Try the basic DML stored procedures:
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
They work, because the owner of the SPs have the right rights, even though SoUser does not.
Try the truncate or drop stored procedure:
exec dbo.TruncateSO
go
exec dbo.DropSO
Errors again:
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.