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

Mike,

If you are using MSDE, you can (I believe) assign the SA password as part of
your install script. Then, when you run the install script, you would also
create any SQL users or groups that you need. If a default installation of
MSDE includes local admins as part of the SQL admins, you can remove that as
part of your install script as well.

Almost anything you can do to an SQL installation can be accomplished via
Transact SQL. If you have a full version of SQL someplace, take a look at
query analyzer for examples of how to script user permissions, roles, etc.



"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









.

Relevant Pages

  • Re: WSS 3.0 question
    ... I followed the advise given in removing WSS 3.0 etc, ... the server is complaining that the SQL service(?) was tempered with or corrupt. ... I may just instal the SQL server as I was going eventuall use it anyway. ... If WSUS 3.0 is installed, I would suggest you uninstall it and then you install WSS 3.0. ...
    (microsoft.public.windows.server.sbs)
  • Re: Trying to Move Group - SQL Instance Wont Start
    ... You can do a maintenance install and add the new nodes. ... "maintaining a failover cluster" for recovery from failure scenario 1. ... Senior SQL Infrastructure Consultant ... Microsoft SQL Server MVP ...
    (microsoft.public.sqlserver.clustering)
  • Re: Unable to Apply SP4 to SQL 2000 Cluster (new Node)
    ... While attempting to install SP4, it comes up immediately after I ... to be on one of the nodes that can bring SQL Server online and apply SP3a, ...
    (microsoft.public.sqlserver.clustering)
  • Re: Any help here???
    ... Well, for starters, I believe MDAC is unnecessary for .NET applications - as ... ADO.NET has its own SQL Server provider for data access. ... > trust me - all that's been done here was to install SP2 or upgrade of XP ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: how do I change the user install wizard to create user folder in different location?
    ... Some people read the instructions in such a way that they install both a new ... instance of sql, ... Never slight the SBS wizards. ... I reinstalled from scratch using the Dell OpenManage Server ...
    (microsoft.public.windows.server.sbs)