Database scripts epiCentre 2.2 sql

Version	Table structure	Data	SQL login creation	Upgrade table structure from previous version	Update data from previous version
	Creating for blank database			Updating from previous versions
2.2.x			epiCentre Azure Create User generic.sql epiCentre Azure Create Logins generic.sql
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;