T-SQL script to create login and add user read\write role to each DB

mssql_create_user_on_each_db.sql

/* ## Variables ## */
DECLARE @username VARCHAR(50) = 'db_reader'
DECLARE @password VARCHAR(50) = 'asecuredpassword'
DECLARE @can_read VARCHAR(1) = 'Y'
DECLARE @can_write VARCHAR(1) = 'N'
/* ## Variables ## */


DECLARE @tsql_return INT
DECLARE @tsql_statement NVARCHAR(MAX)
DECLARE @tsql_parm_def NVARCHAR(MAX)
DECLARE @dbname VARCHAR(50)


IF NOT exists(SELECT loginname
              FROM master.dbo.syslogins
              WHERE @username = name)
  BEGIN
    SET @tsql_statement = N'CREATE LOGIN ' + @username + ' with password=''' + @password + ''''
    EXEC sp_executesql @tsql_statement
  END

DECLARE db_cursor CURSOR
LOCAL FAST_FORWARD
FOR
  SELECT name
  FROM MASTER.dbo.sysdatabases
  WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb', 'distribution', 'rdsadmin')
OPEN db_cursor

FETCH NEXT FROM db_cursor
INTO @dbname
WHILE @@FETCH_STATUS = 0
  BEGIN
    PRINT N'Database: ' + @dbname
    
    -- Check if user exists (0 not exist, 1 - exists)
    SET @tsql_statement =
    N'SELECT @returnOUT = count(1) FROM ' + @dbname + '.sys.database_principals WHERE name = ''' + @username + ''''
    SET @tsql_parm_def = N'@returnOUT int OUTPUT'
    EXEC sp_executesql @tsql_statement, @tsql_parm_def, @returnOUT = @tsql_return OUTPUT
    
    -- Create user if not exist
    IF @tsql_return = 0
      BEGIN
        SET @tsql_statement = 'use ' + @dbname + '; ' + 'CREATE USER [' + @username + '] FOR LOGIN [' + @username +
                              ']; '
        PRINT @tsql_statement
        EXEC sp_executesql @tsql_statement
      END

    -- Add basic roles
    SET @tsql_statement = 'use ' + @dbname + '; '

    IF @can_read = 'Y'
      -- Add read role
      SET @tsql_statement = @tsql_statement + 'EXEC sp_addrolemember N''db_datareader'', [' + @username + ']; '
    ELSE
      -- Revoke read role
      SET @tsql_statement = @tsql_statement + 'EXEC sp_droprolemember N''db_datareader'', [' + @username + ']; '

    IF @can_write = 'Y'
      -- Add write role
      SET @tsql_statement = @tsql_statement + 'EXEC sp_addrolemember N''db_datawriter'', [' + @username + ']; '
    ELSE
      -- Revoke write role
      SET @tsql_statement = @tsql_statement + 'EXEC sp_droprolemember N''db_datawriter'', [' + @username + ']; '

    PRINT @tsql_statement
    EXEC sp_executesql @tsql_statement

    PRINT N''
    FETCH NEXT FROM db_cursor
    INTO @dbname
  END
CLOSE db_cursor
DEALLOCATE db_cursor