-1

So, here is the scenario. I need to give user access on multiple databases ( but not all on the instance), and in this case, i cannot use AD group which is already having certain grants on all databases on the instance. And I don't want to add the individual user under security and vice versa under each DB. ANY WAY OUT.

Improve this question
3
  • 2
    If the user already has access via an AD group have you considered running explicit DENY CONNECT TO DBName TO Login to prevent the individual login from being able to access certain databases? – Nic Jan 2 '18 at 18:49
  • That's a possibility – JayM Jan 2 '18 at 22:03
  • 1
    why not creating the user Under each database where he need access ? If it's just because there is a lot of DB and you don't want to do it manually, it could be scripted pretty easily. – Dominique Boucher Jan 3 '18 at 14:55
1

Have you thought of Database Containment. Making the database partial containment would make the database standalone from the instance. You can provide access to these databases then and users will connect directly to the database and not to instance first.Contained database users with passwords are authenticated by the database. Also metadata of the database that used to be stored in the master database now will be in the contained database. For more info on this, read this white page: https://docs.microsoft.com/en-us/sql/relational-databases/databases/contained-databases

HTH

Improve this answer
1
  • This is definitely interesting and considering. Thanks samosql. And appreciate all others with helpful tips. – JayM Jan 7 '18 at 0:35

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.