Here is my script:
-- drop StoredProcedure if it exists
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ATI_ImpersonateUser_GetUsers]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ATI_ImpersonateUser_GetUsers]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE ATI_ImpersonateUser_GetUsers
-- SPROC parameters
@IncludeSuperUsers int = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF @IncludeSuperUsers = 1
-- return all users
BEGIN
WITH ProfileProperties (_uid, PropertyName, PropertyValue) AS
( SELECT up.UserID as _uid, PropertyName, up.PropertyValue
FROM dbo.UserProfile up INNER JOIN ProfilePropertyDefinition ppd ON up.PropertyDefinitionID = ppd.PropertyDefinitionID)
SELECT X.FirstName, X.LastName, X.Username, X.Email, X.DisplayName, X.Company, X.IsSuperUser, X.UserID
FROM Users u
INNER JOIN ProfileProperties pp ON u.UserID = pp._uid PIVOT (MAX(PropertyValue) FOR pp.PropertyName IN ( [Company])) X
INNER JOIN aspnet_Users au ON X.Username = au.Username
INNER JOIN aspnet_Membership am ON au.UserID = am.UserID
-- filter out deleted users
WHERE X.IsDeleted = 0;
RETURN;
END;
-- return recordset that does NOT include the superusers
WITH ProfileProperties (_uid, PropertyName, PropertyValue) AS
( SELECT up.UserID as _uid, PropertyName, up.PropertyValue
FROM dbo.UserProfile up INNER JOIN ProfilePropertyDefinition ppd ON up.PropertyDefinitionID = ppd.PropertyDefinitionID)
SELECT X.FirstName, X.LastName, X.Username, X.Email, X.DisplayName, X.Company, X.IsSuperUser, X.UserID
FROM Users u
INNER JOIN ProfileProperties pp ON u.UserID = pp._uid PIVOT (MAX(PropertyValue) FOR pp.PropertyName IN ( [Company])) X
INNER JOIN aspnet_Users au ON X.Username = au.Username
INNER JOIN aspnet_Membership am ON au.UserID = am.UserID
-- filter out deleted users
WHERE X.IsDeleted = 0 and
X.IsSuperUser = 0;
END
GO
And here is my uninstall script:
DROP PROCEDURE [dbo].[ATI_ImpersonateUser_GetUsers]
GO