Product:
Microsoft SQL server 2016
Issue:
How do i add a AD group to all databases on the SQL server as read only access?
Solution:
Adjust below script – replace AD\DBAteam with your domain\group and run it in SQL query.
Use master
GO
DECLARE @dbname VARCHAR(50)
DECLARE @statement NVARCHAR(max)
DECLARE db_cursor CURSOR
LOCAL FAST_FORWARD
FOR
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb','distribution','SSISDB')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @statement = 'use '+@dbname +';'+ ' CREATE USER [AD\DBATeam]
FOR LOGIN [AD\DBATeam] ; EXEC sp_addrolemember N''db_datareader'',
[AB\DBATeam] '
exec sp_executesql @statement
FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor
The script will work until it reaches a database that is offline – there it will stop and not process any databases below that.
The users login must first be in the database;
CREATE LOGIN [AD\DBATeam] FROM WINDOWS
More information:
https://www.mssqltips.com/sqlservertip/3541/grant-user-access-to-all-sql-server-databases/
https://www.guru99.com/sql-server-create-user.html