|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
User login date
Anyone has detail steps how to setup SQl 2005 to capture the users login
date? If you just want login dates, you can turn on Login Auditing on the Security
page under Server Properties (right clicking on the SQl instance on Management Studio will take you there). Linchi Show quoteHide quote "Alan T" wrote: > Anyone has detail steps how to setup SQl 2005 to capture the users login > date? > > > > If you just want login dates, you can turn on Login Auditing on the Hi,> Security > page under Server Properties (right clicking on the SQl instance on > Management Studio will take you there). > >> Anyone has detail steps how to setup SQl 2005 to capture the users login >> date? My problem was I want to see if any log of the users login details in the past 3 months. If you haven't set anything up to record the login events, you don't have any
log to review. Linchi Show quoteHide quote "Alan T" wrote: > > If you just want login dates, you can turn on Login Auditing on the > > Security > > page under Server Properties (right clicking on the SQl instance on > > Management Studio will take you there). > > > >> Anyone has detail steps how to setup SQl 2005 to capture the users login > >> date? > > Hi, > > My problem was I want to see if any log of the users login details in the > past 3 months. > > >
Show quote
Hide quote
> If you haven't set anything up to record the login events, you don't have We want to see if one of our ex-employee had any remote access to our SQL > any > log to review. > >> > If you just want login dates, you can turn on Login Auditing on the >> > Security >> > page under Server Properties (right clicking on the SQl instance on >> > Management Studio will take you there). >> > >> >> Anyone has detail steps how to setup SQl 2005 to capture the users >> >> login >> >> date? >> >> Hi, >> >> My problem was I want to see if any log of the users login details in the >> past 3 months. Server, so if there is no setup to log the succeed/failed login, then there is no way to see if he had ever log in the SQL Server?
Show quote
Hide quote
>> If you haven't set anything up to record the login events, you don't have SQL Server 2005.>> any >> log to review. >> >>> > If you just want login dates, you can turn on Login Auditing on the >>> > Security >>> > page under Server Properties (right clicking on the SQl instance on >>> > Management Studio will take you there). >>> My problem was I want to see if any log of the users login details in >>> the >>> past 3 months. > > We want to see if one of our ex-employee had any remote access to our SQL > Server, so if there is no setup to log the succeed/failed login, then > there is no way to see if he had ever log in the SQL Server? May be I need to ask few questions: 1) If I recall correctly, we can specify to log the user whenever he login successfuly or failed when you create the user via Management Studio and/or sp_addlogin? 2) If that is the case, can I use SQL to retrieve the details of all or particular user login log? From which tables? 3) As the ex-employee also responsible for the system and database administration, we want to track down if he still has remotely access our company's database, how do we know if he had setup the user login to be logged ? (if we are lucky he had set that up). 4) In the worst case scenario, we want to start from now on to keep track of all users when they login to the database(no matter failed or succeeded) and what operations they have done (SELECT, DELETE and/or UPDATE, ... etc). > SQL Server 2005. Yes, via Management Studio.> May be I need to ask few questions: > 1) If I recall correctly, we can specify to log the user whenever he login > successfuly or failed when you create the user via Management Studio and/or > sp_addlogin? > 2) If that is the case, can I use SQL to retrieve the details of all or Well, if you specify to audit logins via the Server Properties of Management > particular user login log? From which tables? Studio, the audit info is in the errorlog and/or app eventlog. You would have to glean info from there first and dump it to a table for query. > 3) As the ex-employee also responsible for the system and database Again, I think this should be first handled by your network admin, and check > administration, we want to track down if he still has remotely access our > company's database, how do we know if he had setup the user login to be > logged ? (if we are lucky he had set that up). it from a network perspective first (unless you are so unlucky to be also the network admin). > 4) In the worst case scenario, we want to start from now on to keep track of Take a look at the SQL Server audit features. There are a number ways of > all users when they login to the database(no matter failed or succeeded) and > what operations they have done (SELECT, DELETE and/or UPDATE, ... etc). > accomplishing it. You may not want to go to the other extreme and start logging everything because that can be very expensive. Get your network secured first. if he can't get into the network, there is no much he can do to your databases. Linchi Show quoteHide quote "Man T" wrote: > >> If you haven't set anything up to record the login events, you don't have > >> any > >> log to review. > >> > >>> > If you just want login dates, you can turn on Login Auditing on the > >>> > Security > >>> > page under Server Properties (right clicking on the SQl instance on > >>> > Management Studio will take you there). > >>> My problem was I want to see if any log of the users login details in > >>> the > >>> past 3 months. > > > > We want to see if one of our ex-employee had any remote access to our SQL > > Server, so if there is no setup to log the succeed/failed login, then > > there is no way to see if he had ever log in the SQL Server? > > SQL Server 2005. > May be I need to ask few questions: > 1) If I recall correctly, we can specify to log the user whenever he login > successfuly or failed when you create the user via Management Studio and/or > sp_addlogin? > 2) If that is the case, can I use SQL to retrieve the details of all or > particular user login log? From which tables? > 3) As the ex-employee also responsible for the system and database > administration, we want to track down if he still has remotely access our > company's database, how do we know if he had setup the user login to be > logged ? (if we are lucky he had set that up). > 4) In the worst case scenario, we want to start from now on to keep track of > all users when they login to the database(no matter failed or succeeded) and > what operations they have done (SELECT, DELETE and/or UPDATE, ... etc). > > > >> 3) As the ex-employee also responsible for the system and database We are using a commercial product use SQL Server as backend database, want >> administration, we want to track down if he still has remotely access our >> company's database, how do we know if he had setup the user login to be >> logged ? (if we are lucky he had set that up). > > Again, I think this should be first handled by your network admin, and > check > it from a network perspective first (unless you are so unlucky to be also > the > network admin). to make sure to know if that ex-employee had access this software/database in the past month after he left our company. Our network admin is not expert in SQL Server and I, as application developer, may need to write an application to access the SQL Server to fetch the recrods from, may be system tables, to display the list of login users and their log. So by going through the Management Studio I can see if that login user had been set up to be logged no matter as failed or succeeded? > So by going through the Management Studio I can see if that login user had Yes, by right click on the SQL instance -> Properoties -> Security, and > been set up to be logged no matter as failed or succeeded? you'll see in the middle of the right pane options for Login auditing. Now, this is a server wide setting not just for a particular login. If you have not done anything, the choice is probably None, and you won't have a record of all the login activities. If your ex-employee had elevated privileges and intended to come into the system after he left, he probably would have created some other accounts for himself to use or got passwords for some existing accounts. In that case, if he could get into your network, there is little you could do on the SQL server side without some really painful security change. Linchi Show quoteHide quote "Alan T" wrote: > >> 3) As the ex-employee also responsible for the system and database > >> administration, we want to track down if he still has remotely access our > >> company's database, how do we know if he had setup the user login to be > >> logged ? (if we are lucky he had set that up). > > > > Again, I think this should be first handled by your network admin, and > > check > > it from a network perspective first (unless you are so unlucky to be also > > the > > network admin). > > We are using a commercial product use SQL Server as backend database, want > to make sure to know if that ex-employee had access this software/database > in the past month after he left our company. > Our network admin is not expert in SQL Server and I, as application > developer, may need to write an application to access the SQL Server to > fetch the recrods from, may be system tables, to display the list of login > users and their log. > So by going through the Management Studio I can see if that login user had > been set up to be logged no matter as failed or succeeded? > > > > > > > If your SQL instance is not internet facing, hopefully it has been set up
such that one can only access it if one is already in the domain somewhere or internal network somewhere. In that case, you can check if this ex-employee has accessed your network, and that would be a network admin job. Linchi Show quoteHide quote "Alan T" wrote: > > If you haven't set anything up to record the login events, you don't have > > any > > log to review. > > > >> > If you just want login dates, you can turn on Login Auditing on the > >> > Security > >> > page under Server Properties (right clicking on the SQl instance on > >> > Management Studio will take you there). > >> > > >> >> Anyone has detail steps how to setup SQl 2005 to capture the users > >> >> login > >> >> date? > >> > >> Hi, > >> > >> My problem was I want to see if any log of the users login details in the > >> past 3 months. > > We want to see if one of our ex-employee had any remote access to our SQL > Server, so if there is no setup to log the succeed/failed login, then there > is no way to see if he had ever log in the SQL Server? > > > > If your SQL instance is not internet facing, hopefully it has been set up Hi,> such that one can only access it if one is already in the domain somewhere > or > internal network somewhere. In that case, you can check if this > ex-employee > has accessed your network, and that would be a network admin job. The ex-employee can access our network/database server via SSH, VPN. So we can inspect Windows event log to track if he had access our system? In addition, we have disable his account now.  
Other interesting topics
|
|||||||||||||||||||||||
