October 3, 2012 at 1:15 am
How to get a list of users or logins, their role, and enabled or not.
SELECT MemPrin.name AS MemberName, RolPrin.name AS RoleName
FROM sys.database_role_members a
INNER JOIN sys.database_principals RolPrin ON RolPrin.principal_id = a.role_principal_id
INNER JOIN sys.database_principals AS MemPrin ON MemPrin.principal_id = a.member_principal_id
group by MemPrin.name, RolPrin.name order by MemPrin.name asc
this query gives logins and users and their roles. How to add enabled/disabled information? I tried SQL_logins, Server_permissions etc, bt could not get required result
This query does not list logins which are not assigned any roles (like data reader, writer etc). I need to list those logins too.
October 3, 2012 at 2:07 am
use the is_disabled flag on sys.server_principles and then join back to this as well for any missing logins which dont have a user in the DB
October 3, 2012 at 3:09 am
Yes i tried that.
SELECT MemPrin.name AS MemberName, RolPrin.name AS RoleName
FROM sys.database_role_members a
INNER JOIN sys.database_principals RolPrin ON RolPrin.principal_id = a.role_principal_id
INNER JOIN sys.database_principals AS MemPrin ON MemPrin.principal_id = a.member_principal_id
INNER JOIN sys.server_principals AS SP ON SP.principal_id = MemPrin.principal_id
--INNER JOIN sys.sql_logins AS SP ON SP.principal_id = a.member_principal_id
and b.IS_DISABLED=1
group by MemPrin.name, RolPrin.name order by MemPrin.name asc
it does not return anything, even when there is an disabled login. It would be helpful if the mistake in the qury is pointed out
October 3, 2012 at 3:16 am
You wont want the is_disabled flag in the join criteria, you would want it in the select, so that you can see if the login is disabled or not
SELECT
ISNULL(DP.Name, SP.Name) AS MemberName,
DP1.Name AS Role,
CASE WHEN SP.Is_Disabled = 1 THEN 'Disabled' ELSE 'Enabled' END AS Status
FROM
sys.server_principals SP
LEFT OUTER JOIN
sys.database_principals DP
ON
SP.principal_id = DP.principal_id
LEFT OUTER JOIN
sys.database_role_members DRM
ON
DP.principal_id = DRM.member_principal_id
LEFT OUTER JOIN
sys.database_principals DP1
ON
DRM.role_principal_id = DP1.principal_id
WHERE
SP.type <> 'R'
October 3, 2012 at 3:51 am
The query returns dbo and datareader roles only, all other are coming as NULLS
October 3, 2012 at 3:52 am
But you wanted to list logins which dont have a role assigned to them, which is why they are null.
This query does not list logins which are not assigned any roles (like data reader, writer etc). I need to list those logins too.
October 3, 2012 at 7:10 am
Anthony,
Yes.. now its clear. Thank you.
October 5, 2012 at 9:15 am
From SQL Server you can query what accounts and groups are members of roles (particilarly SYSADMIN), but it won't tell you what domain accounts are members of a domain group (ex: BUILTIN\Administrators). So, I wrote this script a while back that leverages xp_logininfo to return all SQL Server and domain accounts that would have membership in a sysadmin role.
Query accounts, domain groups, and members who have admin membership.
http://www.sqlservercentral.com/articles/Security/76919/
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply