Add Your Useful DNN SQL Script Here!

If you have one or more scripts that you would like to add to this list for everyone's benefit, please send your script and credits metadata to Mark Buelsing using the "Request Info" page on this site.

Here is a link for your convenience.
http://letitshine.biz/RequestInfo.aspx

Thank you!

Script to set the visibility of all modules to none across the entire website.

Log in as Host
Go to menu item Host -> SQL
Execute the following SQL statement (after ticking Run as script)


UPDATE {databaseOwner}{objectQualifier}TabModules
SET Visibility = 2
WHERE {databaseOwner}{objectQualifier}TabModules.IconFile IS NULL
AND {databaseOwner}{objectQualifier}TabModules.Visibility != 2

Scripts for working with modules and pages.

Find the ModuleID's of the modules on a particluar page

SELECT *
FROM TabModules
WHERE TabID = 254

Note: Instead of 254, use the tabid of the page in question.

Find the ModuleID's of the modules on a particluar page in a particular named pane

SELECT *
FROM TabModules
WHERE TabID = 254
AND (PaneName = 'RightPane')

The quick way to delete modules from a page. This is particularly useful when you have selected to add a module to all pages in the site and there area few pages where they should be removed.

DELETE
FROM TabModules
WHERE TabID =
767
AND (ModuleID= 1620 OR
ModuleID= 1618 OR
ModuleID= 1619 OR
ModuleID= 1621 )

Here is the quick way to move modules to another pane. This is useful when you have changed the skin and your modules need to be repositioned to better panes in the new skin.

This is for one page at a time:

UPDATE TabModules
SET PaneName = 'ThreeGrid3'
WHERE TabID = 907 AND
(PaneName = 'TwoGrid4C')

This moves all instances of any module in the 'TwoGrid4c' pane to the 'ThreeGrid3' pane on all pages

UPDATE TabModules
SET PaneName = 'ThreeGrid3'
WHERE PaneName = 'TwoGrid4C'

Report of Page Names a UserID has edit permissions

Substitute your portal ID in the WHERE claus

SELECT [FirstName]+' '+[LastName] AS 'Full Name',
dbo.Roles.[RoleName] AS [Security Role Name],
dbo.Tabs.TabName AS 'Page Name',
dbo.Tabs.TabPath AS 'Path'

FROM (dbo.Tabs INNER JOIN (dbo.TabPermission
INNER JOIN dbo.Roles ON dbo.TabPermission.RoleID = dbo.Roles.RoleID)
ON dbo.Tabs.TabID = dbo.TabPermission.TabID)
INNER JOIN (dbo.Users INNER JOIN dbo.UserRoles
ON dbo.Users.UserID = dbo.UserRoles.UserID)
ON dbo.Roles.RoleID = dbo.UserRoles.RoleID

WHERE (((dbo.Users.UserID)=@UserID) AND ((dbo.TabPermission.PermissionID)=4) AND
((dbo.Roles.RoleName)<>'Administrators') AND ((dbo.Tabs.PortalID)=5))

ORDER BY dbo.Roles.[RoleName], dbo.Tabs.TabName



And here is a report of page names for which a UserID has view permissions. Again, you must substitute your portal number in for the one used here.

SELECT [FirstName]+' '+[LastName] AS 'Full Name',
dbo.Roles.[RoleName] AS [Security Role Name],
dbo.Tabs.TabName AS 'Page Name',
dbo.Tabs.TabPath AS 'Path'

FROM (dbo.Tabs INNER JOIN (dbo.TabPermission
INNER JOIN dbo.Roles ON dbo.TabPermission.RoleID = dbo.Roles.RoleID)
ON dbo.Tabs.TabID = dbo.TabPermission.TabID)
INNER JOIN (dbo.Users INNER JOIN dbo.UserRoles
ON dbo.Users.UserID = dbo.UserRoles.UserID)
ON dbo.Roles.RoleID = dbo.UserRoles.RoleID

