Security

[Bell1991]

I have a database with two different groups having access to it.

Group1 has datareader and denydatawriter
Group2 has datareader, denydatawriter, execute on sprocs.

I have one member who is in both groups, what level of access will this individual have? From what i can tell, they have the ability to execute stored procedures.

Is there anyway i can stop this (without removing them from one group)?

 

[mrdenny]

You would need to DENY execution of those stored procedures to Group1.

GRANTs are cumulative, where DENY's overwrite all grants.

DENY EXEC ON YourStoredProc TO Group1;

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[Bell1991]

Thanks - I didn't think about that!