One Connection String for Multiple Users

[joe_pool_is]

We have one connection string to our SQL Server 2000:

 

"Data Source=WORKAPP;Initial Catalog=Parts_Data;Integrated Security=SSPI;User ID=public;Password=public";

 

Everyone can connect to the database in our plant except for the Restricted Users.

 

Why can't the Restricted Users access this? How can we get around it?

 

I can access the data, but I am set up as an Administrator.

 

Our managers can access the data, but they are set up as Power Users.

 

Most machines are Windows XP, but some use Citrix Servers and a few are running Windows Vista. The OS does not seem to make any difference.

 

We do not want to grand Power User status to everyone, and we should not need to with the correct connection string.

[Nate Bross]

The connection string looks like it should be OK to me, it's got a uid/pwd hard coded into the string, so all users should be using the same level of access.

 

Are the users with restricted access able to display data, but not update/edit? or do they have no access at all?

[joe_pool_is]

Hi Nate,

 

Turned out that the Integrated Security overrides the User ID/Password settings if it is enabled. (If it is False, then User ID/Password are used).

 

So, I got this fixed, and now our restricted users have access to the data. The only problem is that our regular (power) uses no longer have access.

 

The message I get whenever I attempt to run from my machine is:

 

"EXECUTE permission denied on object 'sp_sdidebug', database 'master', owner 'dbo'."

 

The first line of the StackTrace says:

 

" at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)"

The restricted users do not see this. Any ideas?

[joe_pool_is]

Oh man - I just found it!

 

In the project properties, I had enabled SQL Server debugging, but the global User ID/Password did not have authorization to do this!

 

Solution: Uncheck "Enable SQL Server debugging" or set Integrated Security to SSPI (True).

 

I wanted to post the results. I hope someone that needs help is able to pull this post one day.