1

In our environment, SQL authentication is the norm, but Windows auth is possible when users are using RDP. As a result, each physical user has two logical logins for each instance and two logical users for each database on each instance.

Is there any way to link two logins, so I can (e.g.) grant privs to both simultaneously?

This is a small thing, but seeing repetition like this triggers my "there's got to be a better way" sense:

CREATE LOGIN Bob
CREATE LOGIN Domain\Bob
ALTER SERVER ROLE serveradmin ADD MEMBER Bob
ALTER SERVER ROLE serveradmin ADD MEMBER Domain\Bob
USE Db;  CREATE USER Bob FOR LOGIN Bob
USE Db;  CREATE USER Domain\Bob FOR LOGIN Domain\Bob

Am I overlooking a feature that would reduce this duplication?

We'll eventually replace domain logins with AD groups, which will help.

1

I would suggest moving all clients to the domain and removing the SQL logins as the AD route is much more secure. If this is not an option then I would probably remove the AD logins and have RDP users use SQL logins as having two logins per user doesn't make much sense.

If for some reason you decide you have to go the two login route I would write a stored procedure to replicate the settings from AD users to your SQL logins. This could be triggered with a DDL trigger. This way you would only have to update one set of users.

I would also be careful in using AD groups if you are not the only person responsible for the administration of the domain. It's not clear from the database who has the permissions specified for the group as users be dropped in and out of the AD group without your knowledge.

| improve this answer | |
  • I understand that the division between Prod and Dev domains is obliged for some legal reason; there's financial data in Prod which has to be firewalled. It's moot when people can log in directly with SQL Auth anyway, but try telling that to lawyers. Using triggers to keep them in synch is an excellent idea, I will propose that if there's no better solution offered. Also, a good point re: AD and privs; the IT desk could make someone an admin on a prod server without us DBAs even being aware. – Jon of All Trades Nov 10 '14 at 16:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.