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";
2. In
Security-logins
, right click and select
"New Login";, select
SQL Authentication, use the
username and password
you like.
USE [master]
GO
CREATE LOGIN [ test] WITH PASSWORD=N'test', DEFAULT_DATABASE=[MY_DATABASE], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
3. On the DB you want, in
security,
users, 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.
db_datareader, db_datawriter
):
USE [MY_DATABASE]
GO
CREATE USER [myDefaultUser] FOR LOGIN [test]
GO
USE [MY_DATABASE]
GO
EXEC sp_addrolemember N'db_datareader', N'myDefaultUser'
GO
USE [MY_DATABASE]
GO
EXEC sp_addrolemember N'db_datawriter', N'myDefaultUser'
GO
That is it. Now you can create your connection string using this password.
Ref:stackoverflow.com