mssql create read only.sql

-- =========================================
-- Create a read-only SQL Server login/user for all databases
-- Usage: Set @username and @password as needed
-- =========================================

-- Step 1: Declare variables for username and password
DECLARE @username NVARCHAR(128) = N'USERNAME';
DECLARE @password NVARCHAR(128) = N'PASSWORD';

-- Step 2: Create the login at the server level (if not already created)
IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = @username)
BEGIN
    DECLARE @createLoginSql NVARCHAR(MAX) = N'CREATE LOGIN [' + @username + N'] WITH PASSWORD = N''' + @password + N''';';
    EXEC sp_executesql @createLoginSql;
END

-- Step 3: For each database, create the user and add to db_datareader, or throw if user exists
DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql = @sql + '
USE [' + name + '];
IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ''' + @username + ''')
BEGIN
    THROW 50001, ''User ' + @username + ' already exists in database [' + name + ']'', 1;
END
ELSE
BEGIN
    CREATE USER [' + @username + '] FOR LOGIN [' + @username + '];
    EXEC sp_addrolemember ''db_datareader'', ''' + @username + ''';
END
'
FROM sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb');

EXEC sp_executesql @sql;