Here is the query (its fun
) that is used for for when a user is authorized/unauthorized. Its possible this needs to be changed from version 6.1 possibly... At different times we had to add in different versions because DNN changed their database structure so maybe it changed again?
CREATE Procedure {databaseOwner}[{objectQualifier}DataSprings_RetrieveDNNUserProfileData3]
(@PortalID int,@RoleFilter nvarchar(1000),@LimitResults nvarchar(100),@Authonly int,@Filter nvarchar(4000), @ORDERBY nvarchar(1000))
AS
BEGIN
if exists(select top 1 * from sysobjects where xtype = 'U' and name = 'tempDUDProfile') and (@PortalID = 999)
begin
drop table {databaseOwner}tempDUDProfile
end
if exists(select top 1 * from sysobjects where xtype = 'U' and name = 'tempDUDPivot') and (@PortalID = 999)
begin
drop table {databaseOwner}tempDUDPivot
end
-- generate tempDUDProfile table
-- create table tempDUDProfile if not exists
If not exists(select top 1 * from sysobjects where xtype = 'U' and name = 'tempDUDProfile')
BEGIN
select U.userid,U.username,U.firstname,U.lastname,U.email,U.Displayname,UP.propertyvalue,
P.propertyname, P.PortalID into {databaseOwner}tempDUDProfile
from {databaseOwner}[{objectQualifier}Users] U inner join {databaseOwner}[{objectQualifier}userprofile] UP on U.userid = UP.userid
inner join {databaseOwner}[{objectQualifier}profilepropertydefinition] P on UP.propertydefinitionid = P.propertydefinitionid
--where U.userid In (Select userid from {databaseOwner}[{objectQualifier}UserPortals] where PortalID = Convert(varchar(3), @PortalID))
where P.Visible=1
--AND P.PortalID = @PortalID
AND P.Deleted=0
AND P.PRopertyName <> 'FirstName'
AND P.PropertyName <> 'LastName'
AND P.PRopertyNAme <> 'E-Mail'
AND P.PRopertyNAme <> 'Email'
AND P.PRopertyNAme <> 'Username'
AND P.PRopertyNAme <> 'Displayname'
AND P.PropertyName Not Like '%[.#)(&[@!^!]]%' ESCAPE '!'
exec {databaseOwner}[{objectQualifier}DataSprings_RetrieveDNNUserProfileData3a]
END
If (@PortalID <> 999)
BEGIN
Declare @MySQL nvarchar(max)
-- check if DNN version is 5.0 or higher
if exists (select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME like '%userportals%' and COLUMN_NAME = 'IsDeleted')
and exists (select top 1 * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME like '%users' and COLUMN_NAME = 'IsDeleted')
BEGIN
Set @MySQL = 'select ' + @LimitResults + ' A.*, {databaseOwner}{objectQualifier}DataSprings_DUD_GetUserRoles(A.UserID) AS UserRoles from {databaseOwner}tempDUDPivot AS A' + '
inner join aspnet_users AS B on B.username = A.username
inner join aspnet_membership C on B.userid = C.userid '
+ replace(@Filter,'[','A.[')
+ replace(@RoleFilter,'[','A.[')
+ ' AND ((C.IsApproved = 1) OR (' + Cast(@AuthOnly as varchar(2)) + ' = 0))'
+ ' AND A.PortalID = ' + cast(@PortalID as varchar(3))
+ ' AND A.userid not in (select userid from {databaseOwner}[{objectQualifier}users] where issuperuser = 1 and isdeleted = 1)
AND A.userid not in (select userid from {databaseOwner}[{objectQualifier}userportals] where isdeleted = 1 AND portalID = '
+ cast(@PortalID as varchar(3)) + ')'
+ ' AND (A.userid not in (select userid from {databaseOwner}[{objectQualifier}userportals] where Authorised = 0 AND portalID = '
+ cast(@PortalID as varchar(3)) + ') OR (' + Cast(@AuthOnly as varchar(2)) + ' = 0))'
+ @ORDERBY
EXECUTE(@MySQL)
END
else
if exists (select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME like '%userportals%' and COLUMN_NAME = 'IsDeleted')
and not exists (select top 1 * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME like '%users' and COLUMN_NAME = 'IsDeleted')
BEGIN
Set @MySQL = 'select ' + @LimitResults + ' A.*, {databaseOwner}{objectQualifier}DataSprings_DUD_GetUserRoles(A.UserID) AS UserRoles from {databaseOwner}tempDUDPivot AS A' + '
inner join aspnet_users AS B on B.username = A.username
inner join aspnet_membership C on B.userid = C.userid '
+ replace(@Filter,'[','A.[')
+ replace(@RoleFilter,'[','A.[')
+ ' AND ((C.IsApproved = 1) OR (' + Cast(@AuthOnly as varchar(2)) + ' = 0))'
+ ' AND A.PortalID = ' + cast(@PortalID as varchar(3))
+ ' AND A.userid not in (select userid from {databaseOwner}[{objectQualifier}userportals] where isdeleted = 1 AND portalID = '
+ cast(@PortalID as varchar(3)) + ')'
+ ' AND (A.userid not in (select userid from {databaseOwner}[{objectQualifier}userportals] where Authorised = 0 AND portalID = '
+ cast(@PortalID as varchar(3)) + ') OR (' + Cast(@AuthOnly as varchar(2)) + ' = 0))'
+ @ORDERBY
EXECUTE(@MySQL)
END
else
-- if DNN version is lower than 5.0
BEGIN
Set @MySQL = 'select ' + @LimitResults + ' A.*, {databaseOwner}{objectQualifier}DataSprings_DUD_GetUserRoles(A.UserID) AS UserRoles from {databaseOwner}tempDUDPivot AS A' + '
inner join aspnet_users AS B on B.username = A.username
inner join aspnet_membership C on B.userid = C.userid '
+ replace(@Filter,'[','A.[')
+ replace(@RoleFilter,'[','A.[')
+ ' AND ((C.IsApproved = 1) OR (' + Cast(@AuthOnly as varchar(2)) + ' = 0))'
+ ' AND A.PortalID = ' + cast(@PortalID as varchar(3)) +''
+ @ORDERBY
EXECUTE(@MySQL)
END
--PRINT(@MySQL)
END
END
GO