Hi:
I have a SQL Server 2000 issue that I am trying to solve. I need to grant or deny certain members of a database role the permissions to insert/update/delete certain rows within a table based upon a conditional statement. I'm trying to use the IS_MEMBER function to deny members of a certain SQL Server database role the rights.
I have a conditional WHERE clause that I use -- below:
SELECT FundSites.OBAN_ID
FROM FundSites
WHERE FundSites.OBAN_ID Like '43%'.
This SQL statement returns only those OBAN_ID rows that have a OBAN_ID beginning with 43 (e.g. 4300, 4301, 43AE, 4325). I want to grant members of a database role the permission to only INSERT/UPDATE records that have an OBAN_ID like '43%'.
I created a database role called FMA. So I thought that by stating:
IF IS_MEMBER('FMA') = 1
GRANT INSERT,UPDATE,DELETE ON FundSites TO ...
WHERE FundSites.OBAN_ID Like '43%'
After reading up on the DENY or GRANT permissions statements, I realized that I cannot use the conditional WHERE clause in the statement to accomplish the result that I want.
So how can I conditionally DENY or GRANT permissions to users assigned to a database role based upon a condition for a table column?
Also, I need to add that I'm using an Access 2000 project file (.adp) as the front-end to this SQL Server. Currently the users are allowed to insert, update, or delete records via an Access form bound to an updateable view (based on the FundSites table).
Any suggestions on what I should do in this case?
Since I can't directly use the DENY/GRANT statements with a conditional WHERE clause, can't I create a stored procedure to control all this and apply the DENY/GRANT statements to the view with the WHERE clause (...WHERE FundSites.OBAN_ID Like '43%')?
Any help on this would be greatly appreciated.
Thanks,
Cheryl3D
I have a SQL Server 2000 issue that I am trying to solve. I need to grant or deny certain members of a database role the permissions to insert/update/delete certain rows within a table based upon a conditional statement. I'm trying to use the IS_MEMBER function to deny members of a certain SQL Server database role the rights.
I have a conditional WHERE clause that I use -- below:
SELECT FundSites.OBAN_ID
FROM FundSites
WHERE FundSites.OBAN_ID Like '43%'.
This SQL statement returns only those OBAN_ID rows that have a OBAN_ID beginning with 43 (e.g. 4300, 4301, 43AE, 4325). I want to grant members of a database role the permission to only INSERT/UPDATE records that have an OBAN_ID like '43%'.
I created a database role called FMA. So I thought that by stating:
IF IS_MEMBER('FMA') = 1
GRANT INSERT,UPDATE,DELETE ON FundSites TO ...
WHERE FundSites.OBAN_ID Like '43%'
After reading up on the DENY or GRANT permissions statements, I realized that I cannot use the conditional WHERE clause in the statement to accomplish the result that I want.
So how can I conditionally DENY or GRANT permissions to users assigned to a database role based upon a condition for a table column?
Also, I need to add that I'm using an Access 2000 project file (.adp) as the front-end to this SQL Server. Currently the users are allowed to insert, update, or delete records via an Access form bound to an updateable view (based on the FundSites table).
Any suggestions on what I should do in this case?
Since I can't directly use the DENY/GRANT statements with a conditional WHERE clause, can't I create a stored procedure to control all this and apply the DENY/GRANT statements to the view with the WHERE clause (...WHERE FundSites.OBAN_ID Like '43%')?
Any help on this would be greatly appreciated.
Thanks,
Cheryl3D
