Sql-server – Getting User Access in Individual Databases MSSQL

access-controlsql serverusers

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.

Best Answer

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