Setting File Access & Simple File Sharing

[rkaine]

I want to attach a SQL Server database file but there is a couple of steps that I have to take first

It seems that if I copy the database file (.MDF) into the Program Files\\$TargetDir$ I don't have the permission I need to attach it.

If I try to use 'Set Access Control and set 'File System' to 'Grant Everyone' for $TargetDir$ it wont work if 'Simple File Sharing' is enabled; which is the recommended and default XP setting.

If I try to use 'Write Registry' to disable 'Simple File Sharing' it is cached (the purple commands) which means it runs after the 'Set Access Control'

I'm sure it's just me but I need help

[jimo]

I do this exact procedure in my installation package all the time.

Paste this into the SQL Script edit box for the MS SQL Server Plugin:

Code: Select all

EXEC sp_attach_db @dbname = N'$PRODDATABASENAME$',
@filename1 = N'$TARGETDIR$\\Data\\ProductionDatabase\\mdpmproduction.mdf',
@filename2 = N'$TARGETDIR$\\Data\\ProductionDatabase\\mdpmproduction.ldf'
GO
USE $PRODDATABASENAME$
GO
if not exists (select * from master.dbo.syslogins where loginname = N'PMD')
BEGIN
   declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N'master', @loginlang = N'us_english'
   if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
      select @logindb = N'master'
   if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')
      select @loginlang = @@language
   exec sp_addlogin N'PMD', 'greatest', @logindb, @loginlang
END
GO

if not exists (select * from master.dbo.syslogins where loginname = N'$LOGONDOMAIN$\\Domain Users')
   exec sp_grantlogin N'$LOGONDOMAIN$\\Domain Users'
   exec sp_defaultdb N'$LOGONDOMAIN$\\Domain Users', N'master'
   exec sp_defaultlanguage N'$LOGONDOMAIN$\\Domain Users', N'us_english'
GO
exec sp_addsrvrolemember N'$LOGONDOMAIN$\\Domain Users', sysadmin
GO

exec sp_addsrvrolemember N'PMD', sysadmin
GO

if not exists (select * from dbo.sysusers where name = N'$LOGONDOMAIN$\\Domain Users' and uid < 16382)
   EXEC sp_grantdbaccess N'$LOGONDOMAIN$\\Domain Users', N'$LOGONDOMAIN$\\Domain Users'
GO
if not exists (select * from dbo.sysusers where name = N'BUILTIN\\Administrators' and uid < 16382)
   EXEC sp_grantdbaccess N'BUILTIN\\Administrators', N'BUILTIN\\Administrators'
GO
if not exists (select * from dbo.sysusers where name = N'PMD' and uid < 16382)
   EXEC sp_grantdbaccess N'PMD', N'PMD'
GO
exec sp_addrolemember N'db_owner', N'BUILTIN\\Administrators'
GO
exec sp_addrolemember N'db_owner', N'$LOGONDOMAIN$\\Domain Users'
GO
exec sp_addrolemember N'db_owner', N'PMD'
GO



Set the variables;
$PRODDATABASENAME$=The name you want the database to appear as in Enterprise manager
$LOGONDOMAIN$=The logon domain of the user (You can obtain this value using "Get System Setting"

The PMD user a a user we create for SQL Authentication, you can use one or edit out the appropriate lines.

The user installing the applicaitno MUST have administrator rights to the local machine.

Of course edit the paths for @filename1 and @filename2.

Hope this helps!

[rkaine]

My stored procedure is very similar to yours.

My problem is the attach_db fails 'Access Denied'

The only thing I can figure out is that it is related to SQL Express because I never had this issue with SQL2000

[jimo]

What happens if you copy the db into the directory without the installer and try to attach the db using SQL Server Management Studio?

[rkaine]

Same thing. It's a security issue the problem is that changing file access doesnt work unless simple file sharing is disabled and write registry is a cached command and set access control isnt

[jimo]

I guess I forgot to metion that I always run my attach_db script after the apply install command.

If you do this then the cached commands will have been applied.

[rkaine]

I always run my attach_db script after the apply install command

That's the piece I was missing ...THANK YOU