Adding user and password to Sql Server database

Question

My connection string defined in Web.Config currently indicatea no user or password is needed:

<connectionStrings>
    <add name="CinemaxConnectionString" connectionString="Data Source=PCName\SQLEXPRESS;Initial Catalog=Cinemax;Integrated Security=True"
      providerName="System.Data.SqlClient" />
  </connectionStrings>

I would like to add user and password, like this:

<connectionStrings>
    <add name="CinemaxConnectionString" connectionString="Data Source=PCName\SQLEXPRESS;Initial Catalog=Cinemax;User=cinemax; Password=abc"
      providerName="System.Data.SqlClient" />
  </connectionStrings>

Obviously, this requires changing some settings in Sql Server to add user and password. What is the best way to do this? I specify I have Sql Server 2005 Express and the current authentcation mode is "Sql Server and Windows Authentication". Thank you in advance for your help. It would be nice to see a double take on this: user-interface and code solution.

Will it not work if you just use ;uid=cinemax;pwd=abc - it's set up for Sql Server authentication? — Sam Peacey
– Sam Peacey, Commented Jun 8, 2012 at 9:13
You shouldn't need to set anything up that I'm aware of, unless you mean you haven't set up the user as Rajesh has shown how? Actually you have a typo in your connection string, it should be: Data Source=PCName\SQLEXPRESS;Initial Catalog=Cinemax;User ID=cinemax;Password=abc (note 'User ID') — Sam Peacey
– Sam Peacey, Commented Jun 8, 2012 at 9:31
It worked by typing just "User" so perhaps both variants are valid. Thank you cheeseweasel! — Anna T
– Anna T, Commented Jun 8, 2012 at 9:41

Neil Knight · Accepted Answer · 2012-06-08 09:29:10Z

16

The below statements would do the job for you.

CREATE LOGIN cinemax WITH PASSWORD = 'abc';

GO

CREATE USER cinemaxUser FOR LOGIN cinemax

GO

GRANT SELECT TO cinemaxUser

GO

GRANT INSERT TO cinemaxUser

GO

GRANT UPDATE TO cinemaxUser

GO

GRANT DELETE TO cinemaxUser

GO

edited Jun 8, 2012 at 9:29

Neil Knight

48.8k26 gold badges136 silver badges193 bronze badges

answered Jun 8, 2012 at 9:26

Rajesh

3805 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Anna T Over a year ago

Great, thank you Rajesh, it worked, and I'm glad with the coding solution - which I prefere more than the interface. I had to restart the mysql service for this to work, and then the site gave an error because I didn't have "execute permissions" on a procedure. I solved that from the interface by selecting all permissions. Is there a better way to do this?

Anna T Over a year ago

I also had to restart Visual Studio because it gave this error: "A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)" - which vanished after the restart. Now all works perfectly.

Rajesh Over a year ago

GRANT EXECUTE TO "SP_Name". After changing any permission settings, It is good idea to restart the services coz sometimes it will still hold the previous settings.

David Parvin Over a year ago

As far as the transport-level error, this is caused by your software trying to use a connection that is closed that it thinks is open. All you have to do is run it again and it works because when the error happens, it also closes the connection in your software and the connection is remade when you next try to do whatever you are doing.

Roboblob Over a year ago

It works. If you need to create user with bypassing the password strength policy, use this: CREATE LOGIN cinemax with Password ='weak', CHECK_POLICY = OFF

Collectives™ on Stack Overflow

Adding user and password to Sql Server database

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related