How To Create Logs Of Users And The Specific Transaction He Made?
Mar 4, 2008
hi..i need help on how to create user logs with a specific transaction he made. for example, he added new item in the database, the grid wud contain user id, date, time and transaction. help po.. tnx!
Hi, we are almost finished developing our database and we have a table we want to monitor because it is getting information deleted from it and it has a delete trigger on it but we want to track the changes to the table and were wondering how to track specific changes to a user database? We want to see who is making the change, what the change they are making is and also what is the time they are making it. I have used SELECT * FROM SYSPROCESSES and I am running SQL TRACE with filter on MS SQLEW, and MS TRANS, and Visual Basic with SQL statements on tblRoute,( the table that I want to monitor) and I want to know if there is any other way to monitor this table more closely?
hi alli've got two tables called "webusers" (id, name, fk_country) and "countries" (id, name) at the meantime, i've a search-page where i can fill a form to search users. in the dropdown to select the country i included an option which is called "all countries". now the problem is: how can i make a stored procedure that makes a restriction to the fk_country depending on the submitted fk_country parameter?it should be something like SELECT * FROM webusers(if @fk_country > 0, which is the value for "all countries"){ WHERE fk_country = @fk_country} who has an idea how to solve this problem?
I am very early on in developing a website to track issues with projects which is tied to a SQL database. I have my Projects Table, my Users Table, and am creating a third table to track issues. I'm wondering what is the best way to assign specific users to specific data/projects. The user should only be able to view & update the projects assigned to him. He should not be able to see other projects. What is the best way to assign projects/data to the users to make sure they are only viewing their data?
Hi All,This seems like a tricky question to me. I have a Stored Procedurethat encapsulates a number of updates to various tables within atransaction.However, at a later part of the transaction I need to be able toselect records changed by an update statement made earlier within thesame stored proc (and within the same transaction) and need for thatselect to reflect the changed values.My understanding, however, is that the records aren't actually changedby the update statement until the transaction is committed, andtherefore my later select statement won't return the expected recordsbecause the update is being held until the transaction is committed.Is this accurate? And, if so, is there a reasonable workaround thatstill leaves me able to rollback the entire transaction if I strike aproblem somewhere along the way?So, a pseudo code example would be:BEGIN TRANSACTIONUPDATE mytable SET myid = @yourid WHERE myid = @idSELECT * FROM mytable where myid = @idCOMMIT TRANSACTIONIn this above example, would the select statement return the recordsthat have a myid value of @id as before the update as after theupdate?Many, many thanks in advance!Much warmth,Murray
I am trying to revert back to Windows 7 after upgrading to Windows 10, however it will not let me and the following message occurs: "Remove new accounts.Before you can go back to a previous version of Windows, you'll need to remove any user accounts you added after the most recent upgrade. The accounts need to be completely removed, including their profiles.You created one account (NT SERVICEMSSQLSERVER) Go to Settings> Accounts> Other users to remove these accounts and then try again".However I did not create any new users and there are no other users listed in the Accounts section.
Trying to track the users who are logged in and making changes to my database. I have granted permission through the Windows Groups and the log displays the group names and not the user names. Is there something to set to display the user and the group?
[dbo].[PortalUser]( [PortalUserID] [bigint] IDENTITY(1,1) NOT NULL,
...
[dbo].[Role]( [RoleID] [bigint] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](250) NOT NULL, [IsActive] [bit] NOT NULL CONSTRAINT [DF_Roles_IsActive] DEFAULT ((1)) ...
[dbo].[PortalUserRole]( [PortalUserRoleID] [bigint] IDENTITY(1,1) NOT NULL, [PortalUserID] [bigint] NOT NULL, [RoleID] [bigint] NOT NULL, [IsActive] [bit] NOT NULL CONSTRAINT [DF_PortalUserRole_IsActive] DEFAULT ((1))
I'm asking to get a list of portalUsers that do not have a PortalUserRole records for the rolename I'm checking against. And don't ask me why the person who coded this is searching on rolename not ID. But this is how we're doing it for now.
I have an archival process on a large database that runs once a month.At the beginning of the process the triggers and indexes on thetables whose data is moved are dropped, the data is moved and then thetriggers and indexes are recreated at the end. This produces amassive improvement in performance.The problem is the process is supposed to run on users accounts (thatsthe way the front-end is set up) and they don't have the neccessarypermissions to drop & create triggers & indexes. I can't see any wayto give them permissions only on specific tables or triggers/indexes.Nor does giving them permissions to the stored procedures that do thedropping & re-creating work, DDL permissions don't seem to beinherited the way they are with tables.Is blanket rights to drop & create objects through the db_ddladminrole the only way users can get rights?Thanks,K Finegan
What is the best way to clear the transaction logs. My backup job each night is ending because it says it is running out of disk space and I need to clear up the transaction logs. Any help is appreciated. I see many different options (trucate option, auto shrink, etc.), just need some assistance tosome more specific best approaches. Thanks
Im having issues truncating my transaction logs. I have logs in excess of 40 gigs. All the info in the BOL is very vague. Any assistance would be apreciated.
to take the transaction log back up regularly I should have truncate log on check point false. If I do so then how will I truncate the log. regards, Renu
I am trying to import records via bcp (about 1,500,000 records) and I keep running out of disk space. Is there any way to limit or do away with the transaction log (and still be able to import)?
When I look at the Database maintience plan history entry for backup I have a message that reads: "Backup can not be performed on this database. This sub task is ignored".
Have anyone come across this error before?? As part of the Maintenance plan some transaction log are being backup and some aren't instead they receive the message above.
i have several sql servers doing maintenance plans and backing up the transaction logs to tape. unfortunately it seems that the server keeps adding the transaction logs to the same tape, without overwriting them. Nowadays a transaction log backup to disk takes 2 minutes but when done to tape it is taking up 1hr54minutes. What can i do so that the tape is automatically initialised without having to do it manually... Thanks
Is there a way to view the transactions from a .TRN transaction log file? If so can I overwrite some of the transactions on the file and then restore from it? I am just curious.
Hi, I know just about nothing about SQL Server. I am getting this error:The log file for database 'my_database' is full. Back up the transaction logfor the database to free up some log space.I can't access the transaction logs to back them up. I am told that my ISP'stech support should have it set up to shrink those logs automatically everyso often. Is that true?Why are they needed? Up till Monday, the logs are just of our getting SQLServer set up, so couldn't the logs just be deleted? Most of my site can'trun with this problem.I'd appreciate any enlightenment anyone can give me!Thanks, Jill
This seems like it'd be a really stupid question, but for the life of me Ihaven't been able to find an answer that works....I have a database that's approximately 400MB when shrunk... and thetransaction logs are at about 4.8GB when shrunk... I can't seem to get thetransaction log any smaller, no what I try. How can I get the log downbelow 1GB and keep it there? I've only got an 18GB hard drive and I need torun 2 copies of this database.-steve
Hello, I was wondering if someone could point me in the right direction on how to do this....I have a table that is a many to many relationship between userID's and courseID's. for instance (example data for each row - dont let the spaces between rows throw you off I am just adding them to seperate the different users)userID = 1, courseID = 1userID = 1, courseID = 2userID = 1, courseID = 3userID = 2, courseID = 1userID = 2, courseID = 3userID = 3, courseID = 1userID = 3, courseID = 2userID = 3, courseID = 3I want to be able to check for a number so in this case "2" which I would like to return all users who do not have a courseID = 2. In the example above the query would return the user with the userID of 2.Any help in how I should go about doing this would be greatly appreciated. Is there a function I can use? I apologize I am extremely new to SQL
Hello, I am trying to figure out the time a certain store procedure was executed. I know the SP's name and approximetly the time it was executed. Is this possible to do? Honestly, i am not sure if SQL server 2005 is smart enough to keep track of commited transaction on the server.
I have seen quite a few people post this type of problem, but I am finding few solutions. Your advice and/or experiences are greatly appreciated.
Here's my scenario:
Environment: Windows NT 4.0 SP 6, SQL 7.0 (set up for Transactional Replication)
Problem: We have several remote dB machines configured for full recovery. On these machines are several dB's that are capturing aprox. 280 data points per second per unit. (Each dB represents one unit, and we have 21 units) No problem here. The problem is the transaction logs, that obviously grow profusely, will not SHRINK after, backups, dbcc commands and TSQL has been issued in failed attempts to shrink the logs. (in other words we've tried everything)
My questions are:
1. Because we are replicating, is it absolutely necessary to configure dB's for FULL recovery? How do I check in 7.0 if the dB is certainly in FULL vs. SIMPLE recovery mode?
2. I work with SQL 2000 and shrinking files is no problem. How can we shrink these log files in SQL 7.0?
It is rather urgent I find a solution as we are running out of hard drive space on our remote machines. Please help :-(
I am having trouble Truncating a Transaction Log. I`ve tried everything in Book Online. I`ve backed up the database, I`ve tried DBCC SHRINKFILE, DBCC SHRINKDATABASE, BACKUP LOG TRUNCATE_ONLY ...etc, but it will not shrink. Any suggestions ? Thanks.
Currently, my databases and their corresponding transaction logs are all on the same disk array. I finally was able to acquire a seperate disk, specifically to seperate the logs, in case of failure, etc. Now, I need to figure out how to go about moving the tansaction logs off the current disk array and on to the new disk. In Enterprise Manager, I brought up "Properties" for the first database, and went to the "Transaction Log" tab. Clicked on the "browse" or "..." button in the location field and got the following message: "A transaction log file's physical name cannot be changed once the transaction log file has been created". :(
I have been looking through Books On Line, but have been unable to find anything helpful yet.
Can anyone help me figure out how to go about moving a transaction log's location? There has to be some way. Even if it involves shutting down the server, altering system tables, etc. I need to get these moved.
I have set up a maintenance plan to backup my databases but when I view the maintenance plan history the transaction log backup steps have a success tick but a message saying :
"Backup can not be performed on this database. This sub task is ignored"
I have looked in my backup directory and only see *.BAK files and no *.TRN. The transaction logs are supposed to back up at 1AM and the databases at 2AM
All my databases being backed up have the truncate log on checkpoint option set. Is this best practise according to my backup schedule ?