Grant read access on SYSTEM and USER Databases

1.SYSTEM Databases
--Read access(including metadata) on system databases.

USE msdb
go
drop user [ADGroup_Name]
go
USE master
go
drop user [ADGroup_Name]
go
drop login [ADGroup_Name]
go

USE master
go
IF EXISTS(select 1 from sys.server_principals where name='ADGroup_Name')
BEGIN
drop login [ADGroup_Name]
END
go
CREATE LOGIN [ADGroup_Name] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO

GRANT VIEW SERVER STATE to [ADGroup_Name]
GRANT VIEW ANY DEFINITION to [ADGroup_Name]
GRANT VIEW ANY DATABASE to [ADGroup_Name]
go


USE master
go
IF EXISTS(select 1 from sys.database_principals where name='ADGroup_Name')
BEGIN
drop user [ADGroup_Name]
END
go
CREATE USER [ADGroup_Name] FOR LOGIN [ADGroup_Name]
go
EXEC sp_addrolemember db_datareader, [ADGroup_Name]
go
GRANT EXECUTE on xp_readerrorlog to [ADGroup_Name]
GRANT SHOWPLAN TO [ADGroup_Name]
go

--SQL Server Agent
USE msdb
go
IF EXISTS(select 1 from sys.database_principals where name='ADGroup_Name')
BEGIN
drop user [ADGroup_Name]
END
go
CREATE USER [ADGroup_Name] FOR LOGIN [ADGroup_Name]
go
EXEC sp_addrolemember db_datareader, [ADGroup_Name]
go
EXEC sp_addrolemember SQLAgentReaderRole, [ADGroup_Name]
GO
GRANT SHOWPLAN TO [ADGroup_Name]
go

************************************************************************************************

2.USER Databases

--USE
--go
--Remove [ADGroup_NAME]
--drop user [ADGroup_NAME]

IF EXISTS(select 1 from sys.database_principals where name='ADGroup_NAME')
BEGIN
drop user [ADGroup_NAME]
END
go
CREATE USER [ADGroup_NAME] FOR LOGIN [ADGroup_NAME]
go
EXEC sp_addrolemember db_datareader, [ADGroup_NAME]
go
GRANT SHOWPLAN to [ADGroup_NAME]
go