Thanks for sharing this with us. I think it may just be what I'm looking for.
Which stored procs are not in SQL2008? From what I can tell they are still there, but have been marked for deprecation in future versions of SQLServer. Dominique. -----Original Message----- From: Michael Osmond [mailto:mosm...@baytech.com.au] Sent: 07 October 2009 07:11 To: General discussion for Windows Installer XML toolset. Subject: Re: [WiX-users] Creating SQLServer Users/Logins.... Here's an example of how I create the user login for a database depending if the database is on the same server as the web application or a different server Some of the security stored procs I am using are obsolete in SQL2008 though (this was built for SQL2000) <SqlDatabase Id="DBSQL1" CreateOnInstall="yes" ConfirmOverwrite="yes" DropOnInstall="no" ContinueOnError="no" Database="[BAYAPPDB]" DropOnUninstall="no" Server="[BAYDBSERVER]"> ---- SQL Scripts go here --- <SqlString Id="CreateUsers" ContinueOnError="yes" ExecuteOnInstall="yes" ExecuteOnReInstall="no" Sequence="11000" SQL=" -- Add user to the database, if it is local then this is just a Rpt User -- Else add the System User if ('[BAYSECURITYMODEL]'='local') BEGIN -- Create User in SQL if ((select count(name) from master.dbo.syslogins where name = '[BAYSYSUSERNAME]') < 1) exec sp_addlogin '[BAYSYSUSERNAME]', '[BAYSYSPASSWORD]' if ((select count(name) from sysusers where sid=(select sid from master.dbo.syslogins where name = '[BAYSYSUSERNAME]')) < 1) BEGIN exec sp_grantdbaccess '[BAYSYSUSERNAME]', 'RptUser' exec sp_addrolemember @rolename = 'db_datareader', @membername = 'RptUser' END END else BEGIN -- Add the windows user to the system if ( (select count(name) from master.dbo.syslogins where name = '[BAYSYSUSERNAME]') < 1) exec sp_grantlogin '[BAYSYSUSERNAME]' -- Create User in this database if ((select count(name) from sysusers where sid=(select sid from master.dbo.syslogins where name = '[BAYSYSUSERNAME]')) < 1) BEGIN exec sp_grantdbaccess '[BAYSYSUSERNAME]', 'AppUser' exec sp_addrolemember @rolename = 'db_datareader', @membername = 'AppUser' exec sp_addrolemember @rolename = 'db_datawriter', @membername = 'AppUser' END END -- If we are all installing together if (('[BAYSECURITYMODEL]'='local') AND ('[ComputerName]' like '[BAYDBSERVER]') AND ( NOT '[SKIPCONFIGUREIIS]' = '1' )) BEGIN if ( SUSER_ID('[ComputerName]\iis_wpg') is null) exec sp_grantlogin '[ComputerName]\iis_wpg' if ((select count(name) from sysusers where sid=SUSER_SID('[ComputerName]\iis_wpg')) < 1) BEGIN exec sp_grantdbaccess '[ComputerName]\iis_wpg', 'iis_wpg' exec sp_addrolemember @rolename = 'db_datareader', @membername = 'iis_wpg' exec sp_addrolemember @rolename = 'db_datawriter', @membername = 'iis_wpg' END END "/> -----Original Message----- From: Thomas Due [mailto:thomas....@scanvaegt.dk] Sent: Wednesday, 7 October 2009 4:04 PM To: General discussion for Windows Installer XML toolset. Subject: Re: [WiX-users] Creating SQLServer Users/Logins.... This one triggered me: >> This way I can pass username information into the SQL script as an MSI property. How do you do that? That is something I find could be very very useful to me, not only username information, but all sorts of information that could be useful during database creation, or update. TYI /Thomas -----Original Message----- From: Michael Osmond [mailto:mosm...@baytech.com.au] Sent: 6. oktober 2009 23:32 To: General discussion for Windows Installer XML toolset. Subject: Re: [WiX-users] Creating SQLServer Users/Logins.... Louis You need to look at SqlDatabase, SqlScript and SqlString. I use SqlString to create users (with TSQL) and then assign them a role in the target database, as SqlString expands formatted strings. This way I can pass username information into the SQL script as an MSI property. You could also use this to do the attach process, passing the file The alternate method to attaching databases is to script out you database creation into SLQ Scripts and use the WIX SqlScript custom actions to create the database. Michael -----Original Message----- From: Dominique Louis [mailto:dominique.lo...@amxeurope.com] Sent: Wednesday, 7 October 2009 1:42 AM To: General discussion for Windows Installer XML toolset. Subject: [WiX-users] Creating SQLServer Users/Logins.... Hi all, I'm working on a script that attaches our database to an instance of SQLServer. It then creates a virtual directory ( not under c:\Inetpub ) in IIS ( 6 or 7 ) and correctly sets the appropriate folder permissions so that the ASP.NET v2.x, can be viewed across the network. This all seems to work and I'll post some code snippets in the next week. My issue is that if these .aspx page make a DB call, it doesn't get through. On XP I can manually create a [ComputerName]\ASPNET Login and that seems to allow the pages to correctly connect to the SQLServer DB. On Vista, and I assume Window Server 2008, the ASPNET user does not exist :(. So my questions are, using Wix... 1. How can I automate the creation of a User/Login on SQLServer. 2. Is there a way to work out ( maybe via a custom action ) what the correct ASPNET/IIS user that is required for SQLServer to accepts connections from ASP.NET pages. 3. One of the developers here suggested that what might be better or more flexible would be to create a DB specific user and use that DB User to connect from the .aspx page to the DB. a. How can I automate the creation of the DB User? b. Can Wix automate the attachment of aforementioned user to the DB? 4. Are there better/easier ways of doing what I'm trying to do? I hope that all makes sense. AMX AMX UK Auster Road Clifton Moor York, North Yorkshire United Kingdom YO30 4GD +44 (0) 1904 343100 office +44 (0) 1904 343101 fax AMX South 6th Floor Salisbury House London Wall London United Kingdom EC2M 5QQ +44 (0) 2076 529450 office +44 (0) 8701 991661 fax AMX Belgium Boerenkrijglaan, 96a B-2260 Westerlo Belgium + 32 (0) 1454 2763 office + 32 (0) 1454 2766 fax ###################################################################### Attention: This e-mail message is privileged and confidential. If you are not the intended recipient please delete the message and notify the sender. Any views or opinions presented are solely those of the author. This email was scanned and cleared by NetIQ MailMarshal. ###################################################################### ------------------------------------------------------------------------------ Come build with us! The BlackBerry(R) Developer Conference in SF, CA is the only developer event you need to attend this year. Jumpstart your developing skills, take BlackBerry mobile applications to market and stay ahead of the curve. Join us from November 9 - 12, 2009. Register now! http://p.sf.net/sfu/devconference _______________________________________________ WiX-users mailing list WiX-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/wix-users
