— Create a temporary table to hold the xp_logininfo result
CREATE TABLE #LoginInfo (
AccountName NVARCHAR(512),
Type NVARCHAR(50),
Privilege NVARCHAR(50),
MappedLoginName NVARCHAR(512),
PermissionPath NVARCHAR(512)
);
— Declare variables to store the dynamic SQL and the username (from the larger query)
DECLARE @sql NVARCHAR(MAX);
DECLARE @username NVARCHAR(512);
— Get the username (db owner) from the sys.databases table
— This is the same query as before but just fetching the owner name
SELECT @username = suser_sname(db.owner_sid)
FROM sys.databases db
WHERE db.name=”YourDatabaseName”; — Specify a database name if needed, or remove the WHERE clause if for all DBs
— Construct the dynamic SQL to execute xp_logininfo for the fetched username
SET @sql = N’EXEC xp_logininfo @acctname=””‘ + @username + ”’, @option = ”all”’;
— Execute the dynamic SQL and insert the result into the temporary table
INSERT INTO #LoginInfo
EXEC sp_executesql @sql;
— Now, modify the larger query to check if admin privileges exist in the temporary table
SELECT @@SERVICENAME AS InstanceName,
db.name AS DatabaseName,
CASE
WHEN db.is_trustworthy_on = 1 THEN 'Yes'
WHEN db.is_trustworthy_on = 0 THEN 'No'
WHEN db.is_trustworthy_on IS NULL THEN 'NULL'
ELSE CAST(db.is_trustworthy_on AS VARCHAR)
END AS 'is_trustworthy_on',
suser_sname(db.owner_sid) AS db_owner,
-- Check if the admin privilege exists in the temp table
CASE
WHEN EXISTS (
SELECT 1 FROM #LoginInfo
WHERE Privilege="admin"
) THEN 'Yes'
ELSE 'No'
END AS 'is_sysadmin',
CASE
WHEN s1.is_disabled = 1 THEN 'Yes'
WHEN s1.is_disabled = 0 THEN 'No'
WHEN s1.is_disabled IS NULL THEN 'NULL'
ELSE CAST(s1.is_disabled AS VARCHAR)
END AS 'IsLoginDisabled',
CASE
WHEN s1.name IS NULL THEN 'Yes'
ELSE 'No'
END AS IsOrphaned,
CASE
WHEN dbm.mirroring_role = 1 THEN 'Mirroring - Primary'
WHEN dbm.mirroring_role = 2 THEN 'Mirroring - Secondary'
WHEN sys.fn_hadr_is_primary_replica(db.name) = 1 THEN 'AG - Primary'
WHEN sys.fn_hadr_is_primary_replica(db.name) = 0 THEN 'AG - Secondary'
ELSE 'None'
END AS 'DR_Role'
FROM sys.databases db
LEFT JOIN sys.sql_logins sl ON db.owner_sid = sl.sid
LEFT JOIN sys.database_mirroring dbm ON db.database_id = dbm.database_id
LEFT JOIN sys.server_principals s1 ON db.owner_sid = s1.sid;
— Drop the temporary table after use
DROP TABLE #LoginInfo;
You need to sign in to view this answers
