Grant Permission on multiple Database in MSSQL

To grant permissions to a login for multiple databases in Microsoft SQL Server (MSSQL), you can use a loop to iterate through each database and grant the necessary permissions. Here's a general outline of how you can achieve this:

1. Connect to your MSSQL instance using SQL Server Management Studio (SSMS) or any other SQL client tool.

2. Write a script to iterate through each database and grant the desired permissions to the login.

3. Execute the script to apply the permissions.

Here's an example script:

DECLARE @DatabaseName NVARCHAR(128)

DECLARE @SQL NVARCHAR(MAX)

DECLARE db_cursor CURSOR FOR

SELECT name

FROM sys.databases

WHERE state_desc = 'ONLINE'  -- Optionally, you can filter databases based on their state

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @DatabaseName

WHILE @@FETCH_STATUS = 0

BEGIN

    SET @SQL = 'USE ' + QUOTENAME(@DatabaseName) + ';

                GRANT SELECT, INSERT, UPDATE, DELETE TO YourLogin;'

                

    EXEC sp_executesql @SQL

    

    FETCH NEXT FROM db_cursor INTO @DatabaseName

END

CLOSE db_cursor

DEALLOCATE db_cursor;

Replace YourLogin with the name of the login to which you want to grant permissions. This script will grant SELECT, INSERT, UPDATE, and DELETE permissions to the specified login on all databases that are currently online. You can modify the script to grant different permissions or filter databases based on your requirements. 

Before using the above script, test it first.