PatM
Posts:
 |
| 2/5/2019 5:53 PM |
|
should end with:
ORDER BY Users.Lastname, Users.FirstName |
|
|
|
|
Deleted User
New Member
Posts:62
|
| 10/2/2018 12:28 PM |
|
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 |
|
|
|
|
Deleted User
New Member
Posts:62
|
| 7/31/2018 10:03 PM |
|
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' |
|
|
|
|
Deleted User
New Member
Posts:62
|
| 6/30/2018 10:22 AM |
|
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) |
|
|
|
|
Deleted User
New Member
Posts:62
|
| 6/30/2018 10:19 AM |
|
Mark all pages in portal 0 as SSL secure
UPDATE Tabs SET IsSecure = True WHERE PortalID = 0 |
|
|
|
|
Deleted User
New Member
Posts:62
|
| 6/27/2018 10:32 AM |
|
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 |
|
|
|
|
edlahoz
Posts:
|
| 10/16/2015 11:28 AM |
|
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) |
|
|
|
|
Deleted User
New Member
Posts:62
|
| 10/17/2013 4:54 PM |
|
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 |
|
|
|
|
Deleted User
New Member
Posts:62
|
| 10/10/2013 1:55 PM |
|
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 |
|
|
|
|
Deleted User
New Member
Posts:62
|
| 10/10/2013 1:51 PM |
|
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 |
|
|
|
|
Deleted User
New Member
Posts:62
|
| 7/21/2013 2:01 AM |
|
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] |
|
|
|
|
Deleted User
New Member
Posts:62
|
| 7/21/2013 1:57 AM |
|
Remove a particular container from all modules
UPDATE [TabModules]
SET [ContainerSrc] = NULL
WHERE [ContainerSrc] = '[G]Containers/Apricot_ContDark/ApriContBrownDark.ascx' |
|
|
|
|
Deleted User
New Member
Posts:62
|
| 7/21/2013 1:35 AM |
|
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. |
|
|
|
|
Deleted User
New Member
Posts:62
|
| 5/23/2013 4:37 PM |
|
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 |
|
|
|
|
Deleted User
New Member
Posts:62
|
| 1/19/2013 12:08 PM |
|
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'
|
|
|
|
|
Deleted User
New Member
Posts:62
|
| 7/29/2012 11:48 AM |
|
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 |
|
|
|
|
Deleted User
New Member
Posts:62
|
| 6/25/2012 11:21 AM |
|
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!
|
|
|
|
|