You'll have to create it first as a user, and then set up the correct permissions for the user.
1. You'll have to ensure that your DB is configured with both
User auth and SQL auth
. If using the Management Studio
: right-click on the Server
, select "Security"
ensure that server authentication is "SQL Server and Windows Authentication mode"
, right click and select "New Login"
;, select SQL Authentication,
username and password
CREATE LOGIN [ test] WITH PASSWORD=N'test', DEFAULT_DATABASE=[MY_DATABASE], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
3. On the DB you want, in security
, select new User
. Select a username
, and attach the login name you've just created, and select the roles you want to apply to this user (i.e.
CREATE USER [myDefaultUser] FOR LOGIN [test]
EXEC sp_addrolemember N'db_datareader', N'myDefaultUser'
EXEC sp_addrolemember N'db_datawriter', N'myDefaultUser'
That is it. Now you can create your connection string using this password.Ref:stackoverflow.com