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!
|
|
|
|
|