Hiding tables from database users [closed]

Question

I have a web application using a database in SQL Server 2008. I am the developer of this project and this project has been hosted elsewhere.

I've delivered the project to the administrator. Now the administrator is able to connect to SQL Server but I don't want the administrator to be able to see the database tables.

Is there any way to do this?

Answer 1

I've seen this done before by the makers of ACT. Their product installs a new instance of SQL Server Express and as part of that process they encrypt the sa password for the instance. This makes it 'impossible' for others to connect to the database using anything other than their product and add-on tools. I don't know exactly how they do it, but perhaps you could search for encrypting sa password or something similar and find out how to do it. Shy of installing your own instance of SQL Server I am not sure how you would go about this. Bear in mind that your application will need to then provide the ability to backup, tune, modify, etc the database as the DBA would not have access to the instance of SQL Server.

Incidentally, we threw ACT out once we saw this - I didn't, and still don't, like the idea of a black box running on one of our servers.

In the end, you'll probably find that this added layer of protection (for you, not the client) just isn't worth the aggravation. While you may have proprietary information in the form of the database schema the odds of the client reverse engineering your application and then making their own are slim. Even if they did, it is hard to make good software - they likely wouldn't get it 'right' anyway.

My advice, don't worry about this, focus on making your software great so there is no reason for them to roll their own or look elsewhere.

Answer 2

You cannot limit the rights of an administrator on a server.

The administrator also has access to pretty much all tables that store your encryption keys, so TDE won't work.

If he is a security admin or one of the other roles, that might solve the issue as by default such roles has no permissions on your data.

SQL Administrators normally have these roles to protect your data. They need it. If the data is very sensitive, you need to use alternative means to secure it, such as applying your own encryption before saving the sensitive values. (AES etc.)

Answer 3

Simple: No, he is DBA. He can not do his job without the ability to work with the database. Get over your objections.