Find all the inactive Windows Logins

  • 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.

  • 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

  • Thanks for your reply.

    Code executed in the SQL Server 2008 R2 Platform successfully But it does not give any result.

  • 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

  • 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).

  • 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%'

  • 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.

  • 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

  • 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.

  • 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

  • Suresh B. (1/31/2014)


    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

    Good shout - I wasn't aware of that stored procedure. I don't know how it handles logins that have been disabled in AD, though.

    As far as I know, AD doesn't run on a SQL Server database. Here's a link on how to create a linked server to your AD server.

    John

  • Suresh B. (1/31/2014)


    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

    Thanks for your reply.

    I have a user whose Login account is disabled in OS Level & the user is still valid user in SQL Server Level but the sp_validatelogins procedure is unable to find this user.

  • Both of you are right.

    sp_validatelogins doesn't list the logins that have been DISABLED in AD.

    It only lists the loigns that are DELETED in AD.

  • One more thing which I want to ask again:-

    If the users(Windows Logins) are located in a single machine where the SQL Server exits then what should be the procedure.

    Kindly reply.

  • So local Windows account instead of domain accounts? Exactly the same principle, except that the linked server process won't work. You'll have to search to find out whether it's possible to link to the local users and groups directory.

    John

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply