Logging user logins using MS SQL CDC features

Part of this article is reproduced from: tracking records of each business operation data change the sharp weapon--SQLCDC

Http://www.cnblogs.com/artech/archive/2010/11/20/cdc.html

SharePoint Foundation uses the form user authentication method. Recently, the company has started the evaluation of such insurance. Among them, there are two rectification opinions compare headaches:

1, account use strong password Authentication

2, the user login need to have audit log

1th , the Formuser through the Webconfig setting parameters to achieve the effect, specific can see before I an article

sqlmembership parameter Description http://fishvsfrog.blog.51cto.com/388027/1927484

2nd , headache.

At first there were a few ideas

1, open the IIS log, and then develop a small program for data extraction and analysis.

Requires code development and coordination of development resources. High intermediate costs

2. Turn on the auditing feature of SharePoint

SharePoint can be configured to turn on auditing for lists, document libraries, and WSS needs to be implemented by using the command line with page code. The amount of development is not very large, but there is some mismatch between the requirements of the login audit and the above mentioned, feeling a bit overqualified. Learn the project management should understand that we can not "gilded", otherwise how to grow (pian) business (Qian) Ah

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M02/96/FC/wKioL1knq2fjqe2MAAEBkVtlUzk919.gif "title=" Timg.gif "width=" "height=" "border=" 0 "hspace=" 0 "vspace=" 0 "style=" width:40px;height:43px; "alt=" Wkiol1knq2fjqe2maaebkvtluzk919.gif "/>

One day, on the whim, Formuser is recorded in the ASP. NET membership database. Is it possible to log changes to each user record through SQL stored procedures or triggers?!

Q, the NET Membership data table diagram, the comparison shows that we just need to use aspnet_users and aspnet_membership inside the information is enough.

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M01/96/FB/wKioL1knorqBVSu8AAL10SWjUug691.jpg "title=" 10. JPG "alt=" wkiol1knorqbvsu8aal10swjuug691.jpg "/>

Find information on the Internet, found this in the MS SQL 2005 era is very painful, need to write a lot of triggers, and affect database performance. After 2008, Microsoft turned on the CDC feature. Here's an introduction:

the full name of the CDC isChange Data Capture, as the name implies, is used to track and capture data changes. The CDC is a new feature that appears in SQL Server 2008, and this feature was earlier in Oracle. For previous versions of SQL Server, in the absence of CDC, if you need to record data changes based on a data table, we can only useTrigger, specifically by manually creating after inserts, after update, and after delete triggers to record the changed data. The CDC gives us a more convenient, easy-to-use, and hassle-friendly way to record the history of a data table.

The heart is happy, we use MS SQL Server R2. Do as you say.

The first step, the preparatory work

1. Ensure that the SQL Server version is above 2008

2, the CDC function is dependent on the SQL Agent service, to ensure that the open

Step two, turn on the CDC function of the database

Take the database name TestDB as an example

Use TestDb

Go

Exec sys.sp_cdc_enable_db

Go

Check whether it is turned on, you can use the following statement, 0 is not open, 1 is on:

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M01/96/FC/wKioL1knpAfxawOwAAAk08LVTVQ820.png "title=" 111. PNG "alt=" Wkiol1knpafxawowaaak08lvtvq820.png "/>

The third step, turn on the CDC function of the data table

Use TestDb

Go

Exec sys.sp_cdc_enable_table ' dbo ', ' aspnet_users ', @role_name = NULL

Go

Exec sys.sp_cdc_enable_table ' dbo ', ' aspnet_membership ', @role_name = NULL

Go

Description: The CDC feature of the data table is opened by executing the sys.sp_cdc_enable_table stored procedure implementation. The simplest way to invoke the stored procedure is to specify the schema, name, and permissions (roles) of the data table that must be used to extract the changed data. I opened the CDC attribute of the users table we created by executing the following T-SQL, where the @role_name parameter was set to NULL, indicating that I did not authorize the read ALTER data operation. Sys.sp_cdc_enable_table has many parameters, and the CDC behavior affected by the corresponding parameters can be referenced in the SQL Server 2008 online documentation.

Fourth step, complete, you can find the corresponding CDC data table to see the record

Upon completion

1. In SQL Server Agent, 2 jobs are generated automatically

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M01/96/FC/wKioL1knpj7CKRnsAAA1I9kbOMw758.jpg "title=" 222. JPG "alt=" wkiol1knpj7ckrnsaaa1i9kbomw758.jpg "/>

2, in the CDC database, enter into the system table, you can see the CDC automatically created tracking record table

650) this.width=650; "src=" https://s1.51cto.com/wyfs02/M01/96/FC/wKioL1knpnPxEHUoAAB-XN0i6yk239.jpg "title=" 444. JPG "alt=" wkiol1knpnpxehuoaab-xn0i6yk239.jpg "/>

3, we have to check

Select Userid,[__$operation],[lastlogindate] FROM [Sysystemaccount]. [CDC]. [DBO_ASPNET_MEMBERSHIP_CT]

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M01/96/FB/wKiom1knp3rBgphHAADD8bxYCek650.png "title=" 555. PNG "alt=" wkiom1knp3rbgphhaadd8bxycek650.png "/> Description:

__$operation Field2represents the "Insert "operation, 3 represents the before modifying after modification data

Fifth Step, subsequent

The data table has, the field name of its own output optimization, a full log is completed, embedded in the HTML page, perfect

This article is from the "Fishvsfrog" blog, make sure to keep this source http://fishvsfrog.blog.51cto.com/388027/1929727

Logging user logins using MS SQL CDC features