So I figured it was time to upgrade our 4.4.0 DNN install to the latest version 4.6.2.
I pulled down the source package and ran through a basic install. No problems there.
Then I turned off the install wizard (via web config) and changed the databaseOwner setting to "DNN" so as to try and install everything under a separate schema in the database like we have in 4.4.0 since we have several other applications running in the same database under their own schemas as well.
Needless to say I got a bunch of SQL errors. I went looking at the best place to post this and this is the best I could find...if this should go elsewhere...by all means move it or let me know. Also I am totally open to any and all feedback/responses. I would just like to see these fixes get into the next DNN release and more attention given to the use of schemas...they are rather handy.
1. Previously in 4.4.0 we had to do a seek and destroy to change "{DatabaseOwner}sysobjects" to simply "dbo.sysobjects". I ran this by our DBA here and he told me anything other than "dbo.sysobjects" or "sysobjects" simply will never work. For 4.6.2, it looks like the problem is in all the old SQL scripts and the only spot it still was a problem was the HTML module in 03.00.00.SqlDataProvider. I would imagine anyone upgrading from 2.x to 4.x on SQL 2005 is going to hit this as all the really old scripts have TONS of these. I searched and replaced them all.
2. 04.06.00.SqlDataProvider on line #98 has an issue. It may have worked in SQL 2000 but it does not work in SQL 2005.
The offender:
INNER JOIN {objectQualifier}Modules m ON s.ModuleID = m.ModuleID
The fix (was missing "{databaseOwner}"):
INNER JOIN {databaseOwner}{objectQualifier}Modules m ON s.ModuleID = m.ModuleID
3. SqlDataProvider.vb has a couple of functions: GrantStoredProceduresPermission and GrantUserDefinedFunctionsPermission. These do a couple of things wrong.
A. First off they loop through the entire sysobjects table setting permissions on ALL the StoredProcs and UserDefined Functions. The sysobjects table needs to be joined with INFORMATION_SCHEMA.ROUTINES in order to filter out all NON DNN objects. DNN should not be trying to set permissions on objects it does not own. In our case, with our other apps under different schemas, this is exactly what was happening.
B. Also the function needs to exit immediately if the login is SA, DBO, INFORMATION_SCHEMA, or SYS. The grant statement will throw an error otherwise...and it's kind of a silly thing to do anyways.
C. Lastly, the DatabaseOwner was missing from the generated Grant statement...which needs to be there for SQL2005.
After fixing the above...I finally got DNN 4.6.2 to install under it's own schema with NO errors! Hopefully, that is the end of things...doubtful though I think. BTW, I did set my SQL 2005 to run in SQL 2000 compatability mode and it didn't help either.
Here are the modified functions:
Private Function GrantStoredProceduresPermission(ByVal Permission As String, ByVal LoginOrRole As String) As String
If LoginOrRole.ToUpper = "SA" _
OrElse LoginOrRole.ToUpper = "DBO" _
OrElse LoginOrRole.ToUpper = "INFORMATION_SCHEMA" _
OrElse LoginOrRole.ToUpper = "SYS" _
Then
'It is an error (and silly) to try and grant permissions to those logins/roles
Return ""
End If
Dim SQL As String = ""
Dim Exceptions As String = ""
Try
' grant rights to a login or role for all stored procedures
SQL += "declare @exec nvarchar(2000) "
SQL += "declare @name varchar(150) "
SQL += "declare sp_cursor cursor for select o.name as name "
SQL += "from dbo.sysobjects o "
SQL += " inner join INFORMATION_SCHEMA.ROUTINES r "
SQL += " on o.name = r.specific_name "
SQL += "where ( OBJECTPROPERTY(o.id, N'IsProcedure') = 1 or OBJECTPROPERTY(o.id, N'IsExtendedProc') = 1 or OBJECTPROPERTY(o.id, N'IsReplProc') = 1 ) "
SQL += "and OBJECTPROPERTY(o.id, N'IsMSShipped') = 0 "
SQL += "and o.name not like N'#%%' "
SQL += "and (left(o.name,len('" & ObjectQualifier & "')) = '" & ObjectQualifier & "' or left(o.name,7) = 'aspnet_') "
SQL += "and r.specific_schema + '.' = '" & DatabaseOwner & "' "
SQL += "open sp_cursor "
SQL += "fetch sp_cursor into @name "
SQL += "while @@fetch_status >= 0 "
SQL += "begin"
SQL += " select @exec = 'grant " & Permission & " on " & DatabaseOwner & "' + @name + ' to " & LoginOrRole & "'"
SQL += " execute (@exec)"
SQL += " fetch sp_cursor into @name "
SQL += "end "
SQL += "deallocate sp_cursor"
SqlHelper.ExecuteNonQuery(UpgradeConnectionString, CommandType.Text, SQL)
Catch objException As SqlException
Exceptions += objException.ToString & vbCrLf & vbCrLf & SQL & vbCrLf & vbCrLf
End Try
Return Exceptions
End Function
Private Function GrantUserDefinedFunctionsPermission(ByVal ScalarPermission As String, ByVal TablePermission As String, ByVal LoginOrRole As String) As String
If LoginOrRole.ToUpper = "SA" _
OrElse LoginOrRole.ToUpper = "DBO" _
OrElse LoginOrRole.ToUpper = "INFORMATION_SCHEMA" _
OrElse LoginOrRole.ToUpper = "SYS" _
Then
'It is an error (and silly) to try and grant permissions to those logins/roles
Return ""
End If
Dim SQL As String = ""
Dim Exceptions As String = ""
Try
' grant EXECUTE rights to a login or role for all functions
SQL += "declare @exec nvarchar(2000) "
SQL += "declare @name varchar(150) "
SQL += "declare @isscalarfunction int "
SQL += "declare @istablefunction int "
SQL += "declare sp_cursor cursor for select o.name as name, OBJECTPROPERTY(o.id, N'IsScalarFunction') as IsScalarFunction "
SQL += "from dbo.sysobjects o "
SQL += " inner join INFORMATION_SCHEMA.ROUTINES r "
SQL += " on o.name = r.specific_name "
SQL += "where ( OBJECTPROPERTY(o.id, N'IsScalarFunction') = 1 OR OBJECTPROPERTY(o.id, N'IsTableFunction') = 1 ) "
SQL += "and OBJECTPROPERTY(o.id, N'IsMSShipped') = 0 "
SQL += "and o.name not like N'#%%' "
SQL += "and (left(o.name,len('" & ObjectQualifier & "')) = '" & ObjectQualifier & "' or left(o.name,7) = 'aspnet_') "
SQL += "and r.specific_schema + '.' = '" & DatabaseOwner & "' "
SQL += "open sp_cursor "
SQL += "fetch sp_cursor into @name, @isscalarfunction "
SQL += "while @@fetch_status >= 0 "
SQL += "begin "
SQL += "if @IsScalarFunction = 1 "
SQL += "begin"
SQL += " select @exec = 'grant " & ScalarPermission & " on " & DatabaseOwner & "' + @name + ' to " & LoginOrRole & "'"
SQL += " execute (@exec)"
SQL += " fetch sp_cursor into @name, @isscalarfunction "
SQL += "end "
SQL += "else "
SQL += "begin"
SQL += " select @exec = 'grant " & TablePermission & " on " & DatabaseOwner & "' + @name + ' to " & LoginOrRole & "'"
SQL += " execute (@exec)"
SQL += " fetch sp_cursor into @name, @isscalarfunction "
SQL += "end "
SQL += "end "
SQL += "deallocate sp_cursor"
SqlHelper.ExecuteNonQuery(UpgradeConnectionString, CommandType.Text, SQL)
Catch objException As SqlException
Exceptions += objException.ToString & vbCrLf & vbCrLf & SQL & vbCrLf & vbCrLf
End Try
Return Exceptions
End Function
-Mike