question

askmlx121 avatar image
askmlx121 asked

Find Not Frequently used DB users list in SQL server

Hi, I want to find the users who didn't use the SQL Database Frequently atleast 6 or more months. Then I want to disabled that users login immediately. So how can i do this using T-SQL script or SQL MANAGEMENT STUDIO. **1)Find the Not Frequently used users list in SQL server?** **2) How to disabled the users login more than 10 users login at a time?** Any kindly help me
loginusers
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Cyborg avatar image
Cyborg answered
1)Find the Not Frequently used users list in SQL server? I am not sure you can find this unless you have configured any server audit or trace running (may be extended events also). 2)How to disabled the users login more than 10 users login at a time? prepare dynamic sql that has query to disable the required login.

-- Result to text, ctrl+T
SELECT 'ALTER LOGIN '+QUOTENAME(name)+' DISABLE'+CHAR(10)+'GO'
FROM sys.syslogins
WHERE isntUser = 0
and name in ('Login1','Login2')

-- Copy the result paste in new query window then execute.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.