Adding user and password to Sql Server database

sql-server-2005

Solution

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

Problem

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.

Original source