This portal is to share my acquired knowledge related to Sql Server. I hope will be helpful for novice and professionals.

Friday, October 29, 2010

Grant permission on a Store Procedure To Login

The EXECUTE permission was denied on the object 'SPName', database 'Database', schema 'dbo'.

This error accours when we try to execute a Stored procedure using Login that does not have execute permission on that database.

Here is the solutuion :

Note : Only sysadmin or dbowner can grant permission.

1. Login with sysadmin or dbowner login on server.
2. Go to the properties of Login on which you want to grant permission.
3. Click on User Mapping, and Map the login with database.
4. Then execute Grant statement :
GRANT EXECUTE ON SPName TO LoginName;

Try again now to execute the Stored Procedure with Login that we have permitted now.

No comments: