How to log in to SQL with a user other than sa

my connection string is
"Data Source=w2k3-std;Initial Catalog=CTrack;User Id=sa;Password=********;"
I have created extra users in both my database and the master database but if I change the userid and password as above they fail to connect.
Why?
Cheers
PaulHi, the answer to your question is going to depend upon the errormessage you are seeing. Please be sure to post exact errormessages. This will help others to help you.
For new users, you need to assign them permission to log in to theserver, and you need to grant them permission to access your Ctrackdatabase. If you are having trouble doing this, let us know ifyou are using osql, Enterprise Manager, or some other tool.
And, BTW, you are making an excellent decision to stop using the saaccount for your application. This account should never be usedto access the database from any application.
|||

here is teh error i get when i try to login as a user called paul, i have created the user via enterprise manager both in the master database and my ctrack database. authentication is set to sql & windows (is this the best?) and what hav eI d one wrong or more likely not done at all.
Paul
Login failed for user 'paul'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details:System.Data.SqlClient.SqlException: Login failed for user 'paul'.
Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException: Login failed for user 'paul'.] System.Data.SqlClient.ConnectionPool.CreateConnection() +402 System.Data.SqlClient.ConnectionPool.UserCreateRequest() +147 System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +392 System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372 System.Data.SqlClient.SqlConnection.Open() +384 Web1.login.Button1_Click(Object sender, EventArgs e) +232 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain() +1292

|||Are you sure that the password for user paul is correct? Have you tried resetting it to make sure?
|||yep, I have reset it twice now just to be absolutely sure, where should I create the user though in the master db or my db?|||Well, you're going to actually *create* the user for that instance ofSQL Server, but you'll need to give it permissions to yourdatabase. I'd say that the easiest way to test accounts onceyou've initially created them, is to use them to log into QueryAnalyzer and try to run SQL statements directly. That makes iteasy to see what permissions they do and don't have, without beingbound to only the SQL statements that you application needs to run, andit also rules out potential connection string problems.
|||

i logged into the server as user paul and ran query analyser and quiereied (?) my database without any trouble, authentication is set to sql server and windows any more ideas anyone?

|||

I deleted the user and recreated it using sql authentication rather than windows authentication and now I can connect, what is the differenc and does anyone why I could not connect with windows auth?

Paul

|||Because the User ID and Password attributes of the connection string are specific to SQL authentication.
|||

I knew it had to be me doing something stupid....

thanks all