Least Privileged Account Creation for AI SQL Tuner

Secure your SQL Server by running AI SQL Tuner under a dedicated least privileged account. This page explains why the principle of least privilege matters and provides a ready-to-run script to create and verify the correct permissions across all user databases.

Why use a least privileged account?

• Reduce risk: Limit the blast radius of accidental or malicious changes.
• Protect data: Restrict visibility to what AI SQL Tuner actually needs.
• Meet compliance: Align with security best practices and audit requirements.
• Enable tuning safely: Provide read-only access to performance and metadata needed for analysis.

Required permissions for AI SQL Tuner

• VIEW SERVER STATE: Read server-level performance data and plan cache.
• VIEW DATABASE STATE: Inspect database health and execution info.
• VIEW DEFINITION: Access object metadata for tuning without modification rights.

Setup overview

1. Create the server-level login with a strong password (run once as sysadmin).
2. Map the login to users in all user databases and grant least-privilege rights.
3. Verify permissions at server and database levels for auditability.

SQL script: Least privilege security setup

Copy, review, and run the following script in SQL Server Management Studio (SSMS). Update the password before executing.

-- ============================================================================
-- AI SQL Tuner - Least Privilege Security Setup
-- Grants access to all user databases on the server
-- Uses cursor to loop through databases and execute grants
-- ============================================================================

-- Step 1: Create server-level login (run once as sysadmin)
USE master;
GO

-- Drop login if exists (for testing/re-running)
IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'AISQLTuner')
    DROP LOGIN [AISQLTunerUser];
GO

CREATE LOGIN [AISQLTunerUser] WITH PASSWORD = 'YourStrongPassword!';
GO

-- Grant server-level permissions (required for Extended Events and plan cache)
GRANT VIEW SERVER STATE TO [AISQLTunerUser];
GO

-- Step 2: Create database users and grant permissions in ALL user databases
-- This script uses a cursor to loop through each database

DECLARE @dbname NVARCHAR(128);
DECLARE @sql NVARCHAR(MAX);

-- Declare cursor for all user databases
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE database_id > 4  -- Exclude system databases (master, tempdb, model, msdb)
  AND state_desc = 'ONLINE'
  AND is_read_only = 0
ORDER BY name;

-- Open cursor and fetch first database
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @dbname;

-- Loop through all databases
WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        PRINT 'Processing database: ' + @dbname;
        
        -- Build dynamic SQL for current database
        SET @sql = '
        USE [' + @dbname + '];
        
        -- Create user if not exists
        IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ''AISQLTunerUser'')
        BEGIN
            CREATE USER [AISQLTunerUser] FOR LOGIN [AISQLTunerUser];
            PRINT ''  + Created user AISQLTunerUser'';
        END
        ELSE
        BEGIN
            PRINT ''  - User AISQLTunerUser already exists'';
        END
        
        -- Grant VIEW DATABASE STATE
        GRANT VIEW DATABASE STATE TO [AISQLTunerUser];
        PRINT ''  + Granted VIEW DATABASE STATE'';
        
        -- Grant VIEW DEFINITION
        GRANT VIEW DEFINITION TO [AISQLTunerUser];
        PRINT ''  + Granted VIEW DEFINITION'';
        ';
        
        -- Execute the dynamic SQL
        EXEC sp_executesql @sql;
        
        PRINT '  Successfully configured database: ' + @dbname;
        PRINT '';
    END TRY
    BEGIN CATCH
        PRINT '  ! Error processing database: ' + @dbname;
        PRINT '    Error: ' + ERROR_MESSAGE();
        PRINT '';
    END CATCH
    
    -- Fetch next database
    FETCH NEXT FROM db_cursor INTO @dbname;
END

-- Clean up cursor
CLOSE db_cursor;
DEALLOCATE db_cursor;

PRINT '============================================================================';
PRINT 'Setup Complete!';
PRINT '============================================================================';
GO

-- Step 3: Verify permissions across all databases
PRINT '';
PRINT '============================================================================';
PRINT 'Verification Report';
PRINT '============================================================================';
PRINT '';

-- Verify server-level permissions
PRINT 'Server-Level Permissions:';
SELECT 
    sp.name AS LoginName,
    spe.permission_name,
    spe.state_desc
FROM sys.server_principals sp
LEFT JOIN sys.server_permissions spe ON sp.principal_id = spe.grantee_principal_id
WHERE sp.name = 'AISQLTunerUser'
ORDER BY spe.permission_name;

PRINT '';
PRINT 'Database-Level Permissions:';

-- Verify database-level permissions using cursor
DECLARE @verify_sql NVARCHAR(MAX);
DECLARE @verify_dbname NVARCHAR(128);

DECLARE verify_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE database_id > 4
  AND state_desc = 'ONLINE'
ORDER BY name;

OPEN verify_cursor;
FETCH NEXT FROM verify_cursor INTO @verify_dbname;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Database: ' + @verify_dbname;
    
    SET @verify_sql = '
    USE [' + @verify_dbname + '];
    SELECT 
        ''' + @verify_dbname + ''' AS DatabaseName,
        dp.name AS UserName,
        dp.type_desc AS UserType,
        ISNULL(dpe.permission_name, ''(no explicit permissions)'') AS Permission,
        ISNULL(dpe.state_desc, ''N/A'') AS State
    FROM sys.database_principals dp
    LEFT JOIN sys.database_permissions dpe ON dp.principal_id = dpe.grantee_principal_id
    WHERE dp.name = ''AISQLTunerUser'';
    ';
    
    EXEC sp_executesql @verify_sql;
    PRINT '';
    
    FETCH NEXT FROM verify_cursor INTO @verify_dbname;
END

CLOSE verify_cursor;
DEALLOCATE verify_cursor;

PRINT '============================================================================';
GO

Best practices

• Use a strong password: Replace the placeholder with a complex, unique password.
• Limit sysadmin use: Run the setup as sysadmin only for initial provisioning.
• Audit regularly: Use the verification section to confirm permissions over time.
• Avoid unnecessary roles: Do not grant db_owner or elevated rights.

Next steps

• Run the script in SSMS and verify output.
• Configure AI SQL Tuner to use the AISQLTunerUser login.
• Document your setup for audit and operations.