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.
