SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Find all the inactive Windows Logins


Find all the inactive Windows Logins

Author
Message
Subrata Bauri
Subrata Bauri
SSC Veteran
SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)

Group: General Forum Members
Points: 227 Visits: 180
Dear All,

How to query in SQL to retrieve all the disabled Windows Users which are available as records(only) in the SQL Server ?

Thank you.
Igor Micev
Igor Micev
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10442 Visits: 5157
Subrata Bauri (1/30/2014)
Dear All,

How to query in SQL to retrieve all the disabled Windows Users which are available as records(only) in the SQL Server ?

Thank you.


Hi, try with the following code:


SET NOCOUNT ON
CREATE TABLE #all_users (db VARCHAR(70), sid VARBINARY(85), stat VARCHAR(50))
EXEC master.sys.sp_msforeachdb
'INSERT INTO #all_users
SELECT ''?'', CONVERT(varbinary(85), sid) ,
CASE WHEN r.role_principal_id IS NULL AND p.major_id IS NULL
THEN ''no_db_permissions'' ELSE ''db_user'' END
FROM [?].sys.database_principals u LEFT JOIN [?].sys.database_permissions p
ON u.principal_id = p.grantee_principal_id
AND p.permission_name <> ''CONNECT''
LEFT JOIN [?].sys.database_role_members r
ON u.principal_id = r.member_principal_id
WHERE u.SID IS NOT NULL AND u.type_desc <> ''DATABASE_ROLE'''
IF EXISTS
(SELECT l.name FROM sys.server_principals l LEFT JOIN sys.server_permissions p
ON l.principal_id = p.grantee_principal_id
AND p.permission_name <> 'CONNECT SQL'
LEFT JOIN sys.server_role_members r
ON l.principal_id = r.member_principal_id
LEFT JOIN #all_users u
ON l.sid= u.sid
WHERE r.role_principal_id IS NULL AND l.type_desc <> 'SERVER_ROLE'
AND p.major_id IS NULL
)
BEGIN
SELECT DISTINCT l.name LoginName, l.type_desc, l.is_disabled,
ISNULL(u.stat + ', but is user in ' + u.db +' DB', 'no_db_users') db_perms,
CASE WHEN p.major_id IS NULL AND r.role_principal_id IS NULL
THEN 'no_srv_permissions' ELSE 'na' END srv_perms
FROM sys.server_principals l LEFT JOIN sys.server_permissions p
ON l.principal_id = p.grantee_principal_id
AND p.permission_name <> 'CONNECT SQL'
LEFT JOIN sys.server_role_members r
ON l.principal_id = r.member_principal_id
LEFT JOIN #all_users u
ON l.sid= u.sid
WHERE l.type_desc <> 'SERVER_ROLE'
AND ((u.db IS NULL AND p.major_id IS NULL
AND r.role_principal_id IS NULL )
OR (u.stat = 'no_db_permissions' AND p.major_id IS NULL
AND r.role_principal_id IS NULL))
ORDER BY 1, 4
END
DROP TABLE #all_users



Regards,
IgorMi

Igor Micev,
My blog: www.igormicev.com
Subrata Bauri
Subrata Bauri
SSC Veteran
SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)

Group: General Forum Members
Points: 227 Visits: 180
Thanks for your reply.
Code executed in the SQL Server 2008 R2 Platform successfully But it does not give any result.
Igor Micev
Igor Micev
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10442 Visits: 5157
Than all users must have some privileges.
You can modify some conditions in the code if you want to find something else.

Regards,
IgorMi

Igor Micev,
My blog: www.igormicev.com
Subrata Bauri
Subrata Bauri
SSC Veteran
SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)

Group: General Forum Members
Points: 227 Visits: 180
Thanks for your reply.

I have few nos. of Windows Login Users in my Server System (OS & SQL Server Level) & One of them is blocked (Account is disabled in OS Level).

The query which you have provided does not work to find out the same (blocked one).
Manoj Bhopale
Manoj Bhopale
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 35
Use this query to find out disabled windows users/groups.

SELECT name from sys.server_principals
where is_disabled='1' and type_desc like '%WINDOWS%'
Subrata Bauri
Subrata Bauri
SSC Veteran
SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)

Group: General Forum Members
Points: 227 Visits: 180
Manoj Bhopale (1/31/2014)
Use this query to find out disabled windows users/groups.

SELECT name from sys.server_principals
where is_disabled='1' and type_desc like '%WINDOWS%'


This will work when users are disabled in SQL Level not OS Login Level.

Actually I want to list out all the inactive users(windows) which are not able to login into the OS & henceforth they are not able to connect the SQL Server but they are still alive in the SQL Server as records.
John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35209 Visits: 16673
That information isn't stored in SQL Server. You'd need to set up a linked server pointing at your AD server, if your network admin will allow it.

John
Subrata Bauri
Subrata Bauri
SSC Veteran
SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)

Group: General Forum Members
Points: 227 Visits: 180
John Mitchell-245523 (1/31/2014)
That information isn't stored in SQL Server. You'd need to set up a linked server pointing at your AD server, if your network admin will allow it.

John


I'm waiting for this kind of information & so parallelly study the above mentioned thing.

One more thing which I want to ask :-

If the users(Windows Logins) are located in a single machine where the SQL Server exits then what should be the procedure.
Kindly reply.
Suresh B.
Suresh B.
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5072 Visits: 5330
Subrata Bauri (1/31/2014)
Actually I want to list out all the inactive users(windows) which are not able to login into the OS & henceforth they are not able to connect the SQL Server but they are still alive in the SQL Server as records.


This is possible. Run this:
sp_validatelogins

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search