i have a database that i use in my application.

the operation i want to prevent is if i stop sql server and take the database files to another location then start the sql server and make some operations then stop the sql server services and replace the database files with the old one and start the sql server: i want now sql server say error and don't open the database

how can i make this situation using sql server 2012?

  • I'm not completely understanding your question, but why would you do it that way? Why not just backup the database, restore it to the second server, and leave the first server alone? – joeqwerty Nov 25 '14 at 19:46
  • you don't understand my question i want to prevent the user of my application to stop sql service , save database files, then start the sql service and make any operation to my database then stop sql then return the database to the old files as his operation not done – امير طارق Nov 25 '14 at 19:53
  • OK, then don't give them Administrator access to the server or SA access to SQL. – joeqwerty Nov 25 '14 at 19:56
  • So... You want to encrypt your database, then? – HopelessN00b Nov 25 '14 at 21:01

If I understand your question, it sounds like you're looking for something like Transparent Data Encryption, in hopes of preventing a user detaching the database, attaching it to their own server, making changes, and re-attaching it to the original server. Transparent Data Encryption would require the user to have a certificate and a private key in order to attach or restore the database to a different server.

This requires Enterprise (or Developer or Evaluation, neither of which is licensed for production use), and I'm not convinced it's 100% foolproof if the user has administrative access to the server. You'd need to prevent the user from having access to export the certificate, for example.

Unfortunately, joeqwerty is correct that you'll need to not give the user Administrative access to the server or SA access to SQL, and will have to block the user's NTFS permissions to the SQL directories, etc.

  • Good point on the NTFS permissions, although the database files shouldn't be able to be copied while SQL Server is running and the database is "mounted", right? – joeqwerty Nov 25 '14 at 20:56
  • No, you're absolutely correct, they shouldn't. They're in use. So the user would have to be able to stop or crash the SQL Service in order to copy the files anyway. Still. – Katherine Villyard Nov 25 '14 at 20:59
  • 1
    Right, but you can't be too cautious, so a good suggestion. – joeqwerty Nov 25 '14 at 21:00
  • 2
    And backups... encrypt those backups you should be taking. – HopelessN00b Nov 25 '14 at 21:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.