Restricting where a database can be restored too.
-
Hi Guys.
Is it possible to restrict a database to only be allowed to exist under a certain SQL Server Instance. I ask as I am responsible for one of our customers Live databases that is currently housed on a development server and is freely accessible to our developers. We have a contract in place that only allows the live data to be used for development and testing when we have prior approval from this customer. Unfortunately we have a small company mentallity and some of our more senior developers have a habit of copying databases for their own use in problem determination which is not allowed.
I am looking for a way to stop them from backing up and restoring the database's to other locations. Most have full dbo access.
Steve
-
No, you cannot as far as I know. Think of restricting the backup priviledge only to you, no one else. Also adding password to your backup files so people cannot restore without the right password.
HTH,
Jeff
-
I don't know how to restrict the db file restore, but I know of a way in which to restrict the access to those file.
Remove all developer from server access for the windows side. This mean no user/developer/dbo has access to the server.
Lock your server up by making sure that 'everyone' has no access to any disk. Please remember to give 'administrators group' full privelege.
2. re-check all their login to make sure they don't have sa privelege or any other server privelege.
3. assign them dbo as their job needed.
mom
-
It's got to be at least as tough as mom says.
- If I can create a backup of your database, and create it on a volume I can access, and restore it on my own instance of SQL Server [once I wrangle out all that logical file name and "move" junk...]
- If I can detach the database, the files can be copied
- If I can stop SQL Server, I again may have access to the files.
Once I get those data files (in whatever format), I can do anything with them on my own SQL Server installation.
So you'd have to to any or all of the following:
- Deny me backup permissions
- Keep me from dettaching databases
- Prevent me from stopping the service
- Deny me access rights to the folders storing the files and backups
- Putting passwords on backups can work, but I've heard that's hackable and the data's written "in the clear" anyway.
- Encrypting backups is good, but SQL Server doesn't do that natively
Lockdowns like this are rarely feasible on Development servers, and hard to implement fully elsewhere.
A thought: I once was required to copy Production data to Development for testing. This bugged me, as passwords and credit card numbers were stored therein. I wrote a routine to generated new passwords and CC numbers after I loaded the database but before I exposed it to the users. (Amazing what you can do when you get creative with NEWID()...)
Philip
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply