SQL Azure User Permissions

 New Demand to find out the user permissions in Azure SQL Database. 

SELECT    roles.principal_id                            AS RolePrincipalID

  ,    roles.name                                    AS RolePrincipalName

  ,    database_role_members.member_principal_id    AS MemberPrincipalID

  ,    members.name                                AS MemberPrincipalName

FROM sys.database_role_members AS database_role_members  

JOIN sys.database_principals AS roles  

  ON database_role_members.role_principal_id = roles.principal_id  

JOIN sys.database_principals AS members  

  ON database_role_members.member_principal_id = members.principal_id;  

GO

Reference-> SQLShack

https://www.sqlshack.com/database-level-roles-in-azure-sql-database/#:~:text=Expand%20the%20Azure%20SQL%20DB,%3E%20Roles%20%2D%3E%20Database%20Roles.

Find the Jobs that failed

 Well need to send out notifications for the job failures in the SQL Server. 

Well first of all we need to look into the jobhistory to get the last completed instance of the job run completed in last 24hours. 

SELECT j.name,MAX(h.instance_id) instance_id 

FROM msdb.dbo.sysjobhistory h

INNER JOIN msdb.dbo.sysjobs j ON j.job_id=h.job_id

WHERE j.enabled=1

AND msdb.dbo.agent_datetime(h.run_date,h.run_time)> GETDATE()-1

AND h.step_id=0

GROUP BY j.name

Now that we have the instance id of the jobhistory per job that we are interested in, we can now filter the other columns that required for the reporting. 

SELECT j.name,h.message,msdb.dbo.agent_datetime(h.run_date,h.run_time) AS starttime FROM msdb.dbo.sysjobhistory h

INNER JOIN msdb.dbo.sysjobs j ON j.job_id=h.job_id

INNER JOIN 

(

SELECT j.name,MAX(h.instance_id) instance_id 

FROM msdb.dbo.sysjobhistory h

INNER JOIN msdb.dbo.sysjobs j ON j.job_id=h.job_id

WHERE j.enabled=1

AND msdb.dbo.agent_datetime(h.run_date,h.run_time)> GETDATE()-1

AND h.step_id=0

GROUP BY j.name) b ON b.name = j.name AND b.instance_id = h.instance_id

WHERE j.enabled=1

AND h.run_status =0

Hope you enjoyed the query. 

Thanks,

Tushar