.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan
Asad Ali
Post New Web Links

Monitoring the User Activities SQl SERVER 2008 R2

Posted By:      Posted Date: October 28, 2010    Points: 0   Category :Sql Server
 

Hi,

                Our Client Side  few people knows the sa user password. if they login through Management Studio and do some changes in data we will be the responsible for any data lose. I wannted to monitor the User activities in SQL SERVER if Users login through Management studio.If sa users connected through Application I don't want to monitor it but, when user logged in through Management Studio and do some changes in SQL server

We have to record the query fired by the user. is there any way to achieve this Please help me out.

Looking forward to hear from you

Thanks in advance

Selvakumar Rathinam

 

 

 


Selvakumar Rathinam


View Complete Post


More Related Resource Links

No ASPNET User in Windows Server 2008?

  

I have been looking everywhere online regarding this, but have not found a clear answer.

 

I have an ASP.Net app running on Windows Server 2008, it's basically a reporting app using Crystal Reports. I have encountered a roadblock in regards to rendering some pages. When I run the app in Visual Studio(on the same 2008 machine) everything works perfectly. Yet when I publish it and run it live the reports will not display correctly, I get a message saying Failed to render page.

 

I looked this error up in the Crystal Reports website and the fixes they have there refer to giving the windows "aspnet" user full access rights to certain folders on the machine. I would be happy to do this, however there is no aspnet user in Windows Server 2008.

 

I installed the server and set its role to be Web Server. I also checked all the options regarding ASP.Net and IIS 6 compatibility. Yet there is no ASPNET username anywhere.

 

Did I set the server up wrong? or do I have to do something within ASP.Net to  create this user and then give it rights? 


Need help with setting up SQL Server 2008 for role/user management

  

I've posted my problem in a few other categories.  Sorry for that.  I didn't see the security category until now.

I'm trying to use ASP.NET's built-in role/user management for a couple ASP.NET MVC 2 sites.  I'm using the MVCMusicStore as my guide.  I click on the Configuration Tool as the tutorial suggests, which launches the site administration page.  Unfortunately when I click on the security tab, I get an error that it cannot connect to the data store.  Attempting to test the AspNetSqlProvider gives me a similar error.

I've run the aspnet_regsql program as the error message suggested, and I have a role/user management database ready to go on the server.  The proper tables are constructed, and all that.  The configuration tool still doesn't see the data store.

As a test, I ran the MVCMusicStore demo in the debugger.  Its role/user management functionality worked.  I was able to add myself as a customer, and add/remove items from my cart with no errors or exceptions.  However, when I attempted to access its security tab in the configuration tool, I received the same error as I get for my own project.  How is it possible that role/user management works, but the configuration tool cannot change its settings?

Any suggestions on how I can get the configuration t

