Shulou(Shulou.com)06/01 Report--

Part of this article is reproduced from: SQLCDC, a sharp tool for tracking and recording data changes in each business operation.

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

SharePoint foundation uses form user authentication. Recently, enterprises have begun to wait for insurance assessment. Among them, two suggestions for rectification and reform are relatively troublesome:

1. The account is verified with a robust password.

2. Audit logs are required for users to log in

First, the effect is achieved through the setting parameters of webconfig in formuser. For more information, please see my previous article.

Parameter SqlMembership indicates http://fishvsfrog.blog.51cto.com/388027/1927484

Second, I have a headache.

At first, I had a few ideas.

1. Open the iis log, and then develop a Mini Program for data extraction and analysis.

Need code development, coordinate development resources. The intermediate cost is too high

2. Enable the audit function of SharePoint

SharePoint can enable the audit function for lists and document libraries through configuration, and wss needs to be implemented through the command line and page code. The amount of development is not very large, but it does not match the requirements of login audit mentioned above, which makes it feel a little overqualified. Those who have studied project management should understand that we must not be "gilded". Otherwise, how can we grow (pian) business (qian)?

One day, on a whim, formuser was recorded in ASP.NET membership's database. Can you use sql stored procedures or triggers to record changes to each user record?!

The following figure asks, ASP.NET membership data table diagram, the comparison shows that we only need to use the information in aspnet_users and aspnet_membership.

Looking for information on the Internet, I found that this in the era of Ms sql 2005 is very painful, need to write a lot of triggers, and affect database performance. After 2008, Microsoft launched the CDC function. The brief introduction is as follows:

The full name of CDC is Change Data Capture, which, as its name implies, is used to track and capture data changes. CDC is a new feature that just appeared in SQL Server 2008, and this feature appeared in Oracle a long time ago. For previous versions of SQL Server, in the absence of CDC, if we need to record data changes based on a data table, we can only use triggers, specifically by manually creating After Insert, After Update, and After Delete triggers to record the changed data. CDC gives us a more convenient, easy-to-use and easy-to-use way to record the historical operation of a data table.

I was delighted that we were using MS sql server 2008 R2. Just do it.

The first step, preparatory work

1. Make sure the SQL server version is above 2008.

2. The CDC function depends on the SQL agent service. Make sure to enable it.

The second step is to enable the CDC function of the database

Take the database name testDB as an example

Use TestDbGoExec sys.sp_cdc_enable_dbGo

To check whether it is enabled, you can use the following statement. 0: not enabled; 1: enabled:

The third step is to enable the CDC function of the data table

Use TestDbGoExec sys.sp_cdc_enable_table 'dbo',' aspnet_Users', @ role_name = NULLGoExec sys.sp_cdc_enable_table 'dbo',' aspnet_Membership', @ role_name = NULLGo

Description: the CDC feature of the datasheet is enabled by executing the sys.sp_cdc_enable_table stored procedure. The simplest way to call this stored procedure is to specify the Schema of the data table, the name, and the permissions (roles) that must be used to extract the changed data. I turn on the CDC feature of the Users table we created by executing T-SQL below, where the @ role_name parameter is set to NULL, indicating that I am not authorized to read change data. Sys.sp_cdc_enable_table has many parameters, and as for the CDC behavior affected by the corresponding parameters, please refer to the SQL Server 2008 online documentation.

Step 4, complete, you can find the corresponding CDC data table to see the record.

After completion

1. In the SQL server surrogate, 2 jobs are automatically generated

2. In the database with CDC enabled, enter the system table, and you can see the tracking record table created automatically by CDC.

3. Let's check it out.

Select userid, [_ $operation], [LastLoginDate] from [sysystemaccount]. [CDC]. [DBO _ aspnet_Membership_CT]

Description:

The _ $operation field 2 indicates the insert operation, 3 represents the data before modification, and 4 represents the modified data

Step 5, follow-up

With the datasheet, optimize the field name of your output, and a complete login log will be completed, which will be embedded in the html page perfectly.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.