Granting permissions only on a set list of objects

Question

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?

Answer 1

I've done this a couple of weeks ago - Created LOGIN, ROLE and USER. Added USER to ROLE and granted explicit permissions to the ROLE itself. The advantage in my opinion is, that you can add further users later on without the struggle to grant the permissions, again.

First I created the login:

IF NOT EXISTS 
( 
  SELECT 
    "name" 
  FROM 
    "master"."dbo"."syslogins" 
  WHERE 
    "name" = 'your_login'
)
BEGIN 
  CREATE LOGIN your_login WITH PASSWORD = 'your_password', CHECK_POLICY = OFF; 
END

I've added the CHECK_POLICY param because things are created on user interaction and I'm not able to see, if policies are activated server-sided.

Followed by the user:

IF NOT EXISTS 
( 
  SELECT 
    1 
  FROM 
    "sys"."database_principals" 
  WHERE 
    "name" = N'your_username'
)
BEGIN 
  CREATE USER your_username FOR LOGIN your_login;
END

And the role third:

IF NOT EXISTS 
( 
  SELECT 
    1 
  FROM 
    "sys"."database_principals" 
  WHERE
    "name" = N'your_role_name'  
)
BEGIN
  CREATE ROLE "your_role_name"; 
END

Finally I added the user to the created role:

EXECUTE sp_addrolemember N'your_role_name', N'your_username';

After that, permissions can be set for each type of object, eg

GRANT EXECUTE ON "dbo"."your_sproc" TO your_role_name;

You don't have to add denydatareader or else, if you explicitly grant permissions to the specific objects. Beware that an for example INSERT permission doesn't include the SELECT permission on the same object. ( at least in my case it didn't ;) )

If you have problems executing or selecting from your UDF, check if you have granted all permissions on the referenced objects in the UDF itself!

Update after Cobus' update in OP:

I've executed the queries you provided based on my initial answer, results following:

select * from dbo.testtable 

-- The SELECT permission was denied on the object 'testtable', database 'xyz', schema 'dbo'.

update dbo.testtable set id = 4 where id = 2

-- The SELECT permission was denied on the object 'testtable', database 'xyz', schema 'dbo'. -- The UPDATE permission was denied on the object 'testtable', database 'xyz', schema 'dbo'.

update dbo.testtable set id = 4

-- The INSERT permission was denied on the object 'testtable', database 'xyz', schema 'dbo'.

insert into dbo.testtable (id) values (5)

-- The INSERT permission was denied on the object 'testtable', database 'xyz', schema 'dbo'.

It behaves as intended. Do you have any further permissions granted / set or properties which allow you to access dbo-schema?