WHERE (((dbo.Users.UserID)=@UserID) AND ((dbo.TabPermission.PermissionID)=3) AND
((dbo.Roles.RoleName)<>'Administrators') AND ((dbo.Tabs.PortalID)=5))

ORDER BY dbo.Roles.[RoleName], dbo.Tabs.TabName

Show the pages that have a page-level skin or container set in the page settings

SELECT [TabID],[PortalID],[TabName],[SkinSrc],[ContainerSrc] FROM [Tabs]

Useful when you want to remove references to containers that are causing trouble or just cleaning up.

Remove a particular container from all modules

UPDATE [TabModules]
SET [ContainerSrc] = NULL
WHERE [ContainerSrc] = '[G]Containers/Apricot_ContDark/ApriContBrownDark.ascx'

Show all modules and their containers A null value for the container indicates it is using the site default for the container.

SELECT [TabModuleID],[TabID],[ModuleID],[ContainerSrc]
FROM [TabModules]

Change a Username

If you are going to make this repeatable, you might want to wrap the logic in a transaction. You wouldn't want the two tables to be out of sync. Here is a sample as series of T-Sql statements. Run this from the SQL tab in the host menu or from SQL Server Management Studio. Should be easy to convert to a stored proc:

declare @oldName nvarchar(128)
declare @newName nvarchar(128)
declare @error_var int, @rowcount_var int
declare @newNameCount int

select @oldName = 'johndoe1'
select @newName = 'johndoe2'


begin transaction

select @newNameCount = count(*)
from Users
where Username = @newName
if @newNameCount > 0
begin
RAISERROR('Username already exists. @newName=%s', 10, 1, @newName)
ROLLBACK TRANSACTION
RETURN
end

update Users
set Username = @newName
where Username = @oldName

SELECT @error_var = @@ERROR, @rowcount_var = @@ROWCOUNT
IF @rowcount_var <> 1 OR @error_var <> 0
BEGIN
RAISERROR('Could not Update User.Username. @oldName=%s', 10, 1, @oldName)
ROLLBACK TRANSACTION
RETURN
END


update aspnet_Users
set
Username = @newName,
LoweredUserName = LOWER(@newName)
where LoweredUserName = LOWER(@oldName)

SELECT @error_var = @@ERROR, @rowcount_var = @@ROWCOUNT
IF @rowcount_var <> 1 OR @error_var <> 0
BEGIN
RAISERROR('Could not Update aspnet_Users.Username. @oldName=%s', 10, 1, @oldName)
ROLLBACK TRANSACTION
RETURN
END

Commit transaction
go


- Recycle the Application Pool after running this procedure.

This script was borrowed from an entry by Michael Levy in the following DNN Forum entry:
http://www.dnnsoftware.co...id/27822/scope/posts

Thank you Michael Levy

Record of User Logins

Put this in a Report to see who has been logging in and when.

Select
AU.Username,
U.FirstName,
U.LastName,
AM.loweredEmail,
AM.CreateDate,
AM.LastLoginDate
from
aspnet_Membership as AM with (nolock)
join aspnet_Users as AU with (nolock) on AM.UserID = AU.UserID
join Users as U with (nolock) on U.UserName = AU.Username
where
AM.ApplicationID = (select ApplicationID from dbo.aspnet_Applications where ApplicationName = 'DotNetNuke')
ORDER BY AM.LastLoginDate DESC

Script to find the stored procedures that mention a particular string. Useful for finding the stored procedures that use a field name that you changed so you can update the name.

Substitute your own string for the "ThisSearchString" below.


SELECT s.name + '.' + o.name ProcedureName
, c.text ProcedureSteps
FROM sys.syscomments c
INNER JOIN
sys.objects o
ON
c.id = o.object_id
INNER JOIN
sys.schemas s
ON
o.schema_id = s.schema_id
WHERE o.type = 'P'
AND c.text LIKE N'%ThisSearchString%'
ORDER BY s.name + '.' + o.name
, c.colid

Determine how many times each skin is used and of course which skins are actually being used:

