EXECUTE permission denied on object 'aspnet_CheckSchemaVersion'

Exception Details:

System.Data.SqlClient.SqlException: EXECUTE permission denied on object 'aspnet_CheckSchemaVersion', database 'g4ity_dbase4ity', owner 'dbo'.

OR

ANY kind of Execute Permission Denied on sql Server object.

Solution

• Goto properties of the dbo.aspnet_StoredProcedureName
• Open Permissions
• Allow your username for exec Permission

Overview

Whilst SQL Server 2000 has fixed database roles such as db_datareader and db_datawriter that allow a user read or write access respectively to all the table is a database, no such role exists for the execution of stored procedures (a db_executor role if you will). This article describes how to grant execute permission to all stored procedures in a database to a specific user or role in both SQL2000 and SQL2005

SQL2000

A common answer to the question posed by the title of this article is to run a query such as the one below in Query Analyzer and copy and paste the results into a query window and execute them. The query uses the INFORMATION_SCHEMA views to generate a list of GRANT statements for each procedure in the database.

Do one of the following

1. Goto properties of the dbo.aspnet_StoredProcedure Name Open PermissionsAnd Allow your username for exec Permission
   .
2. To change permissions on an individual object
   grant exec on [Owner].[ObjectName] TO UserName
   .
3. Simply run in sql Query Analyzer to change permissions on all objects in a database. It will generate a resultset with GRANT permission queries for all possible objects. copy it and paste in the query window of query analyzer and run it.
   SELECT 'grant exec on ' + QUOTENAME(ROUTINE_SCHEMA) + '.' +QUOTENAME(ROUTINE_NAME) + ' TO ' FROM INFORMATION_SCHEMA.ROUTINES
   WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0
   .
4. Run this code if you want to change permissions on objects under dbo's ownership. Run this code same as mentioned in part 2 above.
   SELECT 'grant exec on ' + QUOTENAME(ROUTINE_SCHEMA) + '.' +QUOTENAME(ROUTINE_NAME) + ' TO ' FROM INFORMATION_SCHEMA.ROUTINESWHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0 AND QUOTENAME(ROUTINE_SCHEMA)='[dbo]'

SQL2005

SQL Server 2005 improves on the current situation by making the EXECUTE permission grantable at the database scope. This means that we can issue a statement like the example below and this will GRANT execute permissions on all existing stored procedures and scalar functions AND all subsequently created ones. Thus it acts very much like the current fixed database roles such as db_datareader

/* CREATE A NEW ROLE */
CREATE ROLE db_executor
/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO db_executor