Re: SQL 2005 express security issue

stm,

A system administrator such as 'sa' or any other login granted the sysadmin
role can read anything on the server. At a remote location where you do not
control the rights granted on the server, there is nothing you can do about
this.

If your application and the remote user log in with accounts that are not
sysadmins then you can protect databases, tables, stored procedures, etc.

However, within a database there are other roles that, if granted, give
sweeping rights. In a database the db_owner role is like a sysadmin for one
database. It can see and do anything in that database.

To understand what is revealed and to whom here is a write-up from SQL 2000:
http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part3/c1061.mspx?mfr=true

There are a few more roles now in 2005 and things are changing some, since
these roles are a concatenation of several rights that can be individually
granted:
http://technet.microsoft.com/en-us/library/ms175892.aspx - fixed server
roles http://technet.microsoft.com/en-us/library/ms189612.aspx - fixed
database roles

RLF

"stm" <stm@xxxxxxx> wrote in message
news:eTjdSHPZIHA.1184@xxxxxxxxxxxxxxxxxxxxxxx
Hi,

We just revamp our application from Access to use SQL Express 2005 as
database engine, which is a standalone application running on client PC.

I'm now simulating the scenario when I deploy the DB to client, how can I
protect the database (or some tables) from being read by them?

Since this application will distribute to client PC, which is out of our
control, so we must choose SQL authentication.

What I got is, when I setup a SQL express in a new machine, and attach the
database I want to protect, login as 'sa', I can read everything inside,
no matter what & how I set.

Is it true that I can't protect database/table in SQL authentication mode?

Please help.



.

Relevant Pages

  • Re: db_denydatawriter
    ... perhaps this also gives read write access on the database to this user? ... Resrictive permissions overrides in its own level. ... However, if she has sysadmin right, then she'll be able to modify that data. ... Is it possible she has some admin rights which override DenyWriter (though ...
    (microsoft.public.sqlserver.security)
  • Re: permissions required for executing CDOSys stored procedures
    ... We broke our ownership chains many years ago, ... Note that the user database must be owned by 'sa' to maintain an unbroken ... SQL Server MVP ... Apparently only members of the sysadmin role can ...
    (microsoft.public.sqlserver.security)
  • Re: Restricting Access priviledge on SQL2000
    ... The "Application-Administrators" are neither members of sysadmin nor ... their respective database, but as well as to the defaults database (master, ... then make them members of the db_owner role in that database. ... then add the real SQL Server admins specifically to the ...
    (microsoft.public.sqlserver.security)
  • Re: Disable Sysadmin to view metadata in SQL2005
    ... given the fact that they have sysadmin rights to that box? ... If the permissions are not granular enough, ... I think it's View Any Database / View Server State/ View ... the metadata? ...
    (microsoft.public.sqlserver.security)
  • Re: permissions required for executing CDOSys stored procedures
    ... that the stored procedure in the database which in turn calls the CDOSys ... stored procedures is executed via a SQL job - and thus in the context of the ... well before SQL 2000 SP3, by giving each database a different owner. ... Apparently only members of the sysadmin role can ...
    (microsoft.public.sqlserver.security)