I have a SQL Server 2005 database with a large number of tables in the dbo schema. I now created a new schema (call it myschema) that only has three table-valued functions and two stored procedures in it. All of that code has access to the tables in dbo.
The code in myschema will ultimately be called from a web service and I am struggling to get the permissions right for the user I created for the web service.
At first, I created the user with no roles except public and then gave it specific permissions on the securables in myschema. But then I could log on using that user and select from (and ever update) anything in dbo.
So I gave the user the denydatareader and denydatawriter roles, which effectively restricted the access to the objects in dbo.
The result of this is that I can execute the two stored procedures just fine.
But if I try to use the table-valued functions, I get this error:
The SELECT permission was denied on the object 'MyFunction', database 'MyDB', schema 'myschema'.
This is despite my use of:
grant select on myschema.MyFunction to MyUser
I'm guessing that's because of my brilliant use of denydatareader.
So what is the correct way to give a user access only to a list of specific stored procedures and table-valued functions and not to anything else?
Update: Based on Nico's answer below, here's what I did. For the sake of brevity, just pretend you see the exists checks.
create table dbo.testtable(id int) go insert into dbo.testtable (id) values (1) insert into dbo.testtable (id) values (2) insert into dbo.testtable (id) values (3) go create schema myschema go create login mylogin with password = 'SomePassword', check_policy = off, default_database=[MyDatabase], default_language=[us_english]; create user myuser for login mylogin with default_schema = [myschema]; create role myschemarole; execute sp_addrolemember N'myschemarole', N'myuser';
Then logged in with mylogin:
-- Error: The SELECT permission was denied... select * from dbo.testtable -- Error: The SELECT permission was denied... update dbo.testtable set id = 4 where id = 2 -- Success: (3 row(s) affected) update dbo.testtable set id = 4 -- Success: (1 row(s) affected) insert into dbo.testtable (id) values (5)
As long as I don't have a where clause, I can insert and update to my heart's content. And on top of that, "use master" also works and the sys schema is also fair game.
Does this mean I still need denydatawriter?