Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Find all the inactive Windows Logins Expand / Collapse
Author
Message
Posted Thursday, January 30, 2014 4:20 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 5:28 AM
Points: 67, 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.
Post #1536244
Posted Thursday, January 30, 2014 7:43 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:51 AM
Points: 3,075, Visits: 3,189
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,
SQL Server developer at Seavus
www.seavus.com
Post #1536350
Posted Thursday, January 30, 2014 9:54 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 5:28 AM
Points: 67, Visits: 180
Thanks for your reply.
Code executed in the SQL Server 2008 R2 Platform successfully But it does not give any result.
Post #1536628
Posted Friday, January 31, 2014 1:56 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:51 AM
Points: 3,075, Visits: 3,189
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,
SQL Server developer at Seavus
www.seavus.com
Post #1536667
Posted Friday, January 31, 2014 3:09 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 5:28 AM
Points: 67, 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).
Post #1536684
Posted Friday, January 31, 2014 4:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 3:33 AM
Points: 3, Visits: 31
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%'
Post #1536703
Posted Friday, January 31, 2014 4:40 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 5:28 AM
Points: 67, 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.
Post #1536710
Posted Friday, January 31, 2014 4:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 31, 2014 11:06 AM
Points: 5,430, Visits: 10,108
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
Post #1536714
Posted Friday, January 31, 2014 5:03 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 5:28 AM
Points: 67, 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.
Post #1536720
Posted Friday, January 31, 2014 5:09 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 2:53 AM
Points: 1,101, Visits: 5,290
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
Post #1536724
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse