Re: How to protect a db from an sa user fetching its schema


"Mark J. McGinty" <mmcginty@xxxxxxxxxxxxxxx> wrote in message
news:OUoCX7OUGHA.1572@xxxxxxxxxxxxxxxxxxxxxxx

"Magnus Österberg" <magnus.osterberg@xxxxxxxxxxx> wrote in message
news:epiDl5DSGHA.4740@xxxxxxxxxxxxxxxxxxxxxxx
On a customer-owned SQL Server 2000, we've got our db placed, together
with other software vendors databases. How can we protect from that the
customer and\or the other companies from fetching the db schema of our
db?

All parties are allowd to login with the sa account. Can we by any means
configure our db such that only we can access the schema, and the sa can
not?

Must all parties (companies) be given individual logins? Then, what about
the sa account? Using this, one is still entitled to the schema of all
databses, right?

Members of the sysadmin fixed server role (which includes the built-in
user sa) cannot be denied access to anything in SQL Server.

But you don't have to be sa to read the schema of a db, because the public
role has select permissions for sysobjects, syscomments and the rest of
the system tables. All aspects of schema can be extracted from there.

You can stop non-sa users from browsing your tables, using stored
procedures or views and delegation/inheritance,

Oops, I meant impersonation, of course, not inheritance.


-Mark


but that may take a fair amount of work, depending on your app, and can't
be used with stored procedures that dynamically generate the SQL they
execute.

You can also encrypt your stored procedures, views, udf's, triggers, user
types and rules, which will keep everyone -- even sa -- from reading or
altering their definitions. There must surely be a way to crack it, for
users with enough skill and time, but this will keep the honest people
honest at the very least.

As for your table structures, as I implied above, they are, afaik,
conventionally unprotectable... otoh, how proprietary could they possibly
be?


-Mark


Any web page were I can read about this?

Magnus





.

Relevant Pages