Database catalog unique identifier

I need to develop a strong licensing solution based on the database accessed...

Currently our solution is easily hackable because the the license information is kept in the database of your choice and is not dependant on some static information, the current encryption key is static and kept in the software so it can be hacked easily. What i want to do to change this is simple in nature but i don't know how to get that one particular info i need.

I want to get some kind of unique identifier for a database (catalog) that sql server could generate. This info must be static and must not be movable. If for example, someone would backup and restore, this information would not be transfered with the backup. Thus, a user that backups his database and restores it on another database server or another database catalog even on the same server would corrupt his license since the Unique ID returned by the SQL Server would be different; the hashing code would change and thus the decryption would fail.

Is there any such info i can query using SQL that will not require admin rights?

after searching a bit more in the help i found out there was something i could use in sys.databases.

I was thinking of using a combination of the database name, the id column and the creation date to create the key but then it struck me, if someone wants to cancel his licensed solution, i can do wathever process i want, he can just restore it and the license is back online. Thus, for the original question, i'm pretty much done and everything is ok, but now i need to know if there is somewhere in the sys.* that i can find the last restore date so i can prevent database restore from happening...

|||

after even thourough searching even after work hours, i finally found it (8 hours on the subject is enough i think).

Here is the code that will do what i am searching for

SELECT
d.database_id,
d.name,
d.create_date,
r.restore_date
FROM sys.databases AS d
LEFT JOIN sys.database_files AS df ON df.name = d.name
LEFT JOIN RestoreHistory AS r ON r.destination_database_name = d.name
WHERE
d.name = '[DatabaseName]'

I hope this will benefit someone else and not just me

database access statistics?

