How to manage security with Access FE and SQL 2005 Express BE

I have upsized my Access 2003 database to SQL Server 2005 Express so that al
l
the tables are now reside in SQL Server; forms, queries and report are still
in Access, but how do I manage users now?
Do I setup users on the SQL Server or maintain those which I have set up in
Access using the security wizard.
It is important that users have no direct access to the tables but
unfortunately they can still do so in Access. I do not wish that they amend
or edit them directly. I am hoping that I could prevent this with the SQL
server, but I don’t know how to and then how do I connect the security of
the
SQL server with that of the Access database.
Please help.Hi,
yeah you can manage users the same as you done in MS ACCESS
1). Create SQL Users
2). Create Roles
3). Assign proper privileges to Roles
4). Map the users to that Roles respectively
and refer http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=24
:-)
Regards
--
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
"Lisa Tanenbaum" wrote:

> I have upsized my Access 2003 database to SQL Server 2005 Express so that
all
> the tables are now reside in SQL Server; forms, queries and report are sti
ll
> in Access, but how do I manage users now?
> Do I setup users on the SQL Server or maintain those which I have set up i
n
> Access using the security wizard.
> It is important that users have no direct access to the tables but
> unfortunately they can still do so in Access. I do not wish that they amen
d
> or edit them directly. I am hoping that I could prevent this with the SQL
> server, but I don’t know how to and then how do I connect the security o
f the
> SQL server with that of the Access database.
> Please help.
>|||Andy
Thanks for your reply. I am completely new to SQL Server so it wasn't so
obvious to me. I think I understand it a little better now.
"Andy Davis" wrote:
[vbcol=seagreen]
> Hi,
> yeah you can manage users the same as you done in MS ACCESS
> 1). Create SQL Users
> 2). Create Roles
> 3). Assign proper privileges to Roles
> 4). Map the users to that Roles respectively
> and refer http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=24
> :-)
> Regards
> --
> Andy Davis
> Activecrypt Team
> ---
> SQL Server Encryption Software
> http://www.activecrypt.com
>
> "Lisa Tanenbaum" wrote:
>

How to Manage Security in SQL Express (2005 CTP)

Is there a tool or mmc snap-in to manage security for Sql Express? I've got the express manager (XM) but it doesn't it doesn't provide a means to manage logins.
I suppose I could resort to T-SQL, but it seems that there should be a tool somewhere for such an obvious function.Nevermind.. I just resorted to sp_addlogin...|||Try the following link:
http://www.microsoft.com/downloads/details.aspx?FamilyId=C7A5CC62-EC54-4299-85FC-BA05C181ED55&displaylang=en

how to make the SQL Express accessible via local IP?

i am using the SQL Server 2005 Express and I want to access it using the local PC IP instead of PC name.

You should be able to just swap the machine name for the ip address and it will work.sql

How to make distributed query by SQL Express

I am going to make a distributed query, but in the query design view I cannot add the tables from another server, why?

hi,

AFAIK, the designers only show objects local to the connected server (and database)... but you can easely insert the required code pointing to remote linked servers...

regards

|||Thanks, i will try.

How to make a database in Sql2005 Express

I have installed the Sql2005 Express.
But there are not any tools like Enterprise Manager in it.
How can I establish a database in it?
You can use Microsoft SQL Server 2005 Express Manager as a database
management tool for SQL Server Express 2005.
http://www.microsoft.com/downloads/d...displaylang=en
- Peter Ward
WARDY IT Solutions
"ad" wrote:

> I have installed the Sql2005 Express.
> But there are not any tools like Enterprise Manager in it.
> How can I establish a database in it?
>
>

How to make a database in Sql2005 Express

I have installed the Sql2005 Express.
But there are not any tools like Enterprise Manager in it.
How can I establish a database in it?You can use Microsoft SQL Server 2005 Express Manager as a database
management tool for SQL Server Express 2005.
http://www.microsoft.com/downloads/details.aspx?familyid=8f92556a-6c3b-47d2-9929-ecdc5a4d25ae&displaylang=en
- Peter Ward
WARDY IT Solutions
"ad" wrote:
> I have installed the Sql2005 Express.
> But there are not any tools like Enterprise Manager in it.
> How can I establish a database in it?
>
>

How to make a database in Sql2005 Express

