Re: Deny access to all users (including Administrator and DomainAd



Mike,

One more thing. You should be using stored procedures for ALL data
manipulation and here's why: permissions on stored procedure override
permissions on objects, etc.

So, you can restrict permissions on tables, fields, etc. and grant your
applictaion's user account permissions to the stored procedure which will
bypass the restrictions on the object.



"Mike Stover" wrote:

Hello all. This is the same problem I believe many of us are facing when
distributing MSDE and now SQL Express with a client applicaiton. But, I
think I must be missing something....

I am writing a commercially available software product that can be installed
on a single computer where (in most cases) the user is the local
administrator. This gives them the right to see all tables/schema/data and
modify them. (I realize I can encrypt stored procs, views, etc). The
installed database MAY also be configured to allow other computers to
connect to it (therefore...user instances are out as well as integrated
security since the users may be in a workgroup and not a domain.)

What my perfect world looks like:
BUILTIN/Admins can be left as a login but my database could restrict them to
read-only use of the data.
BUILTIN/Users - same as above.
I would then create an app specific login/user in the database that had
permissions to execute procs, etc.

I just don't see any way that can be done aside from using "sa" to create my
own logins/users and also remove the BUILTIN/ logins. This has to be a
problem for anyone who distributes a client/server app where they have NO
knowledge or control over the computer/network that it will be installed on.
Am I missing something?

Thanks,
Mike Stover




"John Beschler" <JohnBeschler@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:BAD9B85D-EEB6-4812-882D-1982936FC585@xxxxxxxxxxxxxxxx
I recommend you encrypt the confidential information in the database. That
way, even if the administrators have access to the DB, they would not be
able
to view the encrypted data. You would store the decryption key in the
application code. Alternatively, if your application will have internet
access, it could retrieve the decrypt key via a call to a web service
hosted
on your web server. To protect the web service, you would include a
passphrase within the application that is passed to the web service to
verify
valid access before the decrypt key is provided to the application.




"Pradeep Pamidi" wrote:

The DB ships both in SQL Server 2005 and 2000.

Domain Admins and local admins cannot be removed from the administrators
group. But I don't want even them to be to view the DB.

Will try to deny access through the query for SQL Server 2005 and 2000.

Thank You,
PRadeep

"Uri Dimant" <urid@xxxxxxxxxxx> wrote in message
news:%230TILt7LGHA.2416@xxxxxxxxxxxxxxxxxxxxxxx
Hi
Which vesrion of SQL Server you are using?
In SQL Server 2005 you can try DENY CONNECT TO...... or DENY VIEW
DEFINITION (For more details please refer to the bol)
SQL Server 2000
Remove everyone that you don't want rom an Administrator Groups


tp://vyaskn.tripod.com/sql_server_security_best_practices.htm --------security
best practices




"Pradeep Pamidi" <pamidipradeep@xxxxxxxxxxx> wrote in message
news:%23dutfEoLGHA.1124@xxxxxxxxxxxxxxxxxxxxxxx
How can I ensure that databases in a SQL Server instance are
accessible
(and manageable) ONLY through SA account or other accounts created by
SA.
The Administrator or DomainAdmins of the box must be denied access to
the
DB.

Real life scenario: We ship SQL Server DB along with our product. The
DB
contains confidential/IP information and hence must not be accessible
to
anyone except from the application layer (which maintains the required
credentials). MS Access DBs have a database password that could
restrict
access to the DB. How do we achieve it in SQL?

Thank You,
PRadeep









.