I'm an Oracle DBA that has inherited some SQL Server 2000 databases.
Can you audit a particular user in Sql Server 2000. We need to know exactly what a particular user is doing, (i.e. creating/dropping objects, and what data he is accessing)
I am trying to perform a performance audit on a SQL Server where I am positive my most likely bottle neck is the disks. I assume the primary metrics I should be interested in are %Disk Activity and Disk Queue Length, right?
What are the actually guidelines for these metrics? Also, Are there other perf mons I should be running against the disks?
writing a T_SQL query for the following scenario..I have a SQL DB Audit file that gets populated with data as the activity on DB goes on.I have multiple monthly tables setup that the import should go into these monthly tables based on the event_time value in the SQL DB Audit file.all the data Like event_time '2015-08-25 15:59:39.033' should go to SQL table Audit_tbl_Aug2015 Query for reading SQLDB Audit file
SELECT * FROM sys.fn_get_audit_file ('C:ackupAudit*',default,default) order by event_time desc GO --DML for Audit table CREATE TABLE [dbo].[Audit_tbl_Aug2015]( [id] [bigint] IDENTITY(1,1) NOT NULL, [event_time] [datetime2](7) NOT NULL, [sequence_number] [int] NULL, [action_id] [varchar](4) NULL, [succeeded] [bit] NOT NULL, [permission_bitmask] [bigint] NOT NULL, [is_column_permission] [bit] NOT NULL, [session_id] [smallint] NOT NULL, [server_principal_id] [int] NULL, [database_principal_id] [int]
I have made a server security audit and specify from database audit specification to audit "select" on a certain user and on a certain table. I logged in by this user and made the select statement..when i run this query
"select * from sys.fn_get_audit_file('d:Auditaudit1*',null,null)"
It return a value at which time the query has done
after 15 minutes i repeated the same action, i run the audit query and the same result is showed off on the panel.is it suppose to return a list of values by how many times this user has made the select statement on that table ? for example at 5:00 pm then 6:00 pm and so on
I am new at this site and new at using SQL also.... I was wondering if anyone could help me out with my prob...
I have set up an SQL 2000 server and made some accounts which my friends are using to log onto my server.. Is there a way I could keep a check on who is doin what or maintain a user log....
Does anybody know how to trace user activity after they log off from the SQL Server 6.5? I know SQL Trace is able to trace the user activity in real time.
I'm trying to produce a user activity table out of a table that contains rows on when users log in or out. However, I can't seem to get the query to 'pick' the right date time in the join.
Here is what I came up with, but it doesn't seem to be working right :eek:
select distinct (userin.changeid), userin.userid, userin.createdate as LoggedInDate, userout.createdate as LoggedOutDate, DateDiff(mm,userin.createdate,userout.createdate) as Duration from userChange userin inner join ( select * from userChange where loggedin = 0 ) userout on userin.userid = userout.userid where and userin.loggedin = 1 and userin.createdate < userout.createdate order by userin.createdate desc
im trying to allocate a user appropriate permissions but i would like to see the users activities to make sure i do give that user exactly what they need how do i check user activity on sql server 2005
Hello,I have an application that will be logging to a SQL Server 2000database user user activity from several Windows 2003 terminalservers. This information will be retrieved by monitoring theSecurity logs of these servers (this part I know how to accomplishalready).A table in the database, tblLogEntries, will contain the followingfields:- ID = autoincrementing int- LogTime = Date/Time the user activity was recorded in the securitylog- Username = User's login ID that the activity was recorded with- Type = int, referencing a lookup table with the values of Logon,Logoff, and possible other future items- Server = The name of the server the activity was recorded on.The only question I have is, can you offer a way to process the totaluser login time during a given range using T-SQL.For Example...Given the table data:ID LogTime Username Type Server1 10-10-2003 8:30:00 Tom Logon SERVER-A2 10-10-2003 8:45:00 Sarah Logon SERVER-A3 10-10-2003 16:45:00 Tom Logoff SERVER-A4 10-10-2003 17:00:00 Sarah Logoff SERVER-A5 10-11-2003 8:30:00 Tom Logon SERVER-A6 10-11-2003 8:45:00 Sarah Logon SERVER-A7 10-11-2003 16:30:00 Sarah Logoff SERVER-A8 10-11-2003 17:15:00 Tom Logoff SERVER-AHow would you receive the output:User Logon Total Time for SERVER-ATom 17.0 hrsSarah 16.0 hrsI know I can handle this type of processing on my ASP.NET front-end,but I'm curious as to how easily it can be done by the database,itself.Thanks in advance for your assistance.
Please forgive the simplicity of this question - I am not the dba type. When I connect to a server and look at my connection attributes in activity monitor, the user column shows the correct information for my domainusername. When I run a certain stored procedure in that connection, the domainusername changes to another person. We are not using execute as, setuser, or anything special to explicitly change the user. The stored procedure is in a schema that is owned by dbo (principal_id = 1 - I verified by checking sys.database_principals.)
does anyone know how i can audit a servers login id's and tell the last time it was used. i have just gain about 8 sql servers with a bunch of users that i know are no longer around. so i am trying to trim out dead id's
What is the easiest way to find out what objects a security login has mapped to it? Something that would show all the explicit grants a specific user has.
How to find last login date/time for user DML acitivity on databases on Instance?
Is there any way we can find our the last login date/time for databases?
Note: 1. We can find if the SQL Trace is running and store.This is not good solution         2. Audit logins off/on is also not good solution.        3. Using DMV's also not good option, if reboot sql server instance then historical values can not see.
I am trying to create a TSQL statement or stored procedure that cantell me what users belong to what group and what groups have access towhat files. Can anyone help? Can I pull out a list of names fromActive Directory to use as my user list?
Hello all, does anyone know of a SS2005RS user audit program that an administrator can run on a RS server to show which userids have access to folders? I have in mind a pgm that would show:
folder users
Home user01, user02, user03
folderA user01,user02, user05
folderB user02, user06
Is there a pgm available as a download, or does someone have a home-grown pgm whose source they would let out?
Does anyone know of a quick way to audit all users in a database and display their rights and permissions on a table level. I would hate to have to do it one user at a time. There has to be an easier way.
I'm going through a Sarbanes Oxley audit and need to provide them this information.
I'll try to reproduce this later, but want to report it before I forget.
I just had my package fail on a VM I was testing on. It failed because on that machine, I logged in as MachineNameAdministrator instead of using my domain account (the VM is not in the domain).
This was a problem because the "User Name" column generated by the Audit Transformation was 17 characters long! This is the length of my domain + user name on my development machine. Similarly, the machine name length was 15 characters.
I'd love to know what the "correct" sizes are for these columns. In the meantime, I'm going to set these to 255 manually, and hope the size sticks.
P.S. There was one other post on this topic, though the thread isn't clear that this was the problem: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=472445&SiteID=1.
I need help...here is the problem.Last weekend, the servers in our datacenter where moved around. After thismove, and maybe coincidental, 1 server is performing very poor. Afterrunning a trace with SQL Profiler, I saw the problem which was laterconfirmed with another tool for SQL server performance monitoring. It seemsthat all connections to the SQL server (between 200 - 400) are doing a login/ logout for each command that they process. For example, the user'sconnection will login, perform a SELECT, and then logout. This is not a..NET application. The client software was not changed, it is still thesame. The vendor has said that it is not supposed to do that, it issupposed to use 1 connection that log's on in the morning and logs off atthe end of the day or whenever the user exits. 1 user may have severalconnections to the database.At times, the server is processing over 250 login / logouts (avgeraged for30 second period). Has anyone seen this problem? I have the server inAUDIT FAILUREs only. The server has become very unresponsive, things thattook 3 seconds now take over 15 seconds.Any ideas???
What is the best to produce user activity log ? Using triggers (insert, delete, update) is rather heavy (?) operation. Has anyone used SQL Profiler for that ? In log should be: who, what, when, workstation, table, changed fields, some data key values.
This is my first time to deploy an asp.net2 web site. Everything is working fine on my local computer but when i published the web site on a remote computer i get the error "Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed" (only in pages that try to access the database) Help pleaseee
I have registered a server on to my client machine successfully,but I am not finding the current activity item in the management folder on EM.How do I see the current activity on the server.Do I need to be SA for that server?My machine has NT workstation with SQL server 7.0 on it.Any one to help?
If I run sp_who2, the following message is returned. (it also is returned randomly while using function in EM such as Backup/Restore.)
Msg 268, Level 16, State 1 You cannot run SELECT INTO in this database. The DBO would have to run sp_dboption to enable this option.
What is it??? It's buggin me......
Dano
p.s. I remember a similar problem in the past, it had to do with turning truncate on checkpoint in one of the system databases msdb/master/tempdb or something like that. I turned off all Trunc. and Select/Bulk options on these...
I have a requirment to create a activity log to log all changes that take place in my tables as they happen. All my Inserts,updates & Deletes take place inside stored procs. A windows .NET application calls the stored procs and passes the data set to the stored procs to execute them on a Sql server 2000 database.
When an update statement is run inside my stored proc, the dataset only passes me the new values. but,i will need the old value from the database before the update takes place to report in my activity log. What is the best way to do it ?
My activity log shold report a description like this
"Name Changed from 'Robert Johnson'(old value) to 'Bob Johnson'(New Value)"
Though i can do a select from the tables to get the old values and match the new values to get the differences before running the update statements, it is too much work as i have well over 100 stored procs in my application and i have to check for every single field value in a table. If only one column changed in a table containg 20 columns, i would still have to check for 20 columns before determining which fields changed.
Can any one suggest me a better solution to report the old values and new values for all the updates that take place in a database through stored Procedures.
Hi experts, I just want to know how can i kill all the processes of a database if the database have more than 100 connections.This is for the purpose of restoring a database.
I usually run this command in SQL 2000. Which tells me what is running with all the sql code statements.
Is there an alternative command to run in SQL 2005...rather than using the Reports - i find it easier to execute sp i called this sp_now.
set nocount on declare @handle binary(20), @spid smallint, @rowcnt smallint, @output varchar(500)
declare ActiveSpids CURSOR FOR select sql_handle, spid from sysprocesses where sql_handle <> 0x0000000000000000000000000000000000000000 --and spid <> @@SPID order by cpu desc
OPEN ActiveSpids FETCH NEXT FROM ActiveSpids INTO @handle, @spid
I have a SSIS which copies data from a table to a flat file. The connection string of this file is variable and the file is reused if not exists and is created if exists.
When I run the SSIS manually from my microsoft visual studio it works properly. However, when I run this SSIS from the Job Activity Monitor, I get the following error:
Message Executed as user: REDCAMadminsql2k5. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:41:10 AM Error: 2007-09-10 08:48:04.99 Code: 0xC020200E Source: Crear Historico Historico [1] Description: Cannot open the datafile "\srvnfileHISTORICOSCAJEROSOFI3210C01OFI3210C01_2007-7X.txt". End Error Error: 2007-09-10 08:48:04.99 Code: 0xC004701A Source: Crear Historico DTS.Pipeline Description: component "Historico" (1) failed the pre-execute phase and returned error code 0xC020200E. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 8:41:10 AM Finished: 8:48:53 AM Elapsed: 462.234 seconds. The package execution failed. The step failed.
A developer is running sql transactions from the application and needs to know how it is executed from database, I know I have to run Profiler Trace, but not exactly which traces , Can you please guide me?
Trying to view current activity in EM however get error 1222 Lock request time out period exceeded, this also occurs when trying to view tempdb database.
Is this a EM Gui error or locking issue and hwo do you resolve?