Hi Team,
I got requirement to compare the login with role of two instance. i have used below query run in one instance and load the data into staging data base . and export that table to another instance and run that query another instance compare both result sets.
CREATE TABLE #LoginPermissions
(
ID INteger IDENTITY(1,1),
DBname VARCHAR(100),
LoginName VARCHAR(1000),
DatabaseUser VARCHAR(1000),
RoleName VARCHAR(1000)
)
EXEC sp_MSforeachdb '
USE [?];
INSERT INTO #LoginPermissions (DBname,LoginName,DatabaseUser,RoleName)
SELECT
''?'' as Data_Base_name,
sp.name AS LoginName,
dp.name AS DatabaseUser,
dr.name AS RoleName
FROM sys.server_principals sp
JOIN sys.database_principals dp
ON sp.sid = dp.sid
JOIN sys.database_role_members drm
ON dp.principal_id = drm.member_principal_id
JOIN sys.database_principals dr
ON drm.role_principal_id = dr.principal_id
ORDER BY sp.name, dr.name;
'
SELECT sLP.LoginName,sLP.DatabaseUser,sLP.DBname , sLP.RoleName
,LP.LoginName,LP.DatabaseUser,LP.DBname , LP.RoleName
FROM staging.dbo.Loginslist sLP
LEFT JOIN #LoginPermissions LP
ON LP.DBname = sLP.DBname
AND LP.LoginName = sLP.LoginName
AND LP.DatabaseUser = sLP.DatabaseUser
AND LP.RoleName = sLP.RoleName
ORDER BY sLP.LoginName,sLP.DatabaseUser,sLP.DBname , sLP.RoleName
SELECT LP.LoginName,LP.DatabaseUser,LP.DBname , LP.RoleName,
sLP.LoginName,sLP.DatabaseUser,sLP.DBname , sLP.RoleName
FROM #LoginPermissions LP
LEFT JOIN staging.dbo.Loginslist sLP
ON LP.DBname = sLP.DBname
AND LP.LoginName = sLP.LoginName
AND LP.DatabaseUser = sLP.DatabaseUser
AND LP.RoleName = sLP.RoleName
ORDER BY LP.LoginName,LP.DatabaseUser,LP.DBname , LP.RoleName
--SELECT * FROM staging.dbo.Loginslist