Having problems installing/setting up SQL Server 2008 Express/R2 for ASP.NET user management on deve

  
I'm a bit new to installing and configuring, well, just about anything regarding databases, so please bear with me. I'm currently building a couple of ASP.NET MVC 2 sites.  I'm following along with the MVCMusicStore tutorial, and am trying to set up user management using ASP.NET's configuration tool.  The problem is that ASP.NET can't 'see' the database. I have SQL Server 2008 installed on my laptop.  It's the version which is available on the Web Platform Installer (I believe it's Express).  When I try running the aspnet_sqlreg.exe program to set up my user management database, it can't tell that I'm running any version of SQL Server at all.  I get an exception about 'named pipes', and some other things I don't understand at the moment. Obviously, this is all a bit over my head.  Is there an idiot proof guide or tutorial available that describes, step-by-step, how to: 1. Install SQL Server 2008 on a development PC 2. How to configure it to be used by Visual Studio 2010 Professional 3. How to properly upload/modify the db on the production server with what's on the development PC Essentially, all the things that the ASP.NET tutorials gloss over.  Any suggestions, links to documentation, or other nudges in the right direction would be immensely appreciated.

MSSQL 2008 R2: Change Server Collation Steps, with user DBs already of desired collation

  
On the default instance of SQL Server 2008 R2, I want to change the server collation from the usual US default SQL_Latin1_General_CP1_CI_AS to an application vendor's required collation, Latin1_General_BIN.  I understand that changing the server collation is essentially the same as changing the collation of databases master and model -- is that a fair statement? But my main question is, since there are user databases on the server that I would like to preserve, can I not simply detatch the user DBs prior to performing the server collation change, and then reattach?  The database collation of the user databases is already set to Latin1_General_BIN.  This article, Setting and Changing the Server Collation, at http://msdn.microsoft.com/en-us/library/ms179254.aspx says that to preserve the user databases, I should script all DB objects out, and bulk-copy all data out, because the DBs will have to be recreated and repopulated.  Can someone explain why I ought not simply detach and reattach the user DBs, given that they are already of the desired collation (i.e., the same collation that I will be changing the server collation to)? Your help much appreciated. -Tom

Can not connect to user instance of SQL Server 2008 Express with Advance Services in Visual Studio 2

  
I used SQL Server 2008 Express with Advanced Services (not SQL Server 2008 Express) in Visual Studio 2010 because it is the fast and easy way to developed and managed data-driven applications with powerful built-in reporting and full-text search functionality. I received Sql Server 2008 Service Pack 1 with Windows Update. It's ok if I connect to instance of SQL Server 2008 Express with Advance Services (with SP1) through SQL Server Management, but if I try to attach a database in Server Explorer (Visual Studio 2010), I get the following error: "Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed." I'm stuck at this point. Is there a fix or a workaround? I've already tried deleting the 'C:\Users\Administrtor\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS' directory as mentioned in other posts, with no luck. The error.log at 'C:\Users\Administrator\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\error.log' has the following relevant entries: 2010-09-01 14:44:10.47 Server      Error: 17156, Severity: 16, State: 1. 2010-09-01 14:44:10.47 Server      initeventlog: Could not initiate the EventLog Service for the key '3677F6D3-17C7-4F', last error code is 2. 2010-09-01 14:44:10.47 Server &

Best practice for user defined error messages in SQL Server 2005/2008 --Need help

  
My requirement is to setup a standard practice for our team in handling DB erros and also  user defined messages. We are handling exceptions using Raiseerror method with in Try and Catch blocks.  For user defined message, we are using Sp_addmessage to add our application specific error/warning messages with error codess>80000.  Ex: 80001 | Company name already exists. please check. Here is the sample code ********************************************************************* CREATE PROCEDURE [dbo].[usp_CompanyProfile_insert]  -- Add the parameters for the stored procedure here  (@company_name VARCHAR(50),  @company_code VARCHAR(3),  @user_id INT  ) AS BEGIN  -- SET NOCOUNT ON added to prevent extra result sets from  -- interfering with SELECT statements.  SET NOCOUNT ON;  BEGIN TRY -- Begin Try Block      -- Validate company name   IF EXISTS (SELECT company_id FROM companyprofile WHERE company_name = @company_name)    RAISERROR(90021, 16, 1)      INSERT INTO companyprofile (company_name,       company_code,       createdby)   VALUES(@company_name,     @company_code,     @user_id)    END TRY  -- End Try Block &

Failed to generate a user instance of SQL Server 2008 Express with Advance Services in Visual Studio

  
I used SQL Server 2008 Express with Advanced Services (not SQL Server 2008 Express) in Visual Studio 2010 because it is the fast and easy way to developed and managed data-driven applications with powerful built-in reporting and full-text search functionality. I received Sql Server 2008 Service Pack 1 with Windows Update. It's ok if I connect to instance of SQL Server 2008 Express with Advance Services (with SP1) through SQL Server Management, but if I try to attach a database in Server Explorer (Visual Studio 2010), I get the following error: "Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed." I'm stuck at this point. Is there a fix or a workaround? I've already tried deleting the 'C:\Users\Administrtor\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS' directory as mentioned in other posts, with no luck. The error.log at 'C:\Users\Administrator\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\error.log' has the following relevant entries: 2010-09-01 14:44:10.47 Server      Error: 17156, Severity: 16, State: 1. 2010-09-01 14:44:10.47 Server      initeventlog: Could not initiate the EventLog Service for the key '3677F6D3-17C7-4F', last error code is 2. 2010-09-01 14:44:10.47 Server &

SQL Server Express 2008 idling user logout facility

  

please let me  know whether there is a  facility in SQL SERVER EXPRESS 2008 to logout idling users automatically.My problem here is that due to limited no of license other needy users can not log in to the system 

 


Adding Certificates for SSL connection for a user account in Windows 2008 Server

  

Hi All, I have a windows service running on Windows 2008 server, when I set the service to log on as Local System account, it works fine and does the ssl certificate authentication.

Where as when I change the service to log on to a different account it fails with the authentication error.

Could some one please help me here.

Regards,
Nandu. 


WCF service IIS7 Server 2008 custom User Name Authentication performance

  

Hi

I have WCF service. Nothing special. Everithing from "best-practice".

  • WCF Service hosted on Server2008(64)\IIS7
  • Application pool - integrated\.NET 4.0
  • binding: basicHttpBinding; security:TransportWithMessageCredential
  • transport clientCredentialType="None"
  • message clientCredentialType="UserName
  • custom class inherited from UserNamePasswordValidator provide simple validation.

Everything works as designed. But I have a performance issue.

I traced and logged the server and WCF class and figured out that there is no new "http request" in process until previous one passed "Verification". It looks like concurrent calls do not have any sense because UserName Password Validator may serve them one by one only.

It can't be a true. I hope I just missed some settings.

I will appreciate any help.

Thanks


Are the "ressource quotas" per user in Sql Server 2008?

  

Hi everyone,

I'm new to the administration of sql server 2008 and was wondering if there is a way to restrict ressources (processor time, ram and so on) per user. Something like disk quotas within the file system. Or is there a way to restrict ressources in general? e.g. can I specify somewhere that a query is allowed to use a maximum of 30% of the ressources?

We have the problem that some times users starts queries which consume all ressources of the machine. In these situations it is impossible to log on or to start queries if you already have a connection. They end in a timeout. The machine has 8 cores with hyperthreading enabled and 32 GB of ram, datafiles are stored on a raid 10 system with fast 10.000 rpm hds. System is on its own raid 0.

I have to do dba not as a main job, my main job is beeing a programmer.

Hope I could describe my problem and that there is a solution. Please ask if there is additional information needed.

Kind regards from germay!


SET Options on user logon / SQL SERVER 2008 R2

  

I need to capture the logon event and set some options (SET DEFAULTS, SET ANSI_NULLS, etc) and those options should be set that way during all the user's session. I thought a server trigger for logon would do it, but I found out it runs BEFORE the user's session begins, so it does not do the job. I need to set the options only for certain users of an application I don't have the code for.

I also read it could be done using alerts and  auditing, but I'm not sure how. I would appreciate any suggestion.

Thanks

Christian S.


SQL Server 2005/2008 Query Monitoring via DMVs

  

My understanding is that sys.dm_exec_query_stats is for archive aggreated query statistics so I’ve been using the query below to monitor active long running queries and it works fine for single statements – however it seems to return hugely inflated total_elapsed_time values with some procs or functions where multiple statements are run. I’m also interested if anyone know how to find query completion times from DMVs?

THANKS

Select …<some columns>
FROM sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) as st
INNER JOIN sys.dm_exec_sessions AS es
ON es.session_id = er.session_id
INNER JOIN sys.dm_exec_connections AS ec
ON ec.session_id = es.session_id
where er.total_elapsed_time / 1000.0 > @ElapsedThreshold
ORDER BY er.total_elapsed_time DESC;



