Database scripts epiCentre 2.2 sql

Version	Table structure	Data	Upgrade table structure from previous version	Update data from previous version
	Creating for blank database		Updating from previous versions
2.2.x
2.1.x	N/A	N/A
2.0.x	N/A	N/A	N/A	N/A

Useful troubleshooting scripts

Check Role Membership

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

Check Logins in azure

SELECT *  FROM sys.sql_logins -- you need to execute this on master azure db
SELECT * FROM sys.database_principals -- you need to execute this on the database of interest

SELECT DP1.name AS DatabaseRoleName,   
   isnull (DP2.name, 'No members') AS DatabaseUserName   
FROM sys.database_role_members AS DRM  
RIGHT OUTER JOIN sys.database_principals AS DP1  
   ON DRM.role_principal_id = DP1.principal_id  
LEFT OUTER JOIN sys.database_principals AS DP2  
   ON DRM.member_principal_id = DP2.principal_id  
WHERE DP1.type = 'R'
ORDER BY DP1.name;