Re: SQL 2005 express security issue



The easiest way that probably matches what you were doing before with Access
is:

Create a SQL Server login: MyAppLogin
At MyAppLogin to MyApplicationDB as a member of the db_owner role.

Keep an encrypted password in your application that you can decrypt and use
to log in from the application.

This is not ideal security, since a determined person get get into the
database with MyAppLogin, but the less determined user will simply be able
to use the database through the applicatio. (But this is no different from
Access, where someone with a little bit of work can break into the Access
database.)

If that is enough security to fit your needs, then go ahead.

RLF



"stm" <stm@xxxxxxx> wrote in message
news:ebA%23wwbZIHA.4208@xxxxxxxxxxxxxxxxxxxxxxx
Back to the age of using Access, I can set password and control remote
user access.
Change to SQL Express eventually step backward..............I have a
little regret on my choice!!


"Russell Fields" <russellfields@xxxxxxxxxx> ¦b¶l¥ó
news:eO7R7TPZIHA.4208@xxxxxxxxxxxxxxxxxxxx ¤¤¼¶¼g...
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

  • Fixed
    ... I also found this article that gives the proper way to move system dbs in sql 2008: ... The model exists where the master states it exists. ... This is the error log prior to detaching the model database. ...
    (microsoft.public.sqlserver.server)
  • Re: CREATE AGGREGATE failed because type Concatenate does not conform to UDAGG specification due to
    ... Go to the Database tab and click on the browse button next to the connection string. ... In the New Database Reference dialog, enter the details for the database where you want to deploy the assembly and create the user defined aggregate. ... I'm trying to do some CLR integration with sql server 2005. ...
    (microsoft.public.sqlserver.programming)
  • Re: Logging in irrespective of database access
    ... Sysadmin role members have full permissions, ... SQL Server MVP ... > My problem is that in the Login section of Enterprise Manger I have to> specify that each login has the System Admin ticked under Server Roles> because in SQL 2000 there doesn't seem to a Grant All permissions option> like there used to be in SQL 6.5. ... > What's the point in having the Database Access section if the System Admin> under Server Roles allows you to get into anything?. ...
    (microsoft.public.sqlserver.programming)
  • Re: Logging in irrespective of database access
    ... Sysadmin role members have full permissions, ... SQL Server MVP ... > My problem is that in the Login section of Enterprise Manger I have to> specify that each login has the System Admin ticked under Server Roles> because in SQL 2000 there doesn't seem to a Grant All permissions option> like there used to be in SQL 6.5. ... > What's the point in having the Database Access section if the System Admin> under Server Roles allows you to get into anything?. ...
    (microsoft.public.sqlserver.programming)
  • CREATE AGGREGATE failed because type Concatenate does not conform to UDAGG specification due to meth
    ... Now register the assembly and the aggregate in the SQL Server database you want ... I'm trying to do some CLR integration with sql server 2005. ...
    (microsoft.public.sqlserver.programming)