CODEWITHSUNDEEP
sql-serversql-server-2005-expressdatabase-permissions
Jon Lawson
Jon Lawson

Reputation: 612

Error accessing stored procedure

We have a .NET3.5 Windows Forms application that calls an ASP.NET SOAP web service on the same server. This web service then saves the data into a SQL Server Express 2005 database, again on the same server.

The application has been deployed internally, as well as to a number of our customers and has worked as expected for over 18 months. However this week, at our main customer's site, the application has started generating an error message on four out of the 10 servers it is deployed on. All of the web service methods use the same connection string, but only one web method is affected.

The connection string has this format:

Data Source=MYSERVER\SQLEXPRESS;Initial Catalog=MyDatabase;
Persist Security Info=True;User ID=MyUser;Password=MyPassword

When the application calls one particular web service method, it is producing an error:

Cannot find the user 'MyDatabase', because it does not exist or you do not have permission.

There have been no changes to the application to cause this, however the customers' IT people may have made changes to these servers. MyUser has been granted permissions on the stored procedures and is able to connect to all of the other web service interfaces.

The main thing that is confusing me is that the error messages says that the user that does not exist is MyDatabase which is the Initial Catalog name in the connection string.

I have been onsite with the customer and verified that the application is still set up correctly and that all of the other web service interfaces are correctly connecting to the database.

Any suggestions of either possible causes or other things I can check would be gratefully received.

Upvotes: 2

Views: 225

Answers (1)

Kamal Deepak
Kamal Deepak

Reputation: 221

I would strongly advise against removing the dbo. That is the schema name. Simply removing it will imply to the DB to use the default schema, which is probably dbo anyways. So it would accomplish nothing. But by not using the schema name when calling the procedure you can run into naming conflicts. For example, if you have a procedure with the same name in two different schemas in the DB, and you try to call it without a schema name, the DB will not know which one to call, resulting in an exception.

Upvotes: 1

Related Questions