SELECT SkinSrc AS 'Skin Name', count(TabName) AS 'Pages Using'
FROM Tabs
WHERE PortalID = 1
GROUP BY SkinSrc


Note: you can leave out the WHERE clause to return all the skins (in my case I needed the where clause because i have multiple sites under one portal)

View a list of users in a particular security role

In this example, the role chosen is 'Members'

SELECT Roles.RoleID, Roles.RoleName, Users.FirstName, Users.LastName, Users.Email
FROM UserRoles INNER JOIN
Roles ON UserRoles.RoleID = Roles.RoleID INNER JOIN
Users ON UserRoles.UserID = Users.UserID
WHERE Roles.Rolename = 'Members'
ORDER BY Lastname, FirstName

Mark all pages in portal 0 as SSL secure

UPDATE Tabs SET IsSecure = True WHERE PortalID = 0

Prepare DNN for LetsEncrypt to work

INSERT INTO HostSettings (SettingName, SettingValue, SettingIsSecure) VALUES (N'AUM_UrlsWithNoExtensionRegex', N'.asmx/|.ashx/|.svc/|.aspx/|.axd/|/.well-known', 0)

Test to see if a UserID is in a particular role or is an administrator.

The following example will test UserID=306 to see if they are either in the "Newsletter Manager" role or an administrator. You would change those two parameters to match your needs. And it would be much better if you put this into a stored procedure that passed in those two parameters instead of typing them in the SQL script.

This will return "HasRoleYes" if the user is in the roles, and "HasRoleNo" if they are not in the roles.

SELECT CAST(CASE WHEN COUNT(*) > 0 THEN 'HasRoleYes' ELSE 'HasRoleNo' END AS nvarchar(15)) AS 'HasRole'
FROM UserRoles INNER JOIN
Roles ON UserRoles.RoleID = Roles.RoleID INNER JOIN
Users ON UserRoles.UserID = Users.UserID
WHERE Roles.Rolename IN ('Newsletter Manager', 'Administrator') AND Users.UserID = '306'

Get the people in a list of named roles, including profile properties. DNN 8.0.4

SELECT

r.Rolename AS 'Program Position',
u.FirstName + ' ' + upd.MiddleName + ' ' + u.LastName AS 'Name',

upd.[Street] + '<br />' +
upd.[City] + ', ' +
upd.[Region] + ' ' +
upd.PostalCode AS Address,
u.Email
FROM dbo.Users as u INNER JOIN
dbo.UserRoles AS ur ON u.UserID = ur.UserID INNER JOIN
dbo.Roles AS r ON ur.RoleID = r.RoleID
INNER JOIN RoleGroups rg on r.RoleGroupID = rg.RoleGroupID
LEFT OUTER JOIN
(SELECT
up.UserID,

MAX(CASE WHEN ppd.PropertyName = 'Street' THEN up.PropertyValue ELSE '' END) AS 'Street',
MAX(CASE WHEN ppd.PropertyName = 'City' THEN up.PropertyValue ELSE '' END) AS 'City',
(SELECT L.[Value] FROM [dbo].[Lists] L WHERE L.EntryID = (SELECT MAX(CASE WHEN ppd.PropertyName = 'Region' THEN up.PropertyValue ELSE '' END))) AS 'Region',
MAX(CASE WHEN ppd.PropertyName = 'PostalCode' THEN up.PropertyValue ELSE '' END) AS 'PostalCode',
MAX(CASE WHEN ppd.PropertyName = 'MiddleName' THEN up.PropertyValue ELSE '' END) AS 'MiddleName'

FROM
dbo.UserProfile AS up INNER JOIN
dbo.ProfilePropertyDefinition AS ppd ON up.PropertyDefinitionID = ppd.PropertyDefinitionID

--AND ppd.PortalID = 0

GROUP BY up.UserID) AS upd
ON u.UserID = upd.UserID

ORDER BY CAST(u.Lastname as varchar(3)) ASC

should end with:
ORDER BY Users.Lastname, Users.FirstName