Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

migrated from stackoverflow.com Mar 12 '13 at 1:36

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 2 down vote accepted

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?

share|improve this answer
    
Thanks! This handles the read access just fine, but I can actually run an update statement on a table on dbo, even though I can't select from it. How do I get past that? –  Cobus Kruger Mar 12 '13 at 7:17
    
Perhaps it's because of the different schemas? I tried above again on a test db and can't update or even select on a table I haven't granted permissions on...! If you try it the way I described, could you please provide informations about role- and login properties ( properties in context-menu in SSMS )? –  Nico Mar 12 '13 at 8:00
    
Please have a look at the defined default schema for login on server! –  Nico Mar 12 '13 at 8:04
    
I initially took my script directly from yours. I'll update with a trimmed down version that shows the problem. –  Cobus Kruger Mar 12 '13 at 13:03
    
I executed your example queries based on my initial answer and updated my answer. –  Nico Mar 13 '13 at 10:59
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.