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

From: Andrea Montanari (andrea.sqlDMO_at_virgilio.it)
Date: 12/01/04 

Date: Wed, 1 Dec 2004 11:15:52 +0100

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

  • Re: No db access after publishing web site
    ... GRANT UPDATE TO ... If I detach and attach this database on a different PC (according that PC ... Cannot open database "pago" requested by the login. ... Are you detaching/attaching the SQL Server Express database correctly ...
    (microsoft.public.dotnet.framework.aspnet)
  • Location of MSDE & SQL Server Binn and Database Folders
    ... i.e. SHAREPOINT & SBSMONITORING in C:\Program Files\Microsoft SQL Server. ... Server you are given the option of specifying the location of database ... During the install of ISA Server an MSDE database is created i.e. MSFW ...
    (microsoft.public.windows.server.sbs)
  • Re: Newbie to security
    ... Use sp_grantdbaccess to grant access to the database. ... databases when you are new to security. ... Microsoft SQL Server 2000 SP3 Security Features and Best ...
    (microsoft.public.sqlserver.security)
  • Re: SQL Server Express
    ... GRANT them permissions to do so with the GRANT command. ... GRANT CREATE DATABASE on DATABASE::xxx to SAM ... Yes, I am very familiar with configuring specific SQL Server accounts for access, and I am having no real problems in this area. ... really a good way to block administrator access without making your database pretty hard to administer. ...
    (microsoft.public.sqlserver.msde)
  • Re: How to Generate all grants to a user role? (SQL Server 2000)
    ... another version of the database. ... WHEN 204 THEN 'GRANT' ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)