Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
BitShift
Yak Posting Veteran
98 Posts |
 Posted - 2008-04-05 : 14:27:49
|
| I have a stored procedure in which at the bottom of the code, im granting execute permissions to a role I have defined. However, when I view the permissions on the procedure, the role isnt there, what could I be missing ? The procedures were all created under the default or dbo schema. I could manually give the permissions to the role, but id rather have it scripted.help ? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-04-05 : 14:36:08
|
| I dont think you can grant execute permission to SP from inside code. You should first create SP and then grant execute permission from outside the SP to your created role. |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-04-05 : 14:53:08
|
| You Can do with T-sql.Did you run as a batch? it should work |
|
|
|
BitShift
Yak Posting Veteran
98 Posts |
Posted - 2008-04-05 : 15:10:21
|
| Well, I assume by run as a batch, you meant having a go statement after the grant ?For some reason, the "securables" under the role properties lists the procedure that I granted execute permissions to, but the procedure properties does not show the role in its permissions. |
|
|
|
BitShift
Yak Posting Veteran
98 Posts |
Posted - 2008-04-05 : 15:13:48
|
| Im basically doing the same thing in other procedures in another database, and when I run the create procedure script after removing the permissions (as a test), the permissions show up. eg. GRANT EXECUTE ON [dbo].[My_SP_NAME] TO [ROLE_NAME]GOIf the permissions arent showing up in the properties of the procedure, what should I check ? |
|
|
|
BitShift
Yak Posting Veteran
98 Posts |
Posted - 2008-04-05 : 15:31:01
|
| I got it now, but not sure why...Ok, duh...newbie error I suppose ?It was in fact a batch problemI had a GO after the grant, but none before. Does grant have to go in its own batch ? Can it not be in the same batch as CREATE PROCEDURE ? |
|
|
|
|
|
|
|
|
Grant execute to role but permissions dont show up