Membership.GetAllUsers() System.Data.SqlClient.SqlException: User does not have permission to perform this action

Question

I created a small web application using Visual Web Developer 2010 Express. After I deployed my site and go to the first page, which is the login page that provides a list of users for the app, I get this error:

Exception Details: System.Data.SqlClient.SqlException: User does not have permission to perform this action.

Source Error:

Line 22:         public ActionResult LogOn()
Line 23:         {
Line 24:             var users = Membership.GetAllUsers().Cast<MembershipUser>().ToList();
Line 25:             SelectList usernames = new SelectList(users);
Line 26:             ViewBag.UserNames = usernames;

Here is my connectionStrings settings:

<connectionStrings>
   <add name="ApplicationServices"
     connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true"
     providerName="System.Data.SqlClient" />
</connectionStrings>

Why is this error occurring? Any help is much appreciated. I'm currently developing it on a Windows 2008 Server and testing the deployment of it on the same machine.

This sounds like your connection string is specifying a user that does not have permission to select from the users table. — Jeremy Holovacs, Apr 10 '13 at 20:38
@JeremyHolovacs I'm developing and testing the deployment on the same machine. It works when I'm debugging it in Visual Web Developer. What to I need to do with SQL Server Express to get the permission to work? — massaskillz, Apr 10 '13 at 20:54
Are you using SSPI (Trusted connections)? I would make a Sql User account on your application services database with read/ write access, and use that credential in your connection string. — Jeremy Holovacs, Apr 10 '13 at 20:56

score 1 · Answer 1 · edited Apr 13 '17 at 12:13

I found the issue with my connection string. It was set up for user instancing, which according to this Microsoft article:

Web applications running on IIS 7.5 and that rely on SQL Server Express user instancing will fail to run using the default IIS 7.5 security configuration on both Windows 7 Client and Windows Server 2008 R2

The article also recommends avoiding usage of user instances in production. I found this post which helped me host the database in a normal .\SQLExpress instance. I changed my connection strings to:

<connectionStrings>
    <add name="ApplicationServices"
     connectionString="Database="app_users";Data Source=.\SQLEXPRESS;Initial Catalog=app_users;Integrated Security=SSPI;"
     providerName="System.Data.SqlClient"/>
</connectionStrings>

Before changing the above connection string, I attached the aspnetdb.mdf file in SQL Server Express to a new database I created called "app_users".

I no longer get the permissions error.

Membership.GetAllUsers() System.Data.SqlClient.SqlException: User does not have permission to perform this action

1 Answers1