Is SQL Server 2008 R2 Express - mult-user ?

  

Hi,

We have an application that we ship and include a version of sql express on the CD.  In the old days we used to ship MSDE and this worked fine with mult-users; we have since shipped 2005 & 2008 express editions and have had no issues.  BUT I have read somewhere ( though can't find the article again ) that something changed in 2008 R2 Express with regards to using it in a multi-user environment and I remember that the article suggested that "buying" the workgroup edition might be the way forward, if you want to use it in a multi-user system.  Obviously this has drastic impact, in that we can not give away free editions of Workgroup !! 

I have searched high and low on the Microsoft sites to find any information that might lead us to believe that R2 Express is not designed for multi-user systems, but can not find anything saying this; so just wanted to know if anyone knows of such information or why people may believe R2 Express is not suitable ?

Thanks,

Chris.

 



Auditing User Activity in SQL Server 2008 Enterprise

  
Is there a way to audit the activity of a certain login in SQL?  For example, I want to know everything that user "Test" does after he logs in.  Is that possible?

MS SQL Server: Disconnect Users From Database - Kill User Session

  
If you ever wanted to restore your database from a SQL backup file (.bak), but there are still users connected to your database, the backup operation will fail causing the error: Exclusive access could not be obtained because the database is in use.

Configuring User Profiles in SharePoint Server 2010

  
Note: I've added the necessary links relating to SharePoint Server 2010 prerequisites, along with instructions for configuring the user profile synchronization service, at the end of this
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend