4

I'm writing a tool which is used to perform several database operations.
But the tool should only be used with one specific database.

Now I'm looking for a way, to securely identify the database, the tool is connected to.

First I thought about just checking a string like SERVERNAME\INSTANCE#Database.
Also I found this question where the solution is to use a GUID, but this GUID changes if the DB is restored on another server.

The DB should be recognized even when it is being moved to another server or instance, or if the database name changes.

Is there a reliable way to achieve this?

MatSnow
  • 7,357
  • 3
  • 19
  • 31
  • how about creating a table and reading that info,assuming no one changes the table contents – TheGameiswar Aug 18 '17 at 09:07
  • Probably a duplicate of or at least related to https://stackoverflow.com/questions/2957220/is-there-a-way-in-sql-server-to-uniquely-identify-a-database – Mithrandir Aug 18 '17 at 09:11
  • @TheGameiswar I also thought about this before. But it should be secure from manipulation. – MatSnow Aug 18 '17 at 09:12
  • does table names change or new tables gets added over time – TheGameiswar Aug 18 '17 at 09:16
  • @Mithrandir The mentioned solutions are really near to what i'm looking for but all methods are prone to manipulation. – MatSnow Aug 18 '17 at 09:17
  • @TheGameiswar New tables could be added over time. – MatSnow Aug 18 '17 at 09:18
  • 5
    @MatSnow Well, depending on how many users have the right set of permissions, any method will be prone to manipulation. – Mithrandir Aug 18 '17 at 09:20
  • Only way I see to do this "manipulation free" is by you setting up/verifying the connections to potential databases manually. Especially if the DB can be moved and renamed across servers. – Allan S. Hansen Aug 18 '17 at 09:29
  • 1
    What exactly must be specific in a database? – Serg Aug 18 '17 at 09:29
  • @Serg The database itself. I absolutely understand that a restored DB on a new instance get's a new GUID. Because for the new instance it is like a new DB. I'm just wondering if there is something like a "second GUID" to determine if it originally was the same DB as on the old instance. – MatSnow Aug 18 '17 at 09:38
  • @MatSnow,since server changes wont be so often, you might want to update tool at that point of time – TheGameiswar Aug 18 '17 at 09:40
  • @TheGameiswar Of Course, this is the solution i will use now. But there's more the problem that the tool is not planned to be updated in future. :-) – MatSnow Aug 18 '17 at 09:45
  • You could use the logical name of the database, this doesn't change afaik if the physical name changes with a restore. `select Name from sys.database_files` – Silvermind Aug 18 '17 at 09:47
  • this is an interesting problem,but all solutions are prone to manipulations like Mithrandir suggested – TheGameiswar Aug 18 '17 at 09:47
  • @Silvermind Filenames don't change automatically if you rename or restore a DB, but they can be changed manually. – MatSnow Aug 18 '17 at 09:57

1 Answers1

1

You might be able to achieve this with an extended property.

To create:

exec sp_addextendedproperty @name = 'dbUniqueIdentifier' @value = 'ABCD1234'

To confirm:

select value from sys.extended_properties where name = 'dbUniqueIdentifier'

In my organization, we use extended properties to identify which build and changeset the database schema came from. The properties survive backup/restore/migration.

Matt Perkins
  • 46
  • 4