The EXECUTE permission was denied on the object 'aspnet_CheckSchemaVersion', database 'XXX'

Question

I use asp.net 4 C# and entity framework 4 with MS SQL 2008. I'm trying to set up my web application locally using IIS 7.

For my website I user Asp membership provider which has installed different tables and sprocs in my db (aspnet_).

Running the script we receive this error:

The EXECUTE permission was denied on the object 'aspnet_CheckSchemaVersion', database 'XXX', schema 'dbo'. at System.Data.SqlClient.SqlConnection.OnError

How can I solve the problem?

Solution 1

There should be some db roles related to the membership tables, eg aspnet_profile_fullaccess. Make sure the account you're using is a member of the appropriate role.

You should NOT assign the user you connect to the DB as dbowner privilege. The account should have only the rights it needs & nothing more. If you grant dbo & someone were to exploit a flaw in your website they would have full uncontrolled access to your entire db to what they wanted - delete tables, change data at will.

Solution 2

I don't think you should make the user the db_owner. I had the same problem, and it was sufficient to grand the 4 roles with BasicAccess to my user + to give him EXECUTE permission on all stored proc:

GRANT EXECUTE TO [theUserName];

I know this is not ideal. One should grant EXECUTE permissions only on the required stored proc, but if you need a quick solution until you find which SP's your user needs to be able to execute, this should work.

Solution 3

I agree that one can test by adding the db_owner role to verify the permission error. But should be reminded with all urgency to test only and be sure to remove the role.

Right click on the Login User >> select properties >> then User Mapping.

Looking at the SQL error provided, you can surmise this is the issue based on "Execute Permission was denied". After using db_owner role as a test and confirmation, one can then look at the various SQL statements to see what stored procedures are getting called. For example consider the following

SQL As String = "EXEC [EAC].[myStoredProcedure] " etc . . . "

After you find out the various stored procedures used by the application, you can then grant execute on those specific executes. For example, considering the following SQL.

USE DATABASE
GRANT EXECUTE ON OBJECT::EAC.myStoredProcedure
TO myRoleorUser; 

Note that the EAC.myStoredProcedure mentioned in the code is found when expanding the database and then Programmability and then expand Stored Procedures.

Here is the Microsoft KB for further help on targeting the specific stored procedures on SQL server. https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/grant-permissions-on-a-stored-procedure?view=sql-server-2017

Solution 4

The problem is that the User ID the application is logging in with doesn't have enough privileges in the database. It either needs to be the database owner or be granted permissions on all the aspnet_ stored procedures.

So please check the permissions in SQL server 2008 for this particular user. and if possible make this user as a dbowner.

Hope this helps...

Edit : i wanted you to make it as dbowner just to verify that there are some permission issues,once you are sure about the problem, you can assign permissions to that user. hence knowing the exact cause and the exact solution.

View more solutions

Comments

[Simon Halsey]

you're a nob? I think you meant noob, but that is a comedy typo.

[Duke Hall]

Answered here: stackoverflow.com/questions/16134262/…

[GibboK]

thanks Simone I read carefully your answer and I agree with you.

[awrigley]

Voted down for suggestion to make the user a dbowner. I know you qualified this in your edit, but I know just enough to know this statement could be dangerous.

[ajbeaven]

The user I'm connecting with has all aspnet_* roles assigned to them and I'm still getting this error message. The only way it seems to work is if I add the dbowner role. Anything you know of that would be causing this?

[arni]

You may have to run the ASP.NET SQL Server Registration Tool: msdn.microsoft.com/en-us/library/ms229862(v=vs.100).aspx