Protecting SQL Server Database from system admin?

Question

I am using sql server 2005 enterprise manager to distribute application. how to protect my database from others from editing the data and schema. Kindly note, that I don't even want the sys admin to see my schema or edit the datas. Thanks in advance

Answer 1

Interesting question... I assume you want your admin to back up the data. Which means the admin has to be able to read the information, but not be able to make sense of it... Which means encryption.



Fortunately MS SQL 2005 introduced comprehensive encryption support. I'll list some of the commands -- look them up for more help. If this doesn't work for you, you can use the (more comprehensive) encryption facilities in .NET, or use full-disk encryption for the database of concern.



Full-disk means, of course, you will probably be "outside" the normal backup support matrix. There are some "bump-in-the-wire" encryption solutions, that would work fine (but they are usually expensive, around 10 to 60K to start).



MS SQL ENCRYPTION COMMANDS:



CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'



CREATE CERTIFICATE



EncryptByCert() and DecryptByCert() functions



CREATE ASYMETRIC KEY



EncryptByAsymKey() DecryptByAsymKey()



CREATE SYMETRIC KEY

Answer 2

How can you not have the sys admin have rights to your schema? Others, if given the proper rights, wont have access to your schema, only you and admin does. If you are not the sys admin, then ask the person to make the proper rights,

But something tells me you dont trust the sys admin

;-)

Answer 3

there's a simple solution for that.. :p



make yourself the sys administrator,

then create another account/login

that is less privileged(cant see schema/edit data)

than what you have,

and assign those login to whom you what

to be a "less sys admin" for each assigned db.. :)



hope that helps.. ^_^v

Answer 4

DTS is a collection of approaches you should use to import, export, and rework heterogeneous archives between one or better archives aspects, alongside with Microsoft sq. Server, Microsoft Excel, or Microsoft get properly of entry to. Connectivity is presented by ability of OLE DB, an open-usual for archives get properly of entry to. ODBC (Open Database Connectivity) archives aspects are supported by ability of the OLE DB service for ODBC. You create a DTS answer as one or better programs. each and each equipment may contain an prepared set of initiatives that outline paintings to be finished, transformations on archives and products, workflow constraints that outline pastime execution, and connections to archives aspects and places. DTS programs also furnish facilities, alongside with logging equipment execution information, controlling transactions, and coping with international variables. those approaches are available for starting to be and executing DTS programs: The Import/Export Wizard is for progression particularly common DTS programs, and helps archives migration and easy transformations. The DTS dressmaker graphically implements the DTS merchandise style, allowing you to create DTS programs with a large decision of function. DTSRun is a command-instantaneous application used to execute modern-day DTS programs. DTSRunUI is a graphical interface to DTSRun, which also enables the passing of international variables and the technology of command lines. SQLAgent isn't a DTS application; although, this is used by ability of DTS to agenda equipment execution. utilizing the DTS merchandise style, you may also create and run programs programmatically, construct custom initiatives, and construct custom transformations.