Tracing Users Of A Database
Jun 13, 2007Hi
As a newbie to DBA type tasks, how can I trace who has accessed the server/database. I know there is a SPID in the Server log but what does this represent?
Thanks!
ADVERTISEMENT
Tracing Insert, Update Or Delete For Entire Database
Jul 16, 1999I need to create some kind of log file or table that will record whenever an insert, update or delete is made to any table in a database. I have seen triggers that do this kind of thing on a table level. Can this be done with a trigger or a stored procedure on a database level? If so some kind of example or syntax would be great.
TIA.
Mike
Why Out Of The Blue Would VPN Users Be Unable To Connect To Database And Local Users Are Unaffected?
Mar 6, 2008I have had this issue just pop up. I have local users who can connect fine, but my users that require connection by VPN cannot connect. I get the server not available or access denied error. I did confirm that the VPN'ers are connected to the network correctly and can see that their shares and mappings are correct. Any ideas? Thanking you all in advance!!
View 6 Replies View RelatedDifference Between Database -- &&> Users And Security --&&> Users
Nov 28, 2006Hi Team,
In SQL Enterprise Manager, when we expand "Database -->Users", we see the
users there. When we expand "Security --> logins" we see the same users there.
Can you differentiate these two.
Thanks
Santhosh
Restore Of Database Backup To Another Database Makes Users DBO
May 7, 1999Anybody figure a way around this:
In a development server you take a backup and you want to move the entire database to a production server. The production server does not contain the database, users, or logins in master.
When you restore the backup to production in 7.0, the users get moved to DBO because the logins do not exist and then you cannot delete them.
We are having to drop the users from the database on development, back up the database and restore it to production, then recreate the users on production.
This is bogus and did not happen in 6.5 because of the aliases.
Anybody come up with a better way?
Fun With Tracing
May 24, 2001I would greatly appreciate any help with this problem, as I've been digging thru every resource I can find looking for a solution with no luck.
I'm going to be monitoring a database for all SQL statements containing INSERT, DELETE, or UPDATE. I'm grabbing the user name, time, and the entire text of the query. I can already do this programmatically, no problem. The problem lies in this. When I set up a trace on SQL Server 2000 using the system stored procedures sp_trace_create, sp_trace_setfilter, etc, and set the trace to save to a trace file, I find that I must first stop the trace then close the trace before I can use fn_trace_gettable to get the information that I want. However, this is undesirable, because this database may be accessed worldwide, and stopping the trace to read the data could cause the trace to miss some users making changes. Does anyone know how that I could get my trace data into a table so that I can just run queries on that table to get my data? It's very important that I not stop the trace to do this. Thanks for your help!
JR Rickerson
Software Engineer
Infinite Software Solutions, Inc.
Tracing
May 20, 2008What are different kind of traces we can take regarding the performace and how traces can be used for improving performance ?
spatle
Tracing The Deadlock
Jul 12, 2006Hi,
I need to trace deadlock, one of article was mentioning “QL Server Profiler's Create
Trace Wizard to run the "Identify The Cause of a Deadlock" for SQL Server 7.0, is there any way I can do this in Sql Server 2000?
Tracing UDFs
Jun 6, 2006How would I go about tracing UDF performance in profiler? I'd like to
specifically know the impact of the UDF without having to dig into the
execution plan of the statement containing it. Is this possible?
Tracing Sql For Locks
Jul 20, 2005I am fairly new to SQL Server. I am writing a tool in stored procedureto identify locks in a table. I have already written the basic frameworkof the SP. It will reside in master database and take two inputs. Databasename and table name. From that it will show all locks at that instanton that table of that database. If table name is omitted, then it will showlocks on all tables.I am using syslockinfo, spt_values tables and joining with SP_WHO procedureto get the table name, user name and the session id.Now what I need is to find out which SQL is causing the lock and since whenlock is being held on the table. Which tables in master database holds therequired information.TIA.Ravi
View 2 Replies View RelatedTracing Sql From Code
Jul 20, 2005HiI want to trace all the selects/deletes/modifys whatever on a databasein an application that are performed in a seperate application.I need to look into this, any ideas?- Can triggers do this kind of thing- Can you somehow access the profiler via OLE or similiar to do this?- Anything else?TaF
View 5 Replies View RelatedTracing The Deadlock
Jul 12, 2006Hi,
I need to trace deadlock, one of article was mentioning €œQL Server Profiler's Create
Trace Wizard to run the "Identify The Cause of a Deadlock" for SQL Server 7.0, is there any way I can do this in Sql Server 2000?
Tracing The Sp_executesql
Oct 17, 2006Hi,
I am using sp_executesql to get some data but it is not working. Is there a way to actually see the actual statement where subsituted variables are replaced with the actual values.
SET @SQLString = N'SELECT Top(1) @Passenger_OUT = Name, @Ticket_OUT = TicketNumber , @TAN_OUT= [TAN] '
+ N'FROM dbo.Table1 WHERE 1 = 1 '
+ Case @MatchAmount When 1 Then N' and Amount = @BillingAmount ' Else N'' End
+ Case @MatchTicket When 1 Then N' and LTrim(TicketNumber) = STUFF(STUFF(@TicketNumber,Len(@TicketNumber)-@RemoveRight+1,@RemoveRight,''''),1,@RemoveLeft,'''') ' Else N'' End
+ Case @DaysDiff When 0 Then N'' Else N' and DATEDIFF(d,@BillingDate , InvoiceDate) <= @DaysDiff ' End
+ Case @MatchName When 1 Then N' and Left(Name,@CharsToMatch) = Left(@PassengerName, @CharsToMatch) ' Else N'' End ;
SET @ParmDefinition = N'@BillingAmount decimal, @TicketNumber varchar(15), @RemoveRight tinyint, @RemoveLeft tinyint, @BillingDate datetime, @DaysDiff tinyint, @CharsToMatch tinyint, @PassengerName varchar(35), @Passenger_OUT varchar(35) OUTPUT, @Ticket_OUT varchar(15) OUTPUT, @TAN_OUT varchar(25) OUTPUT';
EXECUTE sp_executesql @SQLString, @ParmDefinition,@BillingAmount=@BillingAmount, @TicketNumber=@TicketNumber, @RemoveRight=@RemoveRight, @RemoveLeft=@RemoveLeft, @BillingDate=@BillingDate, @DaysDiff=@DaysDiff , @CharsToMatch=@CharsToMatch, @PassengerName=@PassengerName, @Passenger_OUT=@Passenger_new OUTPUT, @Ticket_OUT=@Ticket_new OUTPUT, @TAN_OUT=@TAN_new OUTPUT;
Print @SQLString will gives the statement with variables and I need to see the actual data plugged in during runtime.
If I take the ouput of @SQLString and run it myself by adding variables it works fine
Thanks
Shafiq
After Replicating A Database, SP's In The Same Database Are Not Able To Modify By Other Users.
May 21, 2008hi there,
After replicating a database, the stored procedures in that same database are not able to edit by other users than service account (sys admin users), When the normal users try to edit any of the stored procedures, sql server 2005 is throwing the following error...
Msg 21050, Level 16, State 1, Procedure sp_MSmerge_ddldispatcher, Line 12Only members of the sysadmin fixed server role or db_owner fixed database role can perform this operation. Contact an administrator with sufficient permissions to perform this operation.
any suggessions are most welcome.
by
CHINNASAMY
Profiler Only Tracing One Server
Feb 4, 2002There is a bug in one of the service packs where Profiler (7.0) only traces one server (regardless of the server you tell it to trace). Can anyone tell me how to fix this or point me to a KB article? I thought this was fixed in SQL 7 SP 3, however I'm experiencing this problem with SP3 installed.
View 1 Replies View RelatedTracing Failed Logins
Jul 19, 2001SQL 7 profiler has an event in the Misc. category of Failed Login. It does not, or at least I cannot get it to, produce any output when a failed login occurs. Any hints?
I tried this because every week or so I get this in the error log:
Login failed for user 'Admin'.
It occurs several hundred times within a minute or so. It obviously has to be an automated process as you couldn't click a button or press a key 13 times a second.
The login does not exist as a SQL login so I can't tell which database it is trying to get at. Any suggestions gratefully received.
Simon
SQL Server Error Tracing
Jan 24, 2000Hi There,
I hope someone can assist me in tracing the cause of a problem I am experiencing. I have a Web Server with ASP's querying the SQL Server 6.50.416 database. There is only one user db on this machine and yet I am running out of User Connections (current setting 2000) and memory (128MB RAM). Also, NT repeatedly experiences Stack Dumps. I have used the PRINTDMP utility to try and trace the cause of the error. The "Input Buffer" section of the Stack Dump (symptom dump) contains the following:
SELECT FK_SUB_PRODUCT_GROUPING, FK_SUB_PRODUCT_NAME FROM RESOLUTION_PRODUCT_SUB_PRODUCT WHERE FK_PRODUCT_CODE=1072 order by FK_SUB_PRODUCT_GROUPING
Is this SQL Statement the cause of the Stack Dump? Does anyone have any other ideas on what may be causing my problem. Any help would be greatly appreciated.
Tracing Or Debugging A Procedure
Dec 21, 2005I have a very simple piece of code (see below) which when executed sometimes takes around 7 minutes and sometimes around 3.5 hours. the difference is that during the 3.5 hours there is a lot of querying of the table being updated. But I don't know how to find out if this is the case. How can I find out whether my process is waiting (for locks or for any other reason) - is there a trace or debug facility within the tandard Microsoft Toolset which I can use.
Regards
Colin
Problem code below
===============
print 'Updating stm_brnline - Start time is ' + convert(char(25),getdate(),113)
--
update m
set m.branchpgrade = s.branchgrade
from stm_brnline m, tmp_brngrades s
where m.traddiv = s.traddiv and
m.contcode = s.contcode and
m.merchsect = s.merchsect and
m.branch = s.branchcode
--
print 'Updating stm_brnline - End time is ' + convert(char(25),getdate(),113)
Actual Syntax By Tracing
Feb 3, 2004Hello
I am running 6.5 sql and work with a traffic and billing software ( called NOvar) from another company(encoda system) which does a lot of scheduling, reporting etc
I dont know the contents of table (100 table ) and their column
or which table its querying to take out reports
Can i create a trace to know the syntax each time some thing is executed.
I also need to create customized reports, can this be done by sql reporting or does i need to go from crystal reports or someone else
For i dont know any language except sql and HTML
sej
Tracing Query Time
Oct 5, 2007I have a Stored Procedure that execute some queries on link server. It takes so long to complete so my application get timeout error. There was no problem until last week. I suspect, remote queries that qorks on link server takes long. How can i trace the time of queries. Any idea about link server timeout problems?
Thanks in advance.
SQL Profiler And Single Client Tracing
Mar 10, 2000I have a client which makes about 50 connections to my SQL Server using a non-unique username. Under SQL 6.5 I could trace all SQL activity by PC name, is this possible using the SQL Profiler in SQL 7?
Gordon
Tracing The Actual Syntaz As Is Excuted
Jan 30, 2004I am using 6.5 and on this i used different program
and people execute query
Can i place a trace to show what query is being executed from which program and at what time
Meaning the syntax of it and by whom
I start tracing and it paging file becomes to large
sej
Question On Tracing Data Change
Jul 11, 2007I have an issue where i have a field that changes data and i need to trace what it is that is changing it. I have setup a trace for the database but i would like to be able to narrow it down even further to the table and even the column if possible. Is there a way that i can trace data changes on such a granular level
View 1 Replies View RelatedTracing , History And Analyzing DTS Package
Nov 15, 2007Hi guys,
We have 2 databases: DataBaseSource and DatabaseDestination. We need to truncate all the data in DatabaseDestination and put all the data from DataBaseSource into DatabaseDestination.
What is the best way to do that, we have a lot of data?
And what is the best way if we also need to keep a trace of what happened in case we wanna go back and see what happened.
Also , pls, if we use DTS, is it possible that if someone wants to see what the DTS does, is it possible to read the DTS? I mean if I give a dts to sompeone, a new DBA guy in 2 years for example, how can he know what a certain DTS does? I mean does SQL 2005 put the DTS packaege scripts somewhere or is there a friendly way to know what a DTS dsoes exactly?
Also the trace to see if something went bad is important for us?
Sory if i didn t express myself well enough, and thanks a lot for your help.
Rachid.
Tracing Stored Procedure (SP) Usage
Jul 20, 2005Hi,what I am trying to discern is if there is any way of logging SPactivity on a SQL server 2k DB. Ideally I would want to log SP name,parameters, user and time.I found sp_monitor in MSDN but that just gives overall statistics.Not specific enough to aid debugging.Thanks in advance,Finlay Macrae
View 1 Replies View RelatedOptimizer Logic Tracing Tool
Jul 20, 2005Guys,what I need is a tool which gives details on the choice of anexecution plan by the SQL Server. For example, the cost for a hashjoin might be 200 and 100 for a nested loop, and therefore a nestedloop is used. Same thing for the access paths for each table/viewinvolved. In Oracle, we turn on event 100053 to see this kind of info.ThanxDaniel
View 2 Replies View RelatedTracing HTTP/SOAP Endpoints
Mar 23, 2008Has anyone worked out how to trace SOAP/HTTP Endpoints in SQL 2005 SP 2?
I'm not having a particular problem, just want to know how things work, such as which layer is responsible for the HTTP Authentication. I've traced the HTTP.sys stack using logman.exe (see http://www.thelastpickle.com/2008/03/22/tracing-sql-soap-endpoints-through-httpsys/) .
I cannot see any SQL Profiler events, and I've tried setting trace 7801 and 7803 as described in http://www.sqljunkies.com/Article/5CCAC423-1407-4A36-AF71-ED6A67D9646A.scuk#_Toc52964195 but they do not result in any logging.
thanks
Aaron
Users Database
Jan 31, 2008how can i create a users database to store users infos (names pictures...) ??
thx for taking time to read this
Tracing Values In DTS Package/stored Procedure
Jul 20, 2005Hello,I'm currently working on debugging a very large DTS package that wascreated by someone else for the purpose of importing data into mycompany's database. The data is mainly user/contact-related data forour customer base.We ran into problems when one import, of about 40,000 rows, tookupwards of six hours to complete. Many of the stored procedures usedby this package were written using XML. I've re-written many of themusing native SQL to see if that improves the performance, but I'mgetting some errors that I haven't been able to diagnose.Instead of asking about my specific errors, I'd like to know moregenerally what ways are there to debug DTS packages and storedprocedures? I'm aware of, and experienced with SQL Profiler but it'snot giving me the info I need. I need the ability to see exactly whatvalues are being passed to every call to a stored procedure fromwithin the DTS package or another stored procedure.I've used it very successfully to debug .asp, .aspx, .vb and the like,but right now I'm running it while running this huge stored procedurethat is called by the DTS package and does the lion's share of thework, including multiple updates and inserts into about 10 tables.The problem is, I see the calls to the "sub-procedures" from the mainone, but I can't see the values of any of the input or outputparameters. Instead ofInsert_Contact 'John', 'Q', 'Smith', '333-333-3333'......etc.I seeInsert_Contact @FirstName, @Initial, @LastName, @PhoneNumber......etc.My trace includes Stored Procedure events:RPC: CompletedRPC: StartingSP: StartingSP: StmtCompletedSP: StmtStartingand TSQL:Exec Prepared SQLPrepare SQLSQL: BatchCompletedSQL: StmtStartingI figured with these I would've covered the bases but I don't see anyof the parameters, which is critical for my debugging, as some of themare not being properly set.Any ideas or help would be greatly appreciated!TIA,Mike
View 4 Replies View RelatedAccessing Database Users
Feb 8, 2007Hi,
I would like to know if there is any way of Accessing database users from frontend and changing their persmissions.
Thanks
Niranjana.
Users Not Listing In Database.
Jan 25, 2001Hello Experts,
I copied a database from my production server to the development server and now i dont see the users in the database from the enterprise manager,database and users folder. But, When i run a query to against the sysuser table from a query analyser I can see those users here. Why cant I see in from the Enterprise manager. Any advice please..
Appreciated any comments on this.
Thanks,
Vilke.
How To Force Users From A Database?
Dec 1, 2000Does anyone know a way to force out all users from a particular database? I need to script this to perform some maintenance at a particular time every day.
Thanks for your help,
Les
Dropping Users In Database
Dec 23, 1999Is there a way in SQL to drop any users from a database. I am trying to schedule some nightly DB maintenance and some users are still in there sometimes. I need to be able to kick the out to do dbcc checkdbs, etc. Is there a way for me to do this without stopping the SQL services?
View 3 Replies View Related