Set login_mode to Standard for a user

[former_SQLA_member1694913]

I see where you can set the login_mode database option to standard, integrated but that is for the entire database. Is there a way to manage login_mode at the user level? I'd like to prevent certain database users(mainly those with elevated, dba type privileges) from using integrated login, only user id and password. I don't want the users to map the elevated database user account to a windows account to keep them from logging into our application with the elevated user account.

Any experience with this? Or, should I be looking at this a different way?

Appreciate the help.

[VolkerBarth · Accepted solution]

I guess you are using integrated logins for Windows groups, right?

So I guess you have to

• either exclude those users from the according Windows group,

• or add a second integrated login for a different Windows group to a different database user, and put those undesired users in the second group: When an OS user belongs to multiple groups which have integrated login mappings to different database users, they cannot connect via integrated logins, as documented here...

[former_SQLA_member1694913 · Accepted solution]

Volker, thank you. We do not use Windows user groups with integrated login. We map one Windows user account to a db user.

ex. grant integrated login to "domain\\user.name" as user dbuser1;

Problem is we don't have control over the domain and/or active directory.

But, this is interesting and an approach we have not considered.

[VolkerBarth · Accepted solution]

Well, if you do not map Windows groups but map each individual Windows user, why do you map those undesired Windows users to integrated logins? In my understanding, you could simply drop the mapping for those...

[former_SQLA_member1694913 · Accepted solution]

Well, we have personnel at the remotes and they can do things like map accounts.

We could ask them not to do that. That's probably a good place to start.