eG Self-Service Portal

SQL user need to have the below roles

• CONNECT SQL
  
• VIEW ANY DEFINITION
  
• VIEW SERVER STATE
  
• DB_DATAREADER role for all databases (SQL Database Space test).
  
• SELECT ON MSDB.DBO.SYSJOBHISTORY (SQL Job Details test)
  
• SELECT ON MSDB.DBO.SYSJOBS (SQL Job Status test)
  

Queries to grant the privileges 1, 2, 3:

USE [master]

GO

CREATE LOGIN [<eg_user>] WITH PASSWORD=N'<eg_user_password>', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

use [master]

GO

GRANT CONNECT SQL TO [<eg_user>]

GO

use [master]

GO

GRANT VIEW ANY DEFINITION TO [<eg_user>]

GO

use [master]

GO

GRANT VIEW SERVER STATE TO [<eg_user>]

GO

Queries to grant the privileges 4:

USE master

GO

DECLARE @LoginName varchar(256)

SET @LoginName ='<eg_user>'

SELECT 'USE [' + Name + ']'

+ ';'

+ 'CREATE USER [' + @LoginName + '] FOR LOGIN [' + @LoginName + '] '

+ ';'

+ 'EXEC sp_addrolemember ''db_datareader'', '''+ @LoginName + ''''

AS ScriptToExecute

FROM sys.databases

WHERE (state_desc ='ONLINE')

AND (source_database_id Is Null)

ORDER BY Name

Queries to grant the privileges 5, 6:

USE [msdb]

GO

CREATE USER [<eGUser>] FOR LOGIN [<eGUser>]

GO

GRANT SELECT ON [dbo].[sysjobhistory] TO [<eGUser>]

GO

GRANT SELECT ON [dbo].[sysjobs] TO [<eGUser>]

GO