Restrict access on single database from admin user

• webrunner

  SSC-Dedicated

  Points: 31059

  More actions

  August 31, 2016 at 8:34 am

  #1898562

  Eric M Russell (8/29/2016)

  ---

  You'll need to create this database on another instance for which you are the only SYSADMIN. Also, you'll need to enable transparent database encryption (TDE) on this database to prevent the other DBA from copying the .mdf file and attaching it to another instance.

  CREATE CERTIFICATE MySecretCertificate

  WITH SUBJECT = 'Certificate to encrypt MySecretDB database key';

  GO

  BACKUP CERTIFICATE MySecretCertificate

  TO FILE = '\\MySecretFolder\MySecretCertificate.certificate';

  GO

  USE MySecretDB;

  CREATE DATABASE ENCRYPTION KEY

  WITH ALGORITHM = AES_256

  ENCRYPTION BY SERVER CERTIFICATE MySecretCertificate;

  GO

  ALTER DATABASE MySecretDB SET ENCRYPTION ON;

  GO

  Thanks a ton. I understand that the recommended answers have been along the lines of settling this via policy/deciding who is sysadmin in the first place, which I totally understand as the best practice. But your alternative is really creative and can be added as a tool just in case.

  - webrunner

  -------------------
  A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
  Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

• Eric M Russell

  SSC Guru

  Points: 125255

  More actions

  August 31, 2016 at 8:38 am

  #1898564

  webrunner (8/31/2016)

  ---

  Eric M Russell (8/29/2016)

  ---

  You'll need to create this database on another instance for which you are the only SYSADMIN. Also, you'll need to enable transparent database encryption (TDE) on this database to prevent the other DBA from copying the .mdf file and attaching it to another instance.

  CREATE CERTIFICATE MySecretCertificate

  WITH SUBJECT = 'Certificate to encrypt MySecretDB database key';

  GO

  BACKUP CERTIFICATE MySecretCertificate

  TO FILE = '\\MySecretFolder\MySecretCertificate.certificate';

  GO

  USE MySecretDB;

  CREATE DATABASE ENCRYPTION KEY

  WITH ALGORITHM = AES_256

  ENCRYPTION BY SERVER CERTIFICATE MySecretCertificate;

  GO

  ALTER DATABASE MySecretDB SET ENCRYPTION ON;

  GO

  Thanks a ton. I understand that the recommended answers have been along the lines of settling this via policy/deciding who is sysadmin in the first place, which I totally understand as the best practice. But your alternative is really creative and can be added as a tool just in case.

  - webrunner

  My assumption from reading the initial question is that there are multiple people in the role of database administrator, and there is one sensitive database that should not be accessible by all administrators. In that case you practically have to contain the database in a separate instance.

  "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho