T-SQL QUERY to Grant Access to Database

How do I build a t-sql query to grant access to users on multiple databases in multiple instances?

eg:

• Instance 1 - Database A

• Instance 2 - Database B

How do I grant access to both databases to a single windows user?

My query should also check the SQL Instance if the database is present in that "Instance" before granting access.

You can use SQLCMD mode in SSMS (or SQLCMD itself) to do this.

The following query would connect to an instance, check for the database in question, create the login if it does not exist, and then connect to the database and add a user for that login.

:CONNECT InstanceName
GO
IF EXISTS (SELECT Name FROM sys.databases WHERE name = 'DBName')
    EXEC sp_executeSQL N'   IF NOT EXISTS (SELECT name FROM sys.server_principals WHERE name = ''LOGIN_NAME'')
        CREATE LOGIN LOGIN_NAME FROM WINDOWS;

    USE DBName;
    IF NOT EXISTS (SELECT name FROM sys.database_principals WHERE name = ''LOGIN_NAME'')
        CREATE USER LOGIN_NAME;'
GO

You can do other kinds of variable substitution to make things a little quicker, it's worth reading up at https://learn.microsoft.com/en-us/sql/relational-databases/scripting/edit-sqlcmd-scripts-with-query-editor on the things that you can do.

StackExchange Database Administrators Q#186038, answer score: 2