Grant User Access to All SQL Server Databases at a time

Problem :

Need to grant user access to all SQL Server databases where  large number of databases exist

Solution :

Use a simple cursor with the LOCAL and FAST_FORWARD options to optimize its performance. 

LOCAL - Specifies that the cursor can be available only in the batch in which the cursor was created, and will be de-allocated once the batch terminates.

FAST_FORWARD - Specifies that the cursor produces the least amount of overhead on SQL Server by choosing the lowest cost static or dynamic plan.

STEP 1: Create the Login

USE [master]

GO
CREATE LOGIN [DemoUser]

      WITH PASSWORD = N'DemoUser@123'

            ,DEFAULT_DATABASE = [master]

            ,CHECK_EXPIRATION = OFF

            ,CHECK_POLICY = ON

GO

STEP 2 - Get a list of all user databases in SQL Server instance, excluding the system databases (master, model, msdb, tempdb and distribution) from the sysdatabases table.

STEP 3 - Once the databases list is ready, loop through thes database to create a user for that login created in STEP 1 and grant it read and write access on each database.

USE master
GO
DECLARE @dbname VARCHAR(50)
DECLARE @statement NVARCHAR(max)
DECLARE db_cursor CURSOR LOCAL FAST_FORWARD
FOR
SELECT NAME FROM MASTER.dbo.sysdatabases
WHERE NAME NOT IN ('master','model','msdb','tempdb','distribution')
OPEN db_cursor
FETCH NEXT
FROM db_cursor
INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
      SELECT @statement = 'use ' + @dbname + ';' + 'CREATE USER [DemoUser] 
FOR LOGIN [DemoUser]; EXEC sp_addrolemember N''db_datareader'', 
[TipsDemoUser];EXEC sp_addrolemember N''db_datawriter'', [TipsDemoUser]'
      EXEC sp_executesql @statement
      FETCH NEXT
      FROM db_cursor
      INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor

STEP 4 - Execute the scripts that was generated to create the user and grant permissions in new query window.

Now you can cross check the access for 1 or 2 databases.