DB Engine :: Determine Database Access History?
Jul 31, 2015I was asked to determine the last time 2 databases were accessed.
Are the .trc files an accurate way to determine the database access history?
ADVERTISEMENT
How To: Determine If Current Windows User Has Login Access, Database Access And If They Are A Member Of A Specific DB Role.
Mar 25, 2008
I need to determine the following about the current authenticated Windows domain user who is trying to access a SQL Server via a trusted connection.
1 Has the current user been granted login access to the trusted SQL Server?
2 Has the current user been granted access to a specific database?
3 Is the current user a member of a specific database role such as (DB_ROLE_ADMINISTRATORS)?
Thanks,
Sean
Determine Last Access Of A Database Or Table
Dec 18, 2007Does anybody know of a way to determine the last date/time a table has been accessed (query/update)? I've done enough research to know that this isn't easy. However, perhaps somebody has figured out a way through some of the stats that SQL Server keeps to determine the last access of a table. I have recently been put on a team that had no DBA and has a number of databases out there. They would like to determine which databases are inactive and get rid of them. I am a developer and haven't had much SQL Server Administration experience. Any info will help greatly! Thanks!
View 2 Replies View RelatedDB Engine :: Unable To See Server Job History
Nov 18, 2015I am unable to check old history in sql server jobs. I have set limit size of job history. but it will show the old history.
View 2 Replies View RelatedDB Engine :: Table Transaction History
Oct 22, 2015i have a table and i would like to know what are the queries that were executed referring to that table in the past 5 days. is it possible?
View 4 Replies View RelatedThe Microsoft Jet Database Engine Cannot Open MS-Access Database
Aug 18, 2007
I have MS-Access as data source for one of the reports. I can preview the report fine from BI studio however, it does not work when I deploy it on report server.
The error is :
An error has occurred during report processing.
Cannot create a connection to data source '<data source name>'.
The Microsoft Jet database engine cannot open the file '<UNC location of the MS-Access database>'. It is already opened exclusively by another user, or you need permission to view its data.
MS-Access database is located on a different server.
Any help to solve this? I understand it has something to do with permission both on server where reporting service is running as well as the server where MS-access database is located. Pls help.
DB Engine :: Stored Procedure Change History Information?
Jun 5, 2015one of my SQL Developer member had one observation that, size of the parameter 'Parameter_XYZ' in certain stored procedure had changed from 25 to 255 during some production fixes, however suddenly its looks like that, someone has changed it back to 25 instead of 255.
DECLARE @Parameter_XYZ
varchar(25);
Can we figure out in which sprint/drop the stored procedure was changed and the Parameter_XYZ back to 25. Can any log recovery mechanism will get such details.
Can we get stored procedure text between different alteration.
How To Access Transactions History
Mar 16, 2005hello
i want to know how can i see user's logs?i meani want to know wich users have accessed wich tables and when thay have changed the data...
i know these information is in logfile..(i think so..i'm now sure)..but i'm sure these information should be save in sql..but i don't know how to find them..
Somayeh
Query To Determine Public Access
Aug 30, 2007Is there any T-SQL Script or SP that would determine what tables public users have access to? I only ask because I would like to make sure that any users with public rights do not have access to SYSXLOGINS, SYSDATABASES, SYSOBJECTS, SYSCOLUMNS and mswebtasks tables. Thanks
-Kyle
A Query To Determine A Role Or Id's Access To An Object... Do-able?
Oct 19, 2007Hey there.
I must write a stored procedure to update a table and want to make sure the ID used in both Acceptance and Production will have the necessary access. The ID will inherit it's access to the resource though a ROLE.
Wondering if there are queries out there that can traverse for example, the roles/id's that have access to it... Thx!
Database History
Jan 14, 2008hello,
I'm am looking for a smart solution for keeping history of changes in records in my sql database. Not only history of a record but also which user caused the change and when (I have the username in session).
for now I added 4 fields to each table: "created by","created on","cancelled by",cancelled on", when the user create a record the 2 first fields are filled and and a user delete a record the two last fields are filled and the record is not really deleted but it won't be shown again (I'm using "where createdby is null").
this solution is taking the performance down and it does not solve the editing record tracking. creating new records and cancelling old ones for editing/changing solve that but than I m having problems with the primary key and relations to other tables.
I tried also to use a table that holds records that descirbes every change in records in my tables. it's very hard to search old data that way.
I know that oracle databases has a smart solution for history... please someone advise me..
Elad.
Storing History In Database
Nov 25, 2003Does anyone currently do this. I want to store an audit history for changes made in the database through our web application.
I would like some kind of history table structure. I would be interested to see if anyone else has done this and what your structure looks like.
ScAndal
Database Restore History
Jul 16, 1998When I go to the database restore tab the "backup history" window shows the database backups and log backups from all the way back when the database was initially created. How do I purge the old database and log backup entries?
View 1 Replies View RelatedTrading History Database Help
Sep 27, 2007I'm creating a db to be used to store historical trades which will then be used to report on various strategies/returns etc...
I'm struggling with the correct way to store things like price etc...
Right now I have a table tStock which has pkStockId,StockName,Sector
I have a table tPrice which stores the historical prices which has pkCounter,fkTickerId,fkDate,Price,Volume. This seems odd to me since dates are being repeated. Right now I have about 100 stocks in table tStock and the tPrice table has 10 years of price data for each stock so there are a date range of historical prices which is approximately 252,000 records. That isn't a ton but I will probably end up with 500+ stocks and 20 years of data. Am I storing this correctly? Right now I have the pkStockId indexed and the pkCounter index and I also created an indes using fkTickerId and fkDate. Is there a way to create the tables such that looking up a price is faster? The reason I would like it to be faster is that when analyzing the trade history I need to get the portfolio market values based on various dates so at a certain date I will sum all buys - all sales for every stockid in the portfolio table and then look up the prices for each to get the market value of the portfolio on a particular day. I feel like I am missing something in the design process but don't have experience in this so I am asking for help.
Thanks
How To Determine Last LSN For Database
Feb 13, 2008
What is the most reliable way to determine the last LSN of a database? I've looked in sys.database_files to no avail. I've also looked in msdb.dbo.backupset which is accurate but only based on backups already performed not the current state of the database.
-joe
Need To Determine If Records Are Already In Database
Feb 21, 2001Problem: I can add 4 items to a SQL 7.0 database but I need to redirect the user if all 4 items exist in the same Record elsewhere in the Database.
Clearly I need some kind of statement like "if category1 = var1 AND category2 = var 2 AND..." but don't know where to put it.
Code:
strSQL = "SELECT * FROM tblUserQuarter"
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open strSQL, objConn,adOpenStatic , adLockOptimistic
'Insert choices from report.asp into tblUserQuarter
objRS.AddNew
objRS("cboFY") = Request.Form("cboFY")
objRS("Quarter") = Request.Form("Quarter")
objRS("ID") = Request.Form("ID")
objRS("Report") = Request.Form("Report")
objRS.Update
objRS.Close
Set objRS = Nothing
objConn.Close
set objConn = Nothing
How To Determine Size Of Database
Feb 25, 2015I ran a query to see how big my database on my SQL Server is and do not understand the metrics. I want to see how much of my 10GB database is left.
database_namedatabase_sizeunallocated space
ANTHEMQ 6521.06 MB243.55 MB
reserveddata index_sizeunused
1332304 KB1328168 KB1800 KB 2336 KB
How Can I Determine The Size Of A Database?
Mar 8, 2007Simple question...
When I use the full version of SQL Management Studio, I can run a report to view the size. How can I do this with SQL Mgmnt Express?
Determine Date Database Was Last Used ?
Jun 15, 2006Hi There
I am trying to establish the date a database was last used.
At first i checked out sp_helpfile and sysfiles to see if there was a date last modified, i also tried using xp_cmdshell to check the date on the actual .mdf or .ldf on the o/s but if the file has not grown the date will be the create date.
I need to establish the date a database was last used, either the last time someone logged into it or the last time any sort of command was run against the database, i cannot use sysprocesses as there are no connections to the database, so i need to determine the last time there actually were any processes run against this database.
And i need to use tsql, checking audit logs etc is not viable, can anyone help ?
Thanx
DB Engine :: How To Enable Remote Access
Jun 8, 2015On my SQL Server 2014 SP1 on Windows Server 2012 R2, allow inbound TCP 1433 and UDP 1434 seems to not be enough for managing remotely any named instance on this server.
View 8 Replies View RelatedSQL 2012 :: Determine Size Of Database
Sep 17, 2015I am working on Sql Server 2012. and I have multiple databases there. Out of those, i want to move one of my databases to other SQL server 2012, For that i was trying to get approximate size of my database on current server. As i don't have the admin rights, so i can't get that. Can i get the approximate size by right clicking on database and by using the size property Under Database category to get the size idea?
View 4 Replies View RelatedDetermine When A New Record Is Added To A Database
Dec 31, 2007I have a SQL database that gets populated by another program. Is there a way to determine when a new record is added to that database?
Thanks
How To Determine Sp Caller Current Database?
Aug 23, 2007When executing a stored procedure that is defined in another database, as:
USE db1;
EXEC db2.dbo.sproc;
Is there a way in the stored procedure "sproc" to determine that the caller made the call from db1?
DB Engine :: User Access Reverting Back
Aug 3, 2015I have user called DBA_USER he has db_owner access on (123DB) database ....
But when ever my machine restarted the user was getting access error (i.e.. you don't have access on this database) and i checked his access on that particular database his access got reverted back ....
Here my question is why this particular user getting this type of error ....
DB Engine :: Cannot Access Destination Table - Appears Sometimes
Apr 23, 2015I have a simple powershell script, that is writing single row to remote SQL server as sort of keepalive solution for branch machine. It opens System.Data.SqlClient.SqlConnection, SqlBulkCopy writes table, and closes connection.
Sometimes script throws an error in form of - Cannot access destination table.
We are not managing internet connectivity in that place, so I don't know for sure, but can this error be caused by connection timeout that comes when script tries to write ?
SQL 2012 :: Database Failover History In AlwaysOn Group
Jun 16, 2014If there is a history kept somewhere of failover events of a database in an AO group? I have 2 replicas with automatic failover and I'm looking for a history of failovers.
View 0 Replies View RelatedSQL Server 2014 :: Possible To Determine Dataflow Within A Database?
May 14, 2015Is it possible to determine the data flow within a database? e.g. I start by adding/inserting a record in MainTable1, then through a trigger SubTable1, SubTable2 have records inserted into, SubTable3 has a record updated into, then a StoredProcedure1 is run which updates first SubTable4, then a StoredProcedure2 is run which adds a record in SubTable5. So by simply adding a record in MainTable1, I affect 5 other tables.
Imagine a more complex process than this one now (which I'm currently facing and I want to win time and precision; I will still investigate it myself (if I will find an answer first - I will post it here)) Is there a way I could "extract" this flow by using T-SQL?
How Do I Determine The Backup Date Of A Restored Database..?
Feb 1, 2007Hi All,I have to a few backups restored from a set of backup files. Thebackup files have been removed from the drive because of some storageconstrains...For some data comparison reasons, I need to figure out the actual dateof these backup files that is used to restore these databases. Isthere any system table I can query to figure the actual backup timesof these databases...Any help will be much appreciated.. Thanks in advance.- Aravin
View 1 Replies View RelatedCannot Determine How To Set Owner To Install Database Design
Jun 14, 2007My compatibility settings are fine. Anyone know how to to this?
View 4 Replies View RelatedDB Engine :: Spid Is Locking Out Access To Logins In SSMS
Aug 11, 2015SQL2008R2...Have a SPID that started yesterday and contained a command to drop a server audit. This did not finish (after 3hrs) so I killed the SPID. Problem is it's still there (sp_who2) and I think it's now blocking access to viewing editing logins within SSMS as I'm getting "Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)" when double clicking a login.Do I need to restart the instance as killing the "alter server" transaction (SPID=62) is not working? Code to show blocking transactions returns this.
View 12 Replies View RelatedDB Engine :: Backup On Remote Server - Access Error
Nov 12, 2015I am trying to make my maintenance plan to backup on a remote server but it fails with:
"failed with the following error: "xp_delete_file() returned error 2".
I am pretty sure its because it doesnt have full access to the share folder... My question is, how can I grant access to it, if I am using a local account as sql agent service: NT ServiceSQLSERVERAGENT.I need to change the service account?
SQL Security :: How To Determine What Certificate Is Used To Encrypt A Database In 2014
May 28, 2015I use a number of certificates on my database server. I'm trying to verify the correct certificate is being used on the correct database.
View 3 Replies View RelatedWay To Determine Which Logins Have Write Permissions To Database On Sever
Nov 15, 2007Hi there,
Does anyone know a way (a query perhaps?) to determine which logins have write permissions to a specific database on SQL Server 2005? Ive thought about joining sys.syslogins & sys.sysusers but looking at the columns, not sure which one would render me that info.
Any help is appreciated. Many thanks!