Datebase roles problem.

OK this is going to sound like a very easy question but for the life of me its not working.

I have got a login called "Sales" and it is binded to a user called "sales"

The sales user has of course got the public role for my database.

I have created a Role on the database called "Sales Role" and given all the needed permissions to all the tables in the database.

As soon as i give the user the new role and then go to the securables area and look at the tables and hit the "Effective Privileges" button there is nothing listed....

If i take off the "Sales Role" role from the user and go back and look at the "Effective Privileges" it is filled with the privileges the public role has given it.

Any one no why as soon as i give the user my role (which has got privileges set for every table) the user does not have any effective rights on any table?

OK I sorted it my self. To be honest I think it’s rather annoying how they have done it....

I gave the user, select, Delete, Insert and Update privileges and denied the rest... which should have worked in my opinion.

What I did not no was the give a user the privileges I wanted I also had to grant them "control" privileges too... apart from that not being too obvious they should really have given out a warning to say that the following privileges can't be granted with out "control" being granted also, either that or automatically give the user the control privilege if you give them the select privilege?

What you guys think? Was it just me being stupid or what, I could not find the answer any where on Google and you didn’t have to do that in SQL 2000 either.

|||

CONTROL is a covering permission, which means it implies all other permissions on the entity as well. If you grant CONTROL, you also grant the other appropriate permissions depending on entity type. If you deny CONTROL, you will deny all permissions on the object.

So, you don't need to grant CONTROL, to be able to grant SELECT on a table - you can just grant SELECT. On the other hand, if you grant SELECT, but deny CONTROL, the SELECT won't take effect because the deny on the covering permission takes precedence.

SQL Server 2000, didn't had covering permissions, such as CONTROL.

See the following article, for additional information: http://msdn2.microsoft.com/en-us/library/ms177450.aspx.

Thanks
Laurentiu