I am getting a very strange issue occurring when granting and revoking permissions on Roles in my database.
As part of the testing that I'm doing in a Role I revoked the EXEC permission on a stored procedure and when attempting to run the function in my VB app which referenced this procedure I got the error message 'EXECUTE permission denied on stored procedure....' which makes perfect sense. But when going back and granting the permission back to the role when I run the same function in my VB app I get a number of 'SELECT permission denied on object…' errors.
And these SELECT statements are in reference to what the stored procedure is running against ? Is this an issue with SQL security ?
Looking at BOL I found info in regards to the system stored procedure 'sp_change_users_login'. This talks about a discrepancy between the sysusers and syslogins tables. My only concern is that this is for specific users whereas my error is occurring for every member of this role ?
Can anybody provide any insight ?
"For those of you watching in black and white, Spurs are in the all yellow strip." - John Motson
As part of the testing that I'm doing in a Role I revoked the EXEC permission on a stored procedure and when attempting to run the function in my VB app which referenced this procedure I got the error message 'EXECUTE permission denied on stored procedure....' which makes perfect sense. But when going back and granting the permission back to the role when I run the same function in my VB app I get a number of 'SELECT permission denied on object…' errors.
And these SELECT statements are in reference to what the stored procedure is running against ? Is this an issue with SQL security ?
Looking at BOL I found info in regards to the system stored procedure 'sp_change_users_login'. This talks about a discrepancy between the sysusers and syslogins tables. My only concern is that this is for specific users whereas my error is occurring for every member of this role ?
Can anybody provide any insight ?
"For those of you watching in black and white, Spurs are in the all yellow strip." - John Motson
