We help IT Professionals succeed at work.

Multiple Users In MS SQL Server CE 3.5

I've been Googling this for a couple of days now and still can't find the answer to my question, so I figured, who better to ask than the Experts?  ;-)

Anyway, I'm developing a couple of projects (under VB.NET) where I'm strongly considering using MS SQL Server Compact Edition 3.5 as the database backend.  So far, it's seemed perfect for my needs - lightweight, tiny footprint, fast, database creation on the fly - but there's one thing I can't seem to figure out for some reason.  Perhaps I'm just dense and this is a totally moronic question, but here goes anyway:

All of the documentation I've read so far indicates that this version of SQL Server CE is capable of multi-user support, however there's no mention of any form of actual login credentials associated with the DB itself.  Sure, it talks about security and permissions taken from the current Windows user login, but I was really kinda wanting to setup multiple user "accounts" for accessing the database.  Not that revolutionary a concept in most any DBMS out there, including a lot of the smaller ones, but nothing I've found so far seems to give me any hope that this little bad boy is going to do that.

From what I can tell from the documentation, this "multi-user" support is more of a "multiple simultaneous connections" support, enabling more than one user to connect to the database at the same time.  The problem?  I'd like to be able to assign each user their own unique password to the database, but with this structure, that doesn't seem to be possible unless I either a) leave the database itself unencrypted, or b) hard-code the database password into the application, then have the actual user's login credentials stored in a table I create myself.

Maybe I've overlooked something, and perhaps I'll have to simply create multiple, separate and individual database files for each user - which I don't suppose would be too awful - but I'd at least like to know I tried all I could think of to get my "desired" goals.

Thank you, Experts!  I look forward to what you all have to say.
Watch Question

It seems to me (after doing a bit of reading) that CE is built primarily to go ont to mobile devices, then be synchronised back to a central database.
As you can only have one user on a mobile device there is no user security.
Thats what I take away from it.
The next edition up is SQL Express. It definitely has user level security but it is a service based database.


Yeah, that was kinda the feeling I was getting when I was doing my research too.  It looks like CE is still going to be the best choice for the projects I'm working on, even though they're Windows applications.  I just may have to be a bit more "creative" when it comes to the user-level security I want to implement.

Thanks for the response.  Time to throw the "box" out of my thinking.  :)