Is there a simple way to see when the last time a
database was accessed?
Best,
Adria
a-carbo@.tamu.eduSQL Server can log event information for logon attempts and you can view it
by reviewing the errorlog. By turning on the auditing level of SQL Server.
follow these steps to enable auditing of all/successfull connections with
Enterprise Manager in SQL Server:
Expand a server group.
Right-click a server, and then click Properties.
On the Security tab, under Audit Level, click all/success etc(required
option).
You must stop and restart the server for this setting to take effect.
you can make use of profiler as well by running trace.
--
-Vishal|||go to the errorlog file which can be found at
$\Program Files\Microsoft SQL Server\MSSQL$<instance>\LOG
and open the errorlog file , the lastest errorlog file will not have any
extension. Every time SQL Server is started, the current error log is
renamed to errorlog.1; errorlog.1 becomes errorlog.2, errorlog.2 becomes
errorlog.3 and so on you will have to scroll through these errorlog files to
see which user has logged in at what time. This method will be useful if you
want to track "database connection" made by selected users who are specific
to the particular database only. System administrators like 'sa' can logon
to any of the database. Hence you can not exactly track database access on
the basis of database connections but, on the basis of "user connections"
made.
If you purely want to check the database connections you will have to make
use of SQL Profiler. Create a new trace with the events under "Security
audit" >> "audit login" . Under data columns include "database name". and
run the trace.
--
-Vishal
"Adria" <a-carbo@.tamu.edu> wrote in message
news:1c6f01c372f3$390c6980$a501280a@.phx.gbl...
> I just checked the Properties on the server and found
> that the Audit Level was already set to all/successes.
> How then do I view connections?
> FYI ... this is a database that I suspect has not been
> used for some time.
> Best,
> Adria
> >--Original Message--
> >SQL Server can log event information for logon attempts
> and you can view it
> >by reviewing the errorlog. By turning on the auditing
> level of SQL Server.
> >
> >follow these steps to enable auditing of all/successfull
> connections with
> >Enterprise Manager in SQL Server:
> >
> >Expand a server group.
> >Right-click a server, and then click Properties.
> >On the Security tab, under Audit Level, click
> all/success etc(required
> >option).
> >You must stop and restart the server for this setting to
> take effect.
> >
> >you can make use of profiler as well by running trace.
> >
> >--
> >-Vishal
> >
> >
> >.
> >|||Login attempts won't tell you if a database has been used or
not. It will only tell you about server logins, not database
access. You will need to monitor this yourself with profiler
going forward. I don't think there is any way to get
historical usage if you haven't been monitoring for such.
-Sue
On Thu, 4 Sep 2003 07:45:45 -0700, "Adria"
<a-carbo@.tamu.edu> wrote:
>I just checked the Properties on the server and found
>that the Audit Level was already set to all/successes.
>How then do I view connections?
>FYI ... this is a database that I suspect has not been
>used for some time.
>Best,
>Adria
>>--Original Message--
>>SQL Server can log event information for logon attempts
>and you can view it
>>by reviewing the errorlog. By turning on the auditing
>level of SQL Server.
>>follow these steps to enable auditing of all/successfull
>connections with
>>Enterprise Manager in SQL Server:
>>Expand a server group.
>>Right-click a server, and then click Properties.
>>On the Security tab, under Audit Level, click
>all/success etc(required
>>option).
>>You must stop and restart the server for this setting to
>take effect.
>>you can make use of profiler as well by running trace.
>>--
>>-Vishal
>>
>>.|||Vishal, Database Name is not one of the available columns
in the SQL Profiler, unfortunately. I'm trying to find
out the same thing on one of my servers and haven't found
a clean way to make the determination. (My latest RFI is
in subject "Determine database no longer in use - Redux"
If there was some time of condition that could be
monitored to determine if an application opens a
particular database it would help tremendously. Right now
I'm trying to capture "Active Transactions" because that
seems to be the closest I can find to 'real' usage. If
there's another one you can think of I'd be much obliged.
Allen
>--Original Message--
>go to the errorlog file which can be found at
>$\Program Files\Microsoft SQL Server\MSSQL$<instance>\LOG
>and open the errorlog file , the lastest errorlog file
will not have any
>extension. Every time SQL Server is started, the current
error log is
>renamed to errorlog.1; errorlog.1 becomes errorlog.2,
errorlog.2 becomes
>errorlog.3 and so on you will have to scroll through
these errorlog files to
>see which user has logged in at what time. This method
will be useful if you
>want to track "database connection" made by selected
users who are specific
>to the particular database only. System administrators
like 'sa' can logon
>to any of the database. Hence you can not exactly track
database access on
>the basis of database connections but, on the basis
of "user connections"
>made.
>If you purely want to check the database connections you
will have to make
>use of SQL Profiler. Create a new trace with the events
under "Security
>audit" >> "audit login" . Under data columns
include "database name". and
>run the trace.
>--
>-Vishal
>"Adria" <a-carbo@.tamu.edu> wrote in message
>news:1c6f01c372f3$390c6980$a501280a@.phx.gbl...
>> I just checked the Properties on the server and found
>> that the Audit Level was already set to all/successes.
>> How then do I view connections?
>> FYI ... this is a database that I suspect has not been
>> used for some time.
>> Best,
>> Adria
>> >--Original Message--
>> >SQL Server can log event information for logon attempts
>> and you can view it
>> >by reviewing the errorlog. By turning on the auditing
>> level of SQL Server.
>> >
>> >follow these steps to enable auditing of
all/successfull
>> connections with
>> >Enterprise Manager in SQL Server:
>> >
>> >Expand a server group.
>> >Right-click a server, and then click Properties.
>> >On the Security tab, under Audit Level, click
>> all/success etc(required
>> >option).
>> >You must stop and restart the server for this setting
to
>> take effect.
>> >
>> >you can make use of profiler as well by running trace.
>> >
>> >--
>> >-Vishal
>> >
>> >
>> >.
>> >
>
>.
>|||> Database Name is not one of the available columns
> in the SQL Profiler, unfortunately.
This column will not come by default.When you create a new trace go to the
3rd tab "data columns" and add the data column "database name" to the
selected data.
You can run the system stored procedure sp_who2 from query analyzer.
or query sysprocesses table to find users and processes
Ex:
select * from sysprocesses where db_name(dbid) = 'master' --specify
To get the active transaction on particular database you can run a dbcc
command.
dbcc opentran ('<database_name>')
--
-Vishal
"Allen White" <awhite@.advanstar.com> wrote in message
news:1e8101c372fa$8afa0f20$a601280a@.phx.gbl...
> Vishal, Database Name is not one of the available columns
> in the SQL Profiler, unfortunately. I'm trying to find
> out the same thing on one of my servers and haven't found
> a clean way to make the determination. (My latest RFI is
> in subject "Determine database no longer in use - Redux"
> If there was some time of condition that could be
> monitored to determine if an application opens a
> particular database it would help tremendously. Right now
> I'm trying to capture "Active Transactions" because that
> seems to be the closest I can find to 'real' usage. If
> there's another one you can think of I'd be much obliged.
> Allen
>
> >--Original Message--
> >go to the errorlog file which can be found at
> >
> >$\Program Files\Microsoft SQL Server\MSSQL$<instance>\LOG
> >
> >and open the errorlog file , the lastest errorlog file
> will not have any
> >extension. Every time SQL Server is started, the current
> error log is
> >renamed to errorlog.1; errorlog.1 becomes errorlog.2,
> errorlog.2 becomes
> >errorlog.3 and so on you will have to scroll through
> these errorlog files to
> >see which user has logged in at what time. This method
> will be useful if you
> >want to track "database connection" made by selected
> users who are specific
> >to the particular database only. System administrators
> like 'sa' can logon
> >to any of the database. Hence you can not exactly track
> database access on
> >the basis of database connections but, on the basis
> of "user connections"
> >made.
> >
> >If you purely want to check the database connections you
> will have to make
> >use of SQL Profiler. Create a new trace with the events
> under "Security
> >audit" >> "audit login" . Under data columns
> include "database name". and
> >run the trace.
> >
> >--
> >-Vishal
> >
> >"Adria" <a-carbo@.tamu.edu> wrote in message
> >news:1c6f01c372f3$390c6980$a501280a@.phx.gbl...
> >> I just checked the Properties on the server and found
> >> that the Audit Level was already set to all/successes.
> >>
> >> How then do I view connections?
> >>
> >> FYI ... this is a database that I suspect has not been
> >> used for some time.
> >>
> >> Best,
> >> Adria
> >>
> >> >--Original Message--
> >> >SQL Server can log event information for logon attempts
> >> and you can view it
> >> >by reviewing the errorlog. By turning on the auditing
> >> level of SQL Server.
> >> >
> >> >follow these steps to enable auditing of
> all/successfull
> >> connections with
> >> >Enterprise Manager in SQL Server:
> >> >
> >> >Expand a server group.
> >> >Right-click a server, and then click Properties.
> >> >On the Security tab, under Audit Level, click
> >> all/success etc(required
> >> >option).
> >> >You must stop and restart the server for this setting
> to
> >> take effect.
> >> >
> >> >you can make use of profiler as well by running trace.
> >> >
> >> >--
> >> >-Vishal
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

Database Access

Is it possible to get a list of all users that accessed a particular
Sql Server database? ThanksOnly is you had a trace running that captured the user names or logins.
Andrew J. Kelly SQL MVP
"S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
news:1140541552.276198.144260@.g43g2000cwa.googlegroups.com...
> Is it possible to get a list of all users that accessed a particular
> Sql Server database? Thanks
>|||sp_who ?
"S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
news:1140541552.276198.144260@.g43g2000cwa.googlegroups.com...
> Is it possible to get a list of all users that accessed a particular
> Sql Server database? Thanks
>|||No, the current users can be viewed with sp_who or sp_who2 but to see
the users in the past you have to use a log reader to see which user
issued a command against a database.
HTH, Jens Suessmeyer.|||http://msdn.microsoft.com/library/d...>
ity_2ard.asp
http://msdn.microsoft.com/library/d...>
erf_4wl5.asp
"S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
news:1140541552.276198.144260@.g43g2000cwa.googlegroups.com...
> Is it possible to get a list of all users that accessed a particular
> Sql Server database? Thanks
>|||sp_you
"Ben" <bennett@.newsgroups.nospam> wrote in message
news:41773$43fb55f3$d8445835$6596@.FUSE.NET...
> sp_who ?
> "S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
> news:1140541552.276198.144260@.g43g2000cwa.googlegroups.com...
>

Data/Table Last Accessed/Modified

Is anyone aware of a method of determining when a table was last modified or accessed?

Some of our databases have tables that I am sure are not being used and I would like to generate a list of tables that have not been accessed or modified for some period of time.

I looked for a system procedure but didn't see anything that satisfied my need.

Currently I rename suspect tables and wait for someone or some process to gripe, but I don't care for that method for obvious reasons.

Thanks!again .. r u talking about the data or the object?
check what brett says here:
http://www.dbforums.com/t995468.html|||Both actually. I would like to know when someone queries against it or when the data is modified. The trigger method is good for data mods...I can use something similar. But it might be hard see if a table has been accessed or read.

Thanks!|||The only effective way I know to do that is using SQL Profiler (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_mon_perf_86ib.asp). Be forwarned: This can use enormous amounts of disk if you don't set it up very carefully!

This is when I wish that the design team had left the SELECT triggers in place, although that could have been a nightmare too!

-PatP|||That may do the trick. Thanks!