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


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?

Mike Stover

"John Beschler" <JohnBeschler@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
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
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
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
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,

"Uri Dimant" <urid@xxxxxxxxxxx> wrote in message
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:// --------security
best practices

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

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

Thank You,


Relevant Pages

  • Re: Fresh installation and no access
    ... You're not getting access because you're not an administrator when you log ... In previous OS's you had administrator permissions on the database ... You must create a login (to allow connect to SQL Server) then a user to be ...
  • Re: Add node to cluster
    ... I'm installing as Domain Administrator so permissions shouldn't be a barrier ... as well as the account the cluster is ... "SQL Server 2005 Setup requires administrator permissions on every cluster ...
  • Re: SQL Server Upgrade MSP Error: 29538 on KB934458 Upgrade
    ... I had this kind of problem once and it was because of the Windows permissions. ... I suggest you to check your Windows account's permissions on setup folders. ... and either grant administrator permissions to the account currently ... Then run SQL Server ...
  • RE: Could not open global shared memory to communicate w/ performa
    ... If look at my post titled Permissions Problem and dated 12/13/2008, ... db_ssisoperator and I granted db_ssisoperator the Reader Role via the Package ... On the SQL Server box, open Programs, SQL Server 2008, COnfiguration Tools, ... I am logged in as domain administrator yet still have ...
  • Re: Set perissions on stored procedure
    ... stored procedure for one of my database roles, ... stored procedure and choosing All Tasks -> Manage Permissions. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...