SQL Server Windows Authentication with different credentials-CodePudding

I have two different userid and passwords for windows:admin and normal-user

I have logged into SSMS as admin and created a database, now I want my .NET application to connect to it:

Trial 1:

packet size=4096;Server=localhost;Trusted_Connection=True;initial catalog=arrbtr_vlimits3

of course did not work

login failed for user [normal-user-id]

Trial 2:

packet size=4096;persist security info=False;Server=[server-name];user id=[admin-id];password=[admin-pwd];initial catalog=arrbtr_vlimits3

login failed for user [admin-user-id]

Note: logging into SSMS as normal user is a problem for some reason

CodePudding user response：

Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;

CodePudding user response：

Since you are using Windows Authentication, you need to set up a login for normal-user-id. Usernames and passwords in the connection string do not work because they are only used for SQL Authentication.

I would advise you ideally to use an AD group, but if not then you can create a login just for that Windows user.

USE master;

CREATE LOGIN [YourDomain\normal-user-id] FROM WINDOWS WITH DEFAULT_DATABASE = arrbtr_vlimits3;

You then need to create a user for that login within the database

USE arrbtr_vlimits3;

CREATE USER [YourDomain\normal-user-id] FROM LOGIN [YourDomain\normal-user-id];

Then give them permissions

ALTER ROLE db_datareader ADD MEMBER [YourDomain\normal-user-id];
ALTER ROLE db_datawriter ADD MEMBER [YourDomain\normal-user-id];