I have installed the Sql2005 Express.
But there are not any tools like Enterprise Manager in it.
How can I establish a database in it?You can use Microsoft SQL Server 2005 Express Manager as a database
management tool for SQL Server Express 2005.
http://www.microsoft.com/downloads/...&displaylang=en
- Peter Ward
WARDY IT Solutions
"ad" wrote:

> I have installed the Sql2005 Express.
> But there are not any tools like Enterprise Manager in it.
> How can I establish a database in it?
>
>

HOW TO LOGIN IN TO SQL EXPRESS AND HOW TO ATTACH DATABASE PLZZ ITS URGENT

Hi

i am developing an installer for our produce where there is a need to install sqlexpress and then i have to attach the database to that but i am unable to understand how to do that , i any one can help in this issue plss

To install SQL Express you need to include the installer and then pass command line parameters to it. This link will help http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/EmSQLExCustApp.asp

Now to attach a database, you need to create the database, then detach it, now you have an .mdf and an .ldf file. You need to include that in the installer, then once SQl Express is installed on the customer machine you need to re-attach it, you can do this by using the sp_detach, called through sqlcmd on or a sqlclient sqlcommand object.

How to lock out everyone but me?

I'm working with SQL Server Express, and I want to configure a named instance so that only the 'sa' user and a specified SQL Server user with a specified password have access. In particular, I'm trying to lock out BUILTIN\Administrators. Furthermore, I need to be able to do this from a command-line, since I want to configure it in a script. Nothing I do seems to work.

I've attempted to use sqlcmd and the T-SQL call ALTER LOGIN [BUILTIN\Administrators] DISABLE, but that returns the error "Cannot alter the login 'BUILTIN\Administrators' because it does not exist or you do not have permission."

What I can (apparently) successfully do is run DENY CONTROL TO [BUILTIN\Administrators]. This runs without reporting an error. However, after running it against the 'master' database and the specific database in my named instance I care about, I can still run the following:

sqlcmd -S (local)\MyInstance -d MyDB -Q "select * from my_table"

and see the contents of my_table.

What do I need to do to restrict access exclusively to 'sa' and other SQL users I designate?

Have you tried simply dropping the builtin\administrators login?

"Drop login [builtin\administrators]"

You can check sys.server_principals for a list of all logins. This seemed to work for me, although I tested this on Developer Edition and not Express Edition.

Sung

|||

I think the right expression would be to deny connection to builtin administrator under normal circumstances.

I want to clarify that actually stopping a box administrator from accessing SQL Server instance (and any other service for that matter) is practically impossible as your adversary has control over the OS and most likely the hardware.

You need to trust the box admin to some extent, and a proper channel and policies to monitor unauthorized activities (i.e. company policies, box auditing, etc.) should be in place to keep a box administrator honest. If you are hosting any data on a machine where you don’t trust the box administrator, make sure you don’t store (and I would even say don’t manipulate) any valuable information in clear text on the system you don’t trust.

If your objective is to keep an honest box administrator honest and deter her from accessing SQL Server, revoking access as Sung mentioned should be enough. In SQL Express you additionally would have to drop the [Builtin\users] account. SQL Express is designed OOB to grant access to the instance (with low privileges) to any user in the system in order to take advantage of some SQL Express-specific features.

BTW. Keep in mind that in SQL Server 2005 it is sufficient to start the server in single user mode. The reason for this is to allow a box admin to recover the system in case of emergency.

I hope this information will be useful.

-Raul Garcia

SDE/T

SQL Server Engine

How to link ASP with SQL Express 2005?

Hi, I am trying to link my ASP project with my SQL server but ASp 2005 is pretty different from the earlier version. I don't even see any Data Adapter. I set all my rights for my SQL server so I believe that all I have left to do now is to link them together. I hope someone can help me.It's not that different at all from previous versions. You can check outthese tuts to guide you through it.

how to link Access with a .mdf file?

Hi,
i want to import data from a stand-alone .mdf file.
I use sqlserver express 2005 (windows xp prof).
In sqlserver, i attachted the .mdf file.
Then I created an odbc link, but when i try to import data from Access, i
only see the master database, not the tables of the .mdf file.
How can i import data from a .mdf file into Access?
Thanks for help
BenSQL Server Express (and in common 2005) has secured metadata, meaning
that you will only see metadata you are priviledged to. Seems that the
account you are using to connect to the datbase does not have the
appropiate permissions to access the database.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--