January 30, 2014 at 4:20 am
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.
January 30, 2014 at 7:43 am
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
January 30, 2014 at 9:54 pm
Thanks for your reply.
Code executed in the SQL Server 2008 R2 Platform successfully But it does not give any result.
January 31, 2014 at 1:56 am
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
January 31, 2014 at 3:09 am
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).
January 31, 2014 at 4:17 am
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%'
January 31, 2014 at 4:40 am
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.
January 31, 2014 at 4:53 am
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
January 31, 2014 at 5:03 am
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.
January 31, 2014 at 5:09 am
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
January 31, 2014 at 5:14 am
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
January 31, 2014 at 5:24 am
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.
February 1, 2014 at 1:42 am
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.
February 7, 2014 at 4:31 am
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.
February 7, 2014 at 4:37 am
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