| Groups | Blog | Home |
sql server msde : Install MSDE w/ MSDE Depl.Toolkit. What permissions when using Win Auth?
I have been following Mario Szpuszta's excellent article on MSDN "The MSDE Deployment Toolkit (RC)in Action"(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmsde/html/msdedepl.asp) to build a package which can install MSDE (if required) and then install my database and its application. I package my database up by: 1. Stop replication on the database (full SQL server version) on the server that I want to distribute. 2. Detach the database on the server. 3. Copy the mdf and ldf files to my development machine. 4. Create my MSDE installer package with the mdf and ldf files and use sp_attach_db to install the database on the MSDE clients. I am using Windows Authentication, so what do I need to do to make sure that whoever uses the client application to access the MSDE database has the correct permissions? I really cannot explicitly give every single user (>500) their own login to the database as it would mean a lot of tedious admin work and it would be a nightmare to maintain as every time there is a new user I would have to give that new user a login on the SQL server and then go through steps 1-4 and re-package and distribute. Thanks for any help
hi Andrew,
"Andrew Gaskell" <agaskelluk@yahoo.com> ha scritto nel messaggio news:63c811db.0411290355.c5c2166@posting.google.com [quoted text, click to view] > Hi All Action"(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnm> I have been following Mario Szpuszta's excellent article on MSDN "The > MSDE Deployment Toolkit (RC)in > sde/html/msdedepl.asp) [quoted text, click to view] > to build a package which can install MSDE (if required) and then > install my database and its application. > > I package my database up by: > > 1. Stop replication on the database (full SQL server version) on the > server that I want to distribute. > 2. Detach the database on the server. > 3. Copy the mdf and ldf files to my development machine. > 4. Create my MSDE installer package with the mdf and ldf files and use > sp_attach_db to install the database on the MSDE clients. > > I am using Windows Authentication, so what do I need to do to make > sure that whoever uses the client application to access the MSDE > database has the correct permissions? I really cannot explicitly give > every single user (>500) their own login to the database as it would > mean a lot of tedious admin work and it would be a nightmare to > maintain as every time there is a new user I would have to give that > new user a login on the SQL server and then go through steps 1-4 and > re-package and distribute. > as you are using Windows authentication, you can grant the domain logins access to MSDE via sp_grantlogin (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts _sp_ga-gz_8dri.asp) ... you should use not every single Windows login but Windows Groups, so that all members of the specified group will inherit access to SQL Server... later you've to grant them access to your databases via sp_grantdbaccess (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts _sp_ga-gz_290z.asp), again using the Windows group and possibly making them part of a database role you can and should grant the required privileges... you can even create/use more database roles to handle different privileges levels -- 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
Andrea
thanks for the response. However by using Windows groups, don't I still have to maintain the group itself on the server by manually adding a domain user to the group each time a new user requires access to my application? I read about the Builtin\Administrators group. My detatched SQL database will still have the Builtin\Administrators group and my clients will be members of the local admin group on Win2K, so as I understand it they should have access to the MSDE database. Is this correct? Thanks Andrew [quoted text, click to view] "Andrea Montanari" <andrea.sqlDMO@virgilio.it> wrote in message news:<3110nvF353jhhU1@uni-berlin.de>...
> hi Andrew, > "Andrew Gaskell" <agaskelluk@yahoo.com> ha scritto nel messaggio > news:63c811db.0411290355.c5c2166@posting.google.com > > Hi All > > I have been following Mario Szpuszta's excellent article on MSDN "The > > MSDE Deployment Toolkit (RC)in > > > Action"(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnm > sde/html/msdedepl.asp) > > to build a package which can install MSDE (if required) and then > > install my database and its application. > > > > I package my database up by: > > > > 1. Stop replication on the database (full SQL server version) on the > > server that I want to distribute. > > 2. Detach the database on the server. > > 3. Copy the mdf and ldf files to my development machine. > > 4. Create my MSDE installer package with the mdf and ldf files and use > > sp_attach_db to install the database on the MSDE clients. > > > > I am using Windows Authentication, so what do I need to do to make > > sure that whoever uses the client application to access the MSDE > > database has the correct permissions? I really cannot explicitly give > > every single user (>500) their own login to the database as it would > > mean a lot of tedious admin work and it would be a nightmare to > > maintain as every time there is a new user I would have to give that > > new user a login on the SQL server and then go through steps 1-4 and > > re-package and distribute. > > > > as you are using Windows authentication, you can grant the domain logins > access to MSDE via sp_grantlogin > (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts > _sp_ga-gz_8dri.asp) ... you should use not every single Windows login but > Windows Groups, so that all members of the specified group will inherit > access to SQL Server... later you've to grant them access to your databases > via sp_grantdbaccess > (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts > _sp_ga-gz_290z.asp), again using the Windows group and possibly making them > part of a database role you can and should grant the required privileges... > you can even create/use more database roles to handle different privileges
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? 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? Thanks again Andrew [quoted text, click to view] "Andrea Montanari" <andrea.sqlDMO@virgilio.it> wrote in message news:<313cs9F35t7biU1@uni-berlin.de>...
> and Andrew, > "Andrew Gaskell" <agaskelluk@yahoo.com> ha scritto nel messaggio > news:63c811db.0411300308.c9c0b09@posting.google.com > > Andrea > > thanks for the response. However by using Windows groups, don't I > > still have to maintain the group itself on the server by manually > > adding a domain user to the group each time a new user requires access > > to my application? > > this is all performed thought the Domain Controller, which maintains all the > credentials... as you add > YourDomain\Andrea > to your domain, making me part of the XYZ group which has already been > granted access to the YourDomain\YourMsde server and granted that group db > access as db_datareader or any other builtin or userdefined db role, I'll > inherit login privileges and db access as well... for that reason I > recommend using NT groups where possible :D > > > I read about the Builtin\Administrators group. My detatched SQL > > database will still have the Builtin\Administrators group and my > > clients will be members of the local admin group on Win2K, so as I > > understand it they should have access to the MSDE database. Is this > > correct? > > Builtin\Administrators is a local group and not domain group... all local > login member of that group will be granted SQL Server login privilege, but > not your (remote) clients that should be member of other groups... more... > you should not rely on administrative logins/privileges/permissions becouse > an admistrator really is a God in SQL Server, and could be a kind god, or > could be as Loki (and I mean the malicious one of the Norse pantheon)... > so my advice is to create as many database roles as you need, with just > enought permission as required, and map your NT groups to the corresponding
and Andrew,
"Andrew Gaskell" <agaskelluk@yahoo.com> ha scritto nel messaggio news:63c811db.0411300308.c9c0b09@posting.google.com [quoted text, click to view] > Andrea > thanks for the response. However by using Windows groups, don't I > still have to maintain the group itself on the server by manually > adding a domain user to the group each time a new user requires access > to my application? this is all performed thought the Domain Controller, which maintains all the credentials... as you add YourDomain\Andrea to your domain, making me part of the XYZ group which has already been granted access to the YourDomain\YourMsde server and granted that group db access as db_datareader or any other builtin or userdefined db role, I'll inherit login privileges and db access as well... for that reason I recommend using NT groups where possible :D [quoted text, click to view] > I read about the Builtin\Administrators group. My detatched SQL > database will still have the Builtin\Administrators group and my > clients will be members of the local admin group on Win2K, so as I > understand it they should have access to the MSDE database. Is this > correct? Builtin\Administrators is a local group and not domain group... all local login member of that group will be granted SQL Server login privilege, but not your (remote) clients that should be member of other groups... more... you should not rely on administrative logins/privileges/permissions becouse an admistrator really is a God in SQL Server, and could be a kind god, or could be as Loki (and I mean the malicious one of the Norse pantheon)... so my advice is to create as many database roles as you need, with just enought permission as required, and map your NT groups to the corresponding db 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
hi Andrew,
"Andrew Gaskell" <agaskelluk@yahoo.com> ha scritto nel messaggio news:63c811db.0411301019.47b2436e@posting.google.com [quoted text, click to view] > 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... [quoted text, click to view] > > 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
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 [quoted text, click to view] 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
hi Andrew,
"CodeMonkey" <agaskelluk@yahoo.com> ha scritto nel messaggio news:1101991621.708649.228870@c13g2000cwb.googlegroups.com [quoted text, click to view] > .... > **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? :D that's what I (wanted to) suggest... to maintain your WinNT logins as groups (I know "MyApplicationUsers" or "TravellingUsers" do not usually exists as you have to create that groups :D.... I apologize for beeing cryptic :)) [quoted text, click to view] > **Second Question** try executing> 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? > SET NOCOUNT ON SELECT CURRENT_USER AS [current user] which should report --<------------ current user ------------ guest that's to say MyDomain\MyUser is mapped to "Guest" user (if you did not removed it from your model database as I usually do) and will be member of the "Public" 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
Hi Andrea
I understand what you mean about groups now (I hope). Unless I use a group containing ONE user - the special user NTAuthority\Authenticated users, then I can't get away from adding each user individually (unless I user SQL Server authentication), even with groups. Is this correct? I got a test user (lets call them somedomain\testuser) to use osql to run the sql code you suggested. It ran fine, but it didn't return "guest"! Instead it returned "dbo" which surprised me as the dbo of the database on the server (where this MSDE database came from) is me, not somedomain\testuser. I checked on the server and it clearly states that the owner of the database is me. Maybe its because of how the database is created in the first place on the users machine? Here is the code that my installation routine uses to install the database: /* If the abcdb database exists, then drop it */ IF EXISTS (SELECT * FROM sysdatabases WHERE name = 'abcdb') DROP DATABASE abcdb GO /* Attach mdf and ldf to new database */ exec sp_attach_db @dbname=N'abcdb', @filename1 = N'c:\abcsqldata\abcdb.MDF', @filename2 = N'c:\abcsqldata\abcdb.LDF' Thanks Andrew [quoted text, click to view] Andrea Montanari wrote:
> hi Andrew, > "CodeMonkey" <agaskelluk@yahoo.com> ha scritto nel messaggio > news:1101991621.708649.228870@c13g2000cwb.googlegroups.com > > .... > > **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? > > :D that's what I (wanted to) suggest... to maintain your WinNT logins as > groups (I know "MyApplicationUsers" or "TravellingUsers" do not usually > exists as you have to create that groups :D.... I apologize for beeing > cryptic :)) > > > **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? > > > try executing > SET NOCOUNT ON > SELECT CURRENT_USER AS [current user] > which should report > --<------------ > current user > ------------ > guest > > that's to say MyDomain\MyUser is mapped to "Guest" user (if you did not > removed it from your model database as I usually do) and will be member of > the "Public" 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
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |