Re: Install MSDE w/ MSDE Depl.Toolkit. What permissions when using Win Auth?

From: CodeMonkey (agaskelluk_at_yahoo.com)
Date: 12/02/04 

Date: 2 Dec 2004 04:47:01 -0800

Hi Andrea
Here's what I did.

I created a login using Enterprise Manager on SQL Server on my server.
The login is MyDomain\MyUser. On the General tab I set the default
database as master and the default language as English. I set the
security access to Grant access. I didn't set any server roles. On the
Database Access tab I ticked the tick box for the database that I
wanted MyDomain\MyUser to have access to. Lets call it MyDatabase.

I then selected MyDatabase under Databases on the server and then I
selected the publication that I had created for this database, under
Publications. I right clicked the Publication and selected Properties
And then I added MyDomain\MyUser to the PAL (publication access list).

Now when my user installed the MSDE database on his machine locally, he
is able to access records AND he can initiate an anonymous PULL
subscription to the database on the server. Great it works!

**First Question**
As discussed, I don't want to have to add every single user like this.
I looked for a group like "MyApplicationUsers" or"TravellingUsers" but
these don't exist. I suggest the following. Can I instead make a
windows group on my server called MySQLUsers and add to this
NTAuthority\Authenticated users? Then instead of creating a login for
MyDomain\MyUser and adding MyDomain\MyUser to the PAL, I can create a
login for MySQLUsers and add MySQLUsers to the PAL. Is this OK? Are
there any issues with this?

**Second Question**
MyDomain\MyUser is able to access the MSDE database locally and return
records even though I haven't given him a login or any permissions in
the MSDE database that I distributed. Why is he able to do this?

Thanks again for your patience.

Regards
Andrew

Andrea Montanari wrote:
> hi Andrew,
> "Andrew Gaskell" <agaskelluk@yahoo.com> ha scritto nel messaggio
> news:63c811db.0411301019.47b2436e@posting.google.com
> > Andrea
> > ok please consider this scenario, as I am still not clear on this.
I
> > have my SQL database sitting on my server. I stop replication and
> > detach it. Then I copy the ldf/mdf files to my dev machine. Then I
> > deploy the database using the MSDE deployment toolkit and some
adapted
> > code as per previous.
> >
> > I then have my clients who use MSDE to connect *locally* to the
MSDE
> > version of the database. They need to be able to work offline, no
> > network connection. A service running on the client machines will
> > start a pull subscription for replication every x days if the user
is
> > connected to the network.
> >
> > I then get a new user called MyDomain\Andrea who wants to install
the
> > MSDE database version and be able to run the service which starts a
> > pull subscription. The new user must also be able to run queries on
> > the local MSDE database.
> >
> > Do I need to create a group on the server that houses SQL Server or
> > where? I don't have access to the domain controller. What
permissions
> > do I give to this group? How do I create the group?
>
> on your DC you probably have a group for "MyApplicationUsers" or
> "TravellingUsers"... :)
> on your SQL Server you grant login to that group and grant db access
too...
> so MyDomain\Andrea will be parto of MyDomain\TravellingUsers group
and will
> inherit, on that server, all privileges you set for them...
>
> >
> > Once this group has been created and MyDomain\Andrea added, if you
use
> > the MSDE database offline - no connection to the network, how will
> > authentication take place? What credentials will be used?
>
> on each MSDE diconnected instance you should create the needed (NT)
user,
> grant him/her login and dbaccess...
> so you have to grant MyComputer\Andrea login to the diconnected
instance and
> grant me db access
> as you already created the
> YourDatabase\TravellingUsers
> database userdefined role, you only need to add me
(MyComputer\Andrea) to
> that database role...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
> DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a
visual
> interface)
> --------- remove DMO to reply

Relevant Pages