locked
Execute Permissions for a Stored Procedure RRS feed

  • Question

  • Hi,

    I created a new Stored Procedure using SMS while logged in as 'sa".

    I tried to execute this calling from my VB.Net application and it failed with a message referring to Execute permissions not being granted.

    So I went to the database, to security and selected the User.

    I ticked Grant for the Execute line and it added 'dbo' in the Granter column.

    This look a little strange to me as there were now 2 Execute rows but the other stored procedures that the application could execute were configured the same way.

    The application still raised the same error.

    I changed the Application Log-In to use sa and it runs the stored procedure correctly.

    So with SMS again, I went to the database and to the Stored procedure and right clicked to get properties.

    The User is shown and has 2 Execute Rows, 2nd one has Grant icked with dbo as Grantor

    This is the same with the other Stored Procedures that the Application can run.

    I must be missing something?

    Any help would be appreciated.

    Thanks,

    Doug

    Saturday, March 16, 2013 2:05 AM

Answers

  • Hi Stan,

    I ran the script. It made no difference.

    The permissions had already been set-up using SMS.

    You prompted me to go through it all again.

    I read that Error message 100 times last night and just now noticed that it did not refer to the name of the Stored Procedure but to a User Defined Table Type that I defined to pass a Table to the Stored Procedure.

    I did not know permission needed to be granted to use the User Defined Table Type but should have guessed.

    So added permission for that and it is now working fine.

    Thanks for your response, it prompted me to go through it all again including the Error Message (which usually tells us about what went wrong :) ).

    Thanks

    • Marked as answer by Dactech1 Saturday, March 16, 2013 2:46 AM
    Saturday, March 16, 2013 2:46 AM

All replies

  • how is your application connecting to sql server...let's says it is connecting as userA, so, in the management under that database context, run this

    USE <<DATABASENAME>> GO Grant execute  on <<PROCEDURENAME>> to USERA .. this should give execute permissions to the userA for that Procedure. If the procedure has dynamic sql , you should grant access to the tables as well.


    Hope it Helps!!



    • Edited by Stan210 Saturday, March 16, 2013 2:23 AM
    Saturday, March 16, 2013 2:17 AM
  • Hi Stan,

    I ran the script. It made no difference.

    The permissions had already been set-up using SMS.

    You prompted me to go through it all again.

    I read that Error message 100 times last night and just now noticed that it did not refer to the name of the Stored Procedure but to a User Defined Table Type that I defined to pass a Table to the Stored Procedure.

    I did not know permission needed to be granted to use the User Defined Table Type but should have guessed.

    So added permission for that and it is now working fine.

    Thanks for your response, it prompted me to go through it all again including the Error Message (which usually tells us about what went wrong :) ).

    Thanks

    • Marked as answer by Dactech1 Saturday, March 16, 2013 2:46 AM
    Saturday, March 16, 2013 2:46 AM