May 4, 2007 at 8:34 am
My company has many consultants. They come and leave. They ask us to create accounts for them when they come. But they are quiet when they leave. Many accounts become 'orphans'. The accounts can be either NT accounts or SQL accounts.
We would like to find the accounts, which have not been used for 6 months. What is the best approach to do it?
Any input will be greatly appreciated.
May 4, 2007 at 9:01 am
What version of sql are you on ...
Mike
May 4, 2007 at 9:02 am
The only way I know of is to audit logins and then delete or disable those that have not logged in within xx days.
May 5, 2007 at 10:05 am
in 2005 you can make a DDL trigger to catch a login event to do this. Just update a table with the login name and current time, and after a few weeks you'll have your list.
---------------------------------------
elsasoft.org
May 5, 2007 at 5:35 pm
In 2005, a DDL trigger might be the best way. As jezemine mentioned, log to a table, then set up a job that runs and joins this to logins. Anything not appearing in 180 days gets deleted.
To do this, I assume you know, but I'll mention it for others. Join to logins, use the max audit date. If this is < datediff( getdate(), dateadd( d, -180, getdate()), then delete.
May 6, 2007 at 10:44 am
also I ran across this blog post today that might be of use:
http://weblogs.sqlteam.com/phils/archive/2007/04/30/60193.aspx
it will find logins that aren't mapped to any users.
---------------------------------------
elsasoft.org
May 6, 2007 at 3:09 pm
What about SQL Server 2005 Logon Triggers!!!
http://www.sqlservercentral.com/columnists/FVandeputte/sqlserver2005logontriggers.asp
MohammedU
Microsoft SQL Server MVP
May 6, 2007 at 7:57 pm
Mohammed, that article doesn't describe logon triggers, rather logon events that are processed (asynchronously) by service broker.
logon triggers are different in that they are synchronous. described here: http://msdn2.microsoft.com/en-us/library/bb326598.aspx. This is what I meant by a DDL trigger but apparently BOL distinguishes between a DDL trigger and a logon trigger. The one would be used to fire on ALTER or CREATE LOGIN, the other when a login actually connects.
Either the logon trigger or the service broker approach would work for OP's purpose. Perhaps SB is better since it's asynchronous so more lightweight as far as lag time during login.
---------------------------------------
elsasoft.org
May 6, 2007 at 9:36 pm
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/gems-top-10.mspx
Triggers for Logon Events (New in Service Pack 2)
| • | With SP2, triggers can now fire on Logon events as well as DML or DDL events. |
| • | Logon triggers can help complement auditing and compliance. For example, logon events can be used for enforcing rules on connections (for example limiting connection through a specific username or limiting connections through a username to a specific time periods) or simply for tracking and recording general connection activity. Just like in any trigger, ROLLBACK cancels the operation that is in execution. In the case of logon event that means canceling the connection establishment. Logon events do not fire when the server is started in the minimal configuration mode or when a connection is established through dedicated admin connection (DAC). |
| • | The following code snippet provides an example of a logon trigger that records the information about the client connection. CREATE TRIGGER connection_limit_trigger ON ALL SERVER FOR LOGON AS BEGIN INSERT INTO logon_info_tbl SELECT EVENTDATA() END; |
You can find more information about this feature in updated Books Online for SQL Server Services Pack 2 un the heading “Logon Triggers”.
MohammedU
Microsoft SQL Server MVP
May 11, 2007 at 11:48 am
We don't allow access to SQL Server by specific user id's, only thorugh group membership. We also do not use SQL logins unless required by an application. If you use groups for access, you can use tools to review the members of the groups, which will show the last login. In active dorectory, contractor ID's are set to expire at the end of th econtract or after one year, whichever is shorter.
May 14, 2007 at 12:14 am
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply