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.

Answer 1

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