asp.net用户身份验证时读不到用户信息的问题 您的登录尝试不成功。请重试。 Login控件
现象1.asp.net使用自定义sql server身份验证数据库，在A机器新增用户A，可以登录成功。把程序复制到B机器上，B机器读不到A用户信息，也无法使用A用户登录。B机器上新增的用户在A机器上也读不到。
现象2.在vs2005环境下运行程序，A用户登录成功，换在IIS下浏览，A用户登录失败。
原因在于web.config中自定义的身份验证提供者中缺少了applicationname的设置
<membership defaultProvider="CustomizedMembershipProvider">
<providers>
<add connectionStringName="MyDB" minRequiredPasswordLength="1"
minRequiredNonalphanumericCharacters="0" applicationName="/"
name="CustomizedMembershipProvider" type="System.Web.Security.SqlMembershipProvider" />
</providers>
</membership>
<roleManager cacheRolesInCookie="true" cookieName=".ASPROLES"
cookieRequireSSL="true" defaultProvider="CustomizedRolelProvider">
<providers>
<add connectionStringName="MyDB" applicationName="/" name="CustomizedRolelProvider"
type="System.Web.Security.SqlRoleProvider" />
</providers>
</roleManager>
系统建立新用户的时候，根据applicationname中的路径建立不同的用户组，如果没有指定applicationname，系统会以当前项目所在的文件夹名称，例如"prj_asp",作为applicationname建立用户组，在使用iis浏览时往往会将当前项目所在的文件夹设置为网站的根目录，也就是"/"，系统就会寻找名为"/"的applicationname的用户组，结果就是找不到。所以在wen.config中设置 applicationName="/"可以解决这个问题。
用以上方法还是没有解决问题，我就把C:"WINDOWS"Microsoft.NET"Framework"v2.0.50727下的这三个文件执行到需要的数据库服务器上这三个文件:InstallCommon.SQL , InstallMembership.SQL , InstallRoles.SQL
/**********************************************************************/
/* InstallCommon.SQL */
/* */
/* Installs the tables, triggers and stored procedures necessary for */
/* supporting some features of ASP.Net */
/*
** Copyright Microsoft, Inc. 2003
** All Rights Reserved.
*/
/**********************************************************************/
PRINT '---------------------------------------'
PRINT 'Starting execution of InstallCommon.SQL'
PRINT '---------------------------------------'
GO
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON -- We don't want (NULL = NULL) == TRUE
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULL_DFLT_ON ON
GO
DECLARE @dbname nvarchar(128)
DECLARE @dboptions nvarchar(1024)
SET @dboptions = N'/**/'
SET @dbname = N'aspnetdb'
IF (NOT EXISTS (SELECT name
FROM master.dbo.sysdatabases
WHERE name = @dbname))
BEGIN
PRINT 'Creating the ' + @dbname + ' database...'
DECLARE @cmd nvarchar(500)
SET @cmd = 'CREATE DATABASE [' + @dbname + '] ' + @dboptions
EXEC(@cmd)
END
GO
USE [aspnetdb]
GO
/*************************************************************/
/*************************************************************/
/*************************************************************/
-- Create the temporary permission tables and stored procedures
-- TO preserve the permissions of an object.
--
-- We use this method instead of using CREATE (if the object
-- doesn't exist) and ALTER (if the object exists) because the
-- latter one either requires the use of dynamic SQL (which we want to
-- avoid) or writing the body of the object (e.g. an SP or view) twice,
-- once use CREATE and again using ALTER.
IF (OBJECT_ID('tempdb.#aspnet_Permissions') IS NOT NULL)
BEGIN
DROP TABLE #aspnet_Permissions
END
GO
CREATE TABLE #aspnet_Permissions
(
Owner sysname,
Object sysname,
Grantee sysname,
Grantor sysname,
ProtectType char(10),
[Action] varchar(20),
[Column] sysname
)
INSERT INTO #aspnet_Permissions
EXEC sp_helprotect
IF (EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_Setup_RestorePermissions')
AND (type = 'P')))
DROP PROCEDURE [dbo].aspnet_Setup_RestorePermissions
GO
CREATE PROCEDURE [dbo].aspnet_Setup_RestorePermissions
@name sysname
AS
BEGIN
DECLARE @object sysname
DECLARE @protectType char(10)
DECLARE @action varchar(20)
DECLARE @grantee sysname
DECLARE @cmd nvarchar(500)
DECLARE c1 cursor FORWARD_ONLY FOR
SELECT Object, ProtectType, [Action], Grantee FROM #aspnet_Permissions where Object = @name
OPEN c1
FETCH c1 INTO @object, @protectType, @action, @grantee
WHILE (@@fetch_status = 0)
BEGIN
SET @cmd = @protectType + ' ' + @action + ' on ' + @object + ' TO [' + @grantee + ']'
EXEC (@cmd)
FETCH c1 INTO @object, @protectType, @action, @grantee
END
CLOSE c1
DEALLOCATE c1
END
GO
/*************************************************************/
/*************************************************************/
/*************************************************************/
IF (EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_Setup_RemoveAllRoleMembers')
AND (type = 'P')))
DROP PROCEDURE [dbo].aspnet_Setup_RemoveAllRoleMembers
GO
CREATE PROCEDURE [dbo].aspnet_Setup_RemoveAllRoleMembers
@name sysname
AS
BEGIN
CREATE TABLE #aspnet_RoleMembers
(
Group_name sysname,
Group_id smallint,
Users_in_group sysname,
User_id smallint
)
INSERT INTO #aspnet_RoleMembers
EXEC sp_helpuser @name
DECLARE @user_id smallint
DECLARE @cmd nvarchar(500)
DECLARE c1 cursor FORWARD_ONLY FOR
SELECT User_id FROM #aspnet_RoleMembers
OPEN c1
FETCH c1 INTO @user_id
WHILE (@@fetch_status = 0)
BEGIN
SET @cmd = 'EXEC sp_droprolemember ' + '''' + @name + ''', ''' + USER_NAME(@user_id) + ''''
EXEC (@cmd)
FETCH c1 INTO @user_id
END
CLOSE c1
DEALLOCATE c1
END
GO
/*************************************************************/
/*************************************************************/
/*************************************************************/
-- Create the aspnet_Applications table.
IF (NOT EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_Applications')
AND (type = 'U')))
BEGIN
PRINT 'Creating the aspnet_Applications table...'
CREATE TABLE [dbo].aspnet_Applications (
ApplicationName nvarchar(256) NOT NULL UNIQUE,
LoweredApplicationName nvarchar(256) NOT NULL UNIQUE,
ApplicationId uniqueidentifier PRIMARY KEY NONCLUSTERED DEFAULT NEWID(),
Description nvarchar(256) )
CREATE CLUSTERED INDEX aspnet_Applications_Index ON [dbo].aspnet_Applications(LoweredApplicationName)
END
GO
/*************************************************************/
/*************************************************************/
/*************************************************************/
-- Create the aspnet_Users table
IF (NOT EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_Users')
AND (type = 'U')))
BEGIN
PRINT 'Creating the aspnet_Users table...'
CREATE TABLE [dbo].aspnet_Users (
ApplicationId uniqueidentifier NOT NULL FOREIGN KEY REFERENCES [dbo].aspnet_Applications(ApplicationId),
UserId uniqueidentifier NOT NULL PRIMARY KEY NONCLUSTERED DEFAULT NEWID(),
UserName nvarchar(256) NOT NULL,
LoweredUserName nvarchar(256) NOT NULL,
MobileAlias nvarchar(16) DEFAULT NULL,
IsAnonymous bit NOT NULL DEFAULT 0,
LastActivityDate DATETIME NOT NULL)
CREATE UNIQUE CLUSTERED INDEX aspnet_Users_Index ON [dbo].aspnet_Users(ApplicationId, LoweredUserName)
CREATE NONCLUSTERED INDEX aspnet_Users_Index2 ON [dbo].aspnet_Users(ApplicationId, LastActivityDate)
END
GO
/*************************************************************/
/*************************************************************/
/*************************************************************/
-- Create the aspnet_SchemaVersions table
IF (NOT EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_SchemaVersions')
AND (type = 'U')))
BEGIN
PRINT 'Creating the aspnet_SchemaVersions table...'
CREATE TABLE [dbo].aspnet_SchemaVersions (
Feature nvarchar(128) NOT NULL PRIMARY KEY CLUSTERED( Feature, CompatibleSchemaVersion ),
CompatibleSchemaVersion nvarchar(128) NOT NULL,
IsCurrentVersion bit NOT NULL )
END
GO
/*************************************************************/
/*************************************************************/
------------- Create Stored Procedures
/*************************************************************/
/*************************************************************/
-- RegisterSchemaVersion SP
IF (EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_RegisterSchemaVersion')
AND (type = 'P')))
DROP PROCEDURE [dbo].aspnet_RegisterSchemaVersion
GO
CREATE PROCEDURE [dbo].aspnet_RegisterSchemaVersion
@Feature nvarchar(128),
@CompatibleSchemaVersion nvarchar(128),
@IsCurrentVersion bit,
@RemoveIncompatibleSchema bit
AS
BEGIN
IF( @RemoveIncompatibleSchema = 1 )
BEGIN
DELETE FROM dbo.aspnet_SchemaVersions WHERE Feature = LOWER( @Feature )
END
ELSE
BEGIN
IF( @IsCurrentVersion = 1 )
BEGIN
UPDATE dbo.aspnet_SchemaVersions
SET IsCurrentVersion = 0
WHERE Feature = LOWER( @Feature )
END
END
INSERT dbo.aspnet_SchemaVersions( Feature, CompatibleSchemaVersion, IsCurrentVersion )
VALUES( LOWER( @Feature ), @CompatibleSchemaVersion, @IsCurrentVersion )
END
GO
DECLARE @command nvarchar(4000)
SET @command = 'GRANT EXECUTE ON [dbo].aspnet_Setup_RestorePermissions TO ' + QUOTENAME(user)
EXEC (@command)
SET @command = 'GRANT EXECUTE ON [dbo].aspnet_RegisterSchemaVersion TO ' + QUOTENAME(user)
EXEC (@command)
GO
-- Restore the permissions
EXEC [dbo].aspnet_Setup_RestorePermissions N'aspnet_RegisterSchemaVersion'
GO
-- Create common schema version
EXEC [dbo].aspnet_RegisterSchemaVersion N'Common', N'1', 1, 1
GO
/*************************************************************/
/*************************************************************/
-- CheckSchemaVersion SP
IF (EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_CheckSchemaVersion')
AND (type = 'P')))
DROP PROCEDURE [dbo].aspnet_CheckSchemaVersion
GO
CREATE PROCEDURE [dbo].aspnet_CheckSchemaVersion
@Feature nvarchar(128),
@CompatibleSchemaVersion nvarchar(128)
AS
BEGIN
IF (EXISTS( SELECT *
FROM dbo.aspnet_SchemaVersions
WHERE Feature = LOWER( @Feature ) AND
CompatibleSchemaVersion = @CompatibleSchemaVersion ))
RETURN 0
RETURN 1
END
GO
-- Restore the permissions
EXEC [dbo].aspnet_Setup_RestorePermissions N'aspnet_CheckSchemaVersion'
GO
/*************************************************************/
/*************************************************************/
-- CreateApplication SP
IF (EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_Applications_CreateApplication')
AND (type = 'P')))
DROP PROCEDURE [dbo].aspnet_Applications_CreateApplication
GO
CREATE PROCEDURE [dbo].aspnet_Applications_CreateApplication
@ApplicationName nvarchar(256),
@ApplicationId uniqueidentifier OUTPUT
AS
BEGIN
SELECT @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF(@ApplicationId IS NULL)
BEGIN
DECLARE @TranStarted bit
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
SELECT @ApplicationId = ApplicationId
FROM dbo.aspnet_Applications WITH (UPDLOCK, HOLDLOCK)
WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF(@ApplicationId IS NULL)
BEGIN
SELECT @ApplicationId = NEWID()
INSERT dbo.aspnet_Applications (ApplicationId, ApplicationName, LoweredApplicationName)
VALUES (@ApplicationId, @ApplicationName, LOWER(@ApplicationName))
END
IF( @TranStarted = 1 )
BEGIN
IF(@@ERROR = 0)
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END
ELSE
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
END
END
END
GO
-- Restore the permissions
EXEC [dbo].aspnet_Setup_RestorePermissions N'aspnet_Applications_CreateApplication'
GO
/*************************************************************/
/*************************************************************/
-- UnRegisterSchemaVersion SP
IF (EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_UnRegisterSchemaVersion')
AND (type = 'P')))
DROP PROCEDURE [dbo].aspnet_UnRegisterSchemaVersion
GO
CREATE PROCEDURE [dbo].aspnet_UnRegisterSchemaVersion
@Feature nvarchar(128),
@CompatibleSchemaVersion nvarchar(128)
AS
BEGIN
DELETE FROM dbo.aspnet_SchemaVersions
WHERE Feature = LOWER(@Feature) AND @CompatibleSchemaVersion = CompatibleSchemaVersion
END
GO
-- Restore the permissions
EXEC [dbo].aspnet_Setup_RestorePermissions N'aspnet_UnRegisterSchemaVersion'
GO
/*************************************************************/
/*************************************************************/
-- CreateUser SP
IF (EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_Users_CreateUser')
AND (type = 'P')))
DROP PROCEDURE [dbo].aspnet_Users_CreateUser
GO
CREATE PROCEDURE [dbo].aspnet_Users_CreateUser
@ApplicationId uniqueidentifier,
@UserName nvarchar(256),
@IsUserAnonymous bit,
@LastActivityDate DATETIME,
@UserId uniqueidentifier OUTPUT
AS
BEGIN
IF( @UserId IS NULL )
SELECT @UserId = NEWID()
ELSE
BEGIN
IF( EXISTS( SELECT UserId FROM dbo.aspnet_Users
WHERE @UserId = UserId ) )
RETURN -1
END
INSERT dbo.aspnet_Users (ApplicationId, UserId, UserName, LoweredUserName, IsAnonymous, LastActivityDate)
VALUES (@ApplicationId, @UserId, @UserName, LOWER(@UserName), @IsUserAnonymous, @LastActivityDate)
RETURN 0
END
GO
-- Restore the permissions
EXEC [dbo].aspnet_Setup_RestorePermissions N'aspnet_Users_CreateUser'
GO
/*************************************************************/
/*************************************************************/
--- DeleteUser SP
IF (EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_Users_DeleteUser')
AND (type = 'P')))
DROP PROCEDURE [dbo].aspnet_Users_DeleteUser
GO
CREATE PROCEDURE [dbo].aspnet_Users_DeleteUser
@ApplicationName nvarchar(256),
@UserName nvarchar(256),
@TablesToDeleteFrom int,
@NumTablesDeletedFrom int OUTPUT
AS
BEGIN
DECLARE @UserId uniqueidentifier
SELECT @UserId = NULL
SELECT @NumTablesDeletedFrom = 0
DECLARE @TranStarted bit
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
DECLARE @ErrorCode int
DECLARE @RowCount int
SET @ErrorCode = 0
SET @RowCount = 0
SELECT @UserId = u.UserId
FROM dbo.aspnet_Users u, dbo.aspnet_Applications a
WHERE u.LoweredUserName = LOWER(@UserName)
AND u.ApplicationId = a.ApplicationId
AND LOWER(@ApplicationName) = a.LoweredApplicationName
IF (@UserId IS NULL)
BEGIN
GOTO Cleanup
END
-- Delete from Membership table if (@TablesToDeleteFrom & 1) is set
IF ((@TablesToDeleteFrom & 1) <> 0 AND
(EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_MembershipUsers') AND (type = 'V'))))
BEGIN
DELETE FROM dbo.aspnet_Membership WHERE @UserId = UserId
SELECT @ErrorCode = @@ERROR,
@RowCount = @@ROWCOUNT
IF( @ErrorCode <> 0 )
GOTO Cleanup
IF (@RowCount <> 0)
SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
END
-- Delete from aspnet_UsersInRoles table if (@TablesToDeleteFrom & 2) is set
IF ((@TablesToDeleteFrom & 2) <> 0 AND
(EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_UsersInRoles') AND (type = 'V'))) )
BEGIN
DELETE FROM dbo.aspnet_UsersInRoles WHERE @UserId = UserId
SELECT @ErrorCode = @@ERROR,
@RowCount = @@ROWCOUNT
IF( @ErrorCode <> 0 )
GOTO Cleanup
IF (@RowCount <> 0)
SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
END
-- Delete from aspnet_Profile table if (@TablesToDeleteFrom & 4) is set
IF ((@TablesToDeleteFrom & 4) <> 0 AND
(EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_Profiles') AND (type = 'V'))) )
BEGIN
DELETE FROM dbo.aspnet_Profile WHERE @UserId = UserId
SELECT @ErrorCode = @@ERROR,
@RowCount = @@ROWCOUNT
IF( @ErrorCode <> 0 )
GOTO Cleanup
IF (@RowCount <> 0)
SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
END
-- Delete from aspnet_PersonalizationPerUser table if (@TablesToDeleteFrom & 8) is set
IF ((@TablesToDeleteFrom & 8) <> 0 AND
(EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_WebPartState_User') AND (type = 'V'))) )
BEGIN
DELETE FROM dbo.aspnet_PersonalizationPerUser WHERE @UserId = UserId
SELECT @ErrorCode = @@ERROR,
@RowCount = @@ROWCOUNT
IF( @ErrorCode <> 0 )
GOTO Cleanup
IF (@RowCount <> 0)
SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
END
-- Delete from aspnet_Users table if (@TablesToDeleteFrom & 1,2,4 & 8) are all set
IF ((@TablesToDeleteFrom & 1) <> 0 AND
(@TablesToDeleteFrom & 2) <> 0 AND
(@TablesToDeleteFrom & 4) <> 0 AND
(@TablesToDeleteFrom & 8) <> 0 AND
(EXISTS (SELECT UserId FROM dbo.aspnet_Users WHERE @UserId = UserId)))
BEGIN
DELETE FROM dbo.aspnet_Users WHERE @UserId = UserId
SELECT @ErrorCode = @@ERROR,
@RowCount = @@ROWCOUNT
IF( @ErrorCode <> 0 )
GOTO Cleanup
IF (@RowCount <> 0)
SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
END
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END
RETURN 0
Cleanup:
SET @NumTablesDeletedFrom = 0
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN @ErrorCode
END
GO
/*************************************************************/
/*************************************************************/
/*************************************************************/
/*************************************************************/
-- Restore the permissions
EXEC [dbo].aspnet_Setup_RestorePermissions N'aspnet_Users_DeleteUser'
GO
/*************************************************************/
/*************************************************************/
--- aspnet_AnyDataInTables SP
IF (EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_AnyDataInTables')
AND (type = 'P')))
DROP PROCEDURE [dbo].aspnet_AnyDataInTables
GO
CREATE PROCEDURE [dbo].aspnet_AnyDataInTables
@TablesToCheck int
AS
BEGIN
-- Check Membership table if (@TablesToCheck & 1) is set
IF ((@TablesToCheck & 1) <> 0 AND
(EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_MembershipUsers') AND (type = 'V'))))
BEGIN
IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_Membership))
BEGIN
SELECT N'aspnet_Membership'
RETURN
END
END
-- Check aspnet_Roles table if (@TablesToCheck & 2) is set
IF ((@TablesToCheck & 2) <> 0 AND
(EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_Roles') AND (type = 'V'))) )
BEGIN
IF (EXISTS(SELECT TOP 1 RoleId FROM dbo.aspnet_Roles))
BEGIN
SELECT N'aspnet_Roles'
RETURN
END
END
-- Check aspnet_Profile table if (@TablesToCheck & 4) is set
IF ((@TablesToCheck & 4) <> 0 AND
(EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_Profiles') AND (type = 'V'))) )
BEGIN
IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_Profile))
BEGIN
SELECT N'aspnet_Profile'
RETURN
END
END
-- Check aspnet_PersonalizationPerUser table if (@TablesToCheck & 8) is set
IF ((@TablesToCheck & 8) <> 0 AND
(EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_WebPartState_User') AND (type = 'V'))) )
BEGIN
IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_PersonalizationPerUser))
BEGIN
SELECT N'aspnet_PersonalizationPerUser'
RETURN
END
END
-- Check aspnet_PersonalizationPerUser table if (@TablesToCheck & 16) is set
IF ((@TablesToCheck & 16) <> 0 AND
(EXISTS (SELECT name FROM sysobjects WHERE (name = N'aspnet_WebEvent_LogEvent') AND (type = 'P'))) )
BEGIN
IF (EXISTS(SELECT TOP 1 * FROM dbo.aspnet_WebEvent_Events))
BEGIN
SELECT N'aspnet_WebEvent_Events'
RETURN
END
END
-- Check aspnet_Users table if (@TablesToCheck & 1,2,4 & 8) are all set
IF ((@TablesToCheck & 1) <> 0 AND
(@TablesToCheck & 2) <> 0 AND
(@TablesToCheck & 4) <> 0 AND
(@TablesToCheck & 8) <> 0 AND
(@TablesToCheck & 32) <> 0 AND
(@TablesToCheck & 128) <> 0 AND
(@TablesToCheck & 256) <> 0 AND
(@TablesToCheck & 512) <> 0 AND
(@TablesToCheck & 1024) <> 0)
BEGIN
IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_Users))
BEGIN
SELECT N'aspnet_Users'
RETURN
END
IF (EXISTS(SELECT TOP 1 ApplicationId FROM dbo.aspnet_Applications))
BEGIN
SELECT N'aspnet_Applications'
RETURN
END
END
END
GO
/*************************************************************/
/*************************************************************/
/*************************************************************/
/*************************************************************/
DECLARE @command nvarchar(400)
SET @command = 'GRANT EXECUTE ON [dbo].aspnet_AnyDataInTables TO ' + QUOTENAME(user)
EXEC (@command)
GO
-- Restore the permissions
EXEC [dbo].aspnet_Setup_RestorePermissions N'aspnet_AnyDataInTables'
GO
/*************************************************************/
/*************************************************************/
IF (NOT EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'vw_aspnet_Applications')
AND (type = 'V')))
BEGIN
PRINT 'Creating the vw_aspnet_Applications view...'
EXEC('
CREATE VIEW [dbo].[vw_aspnet_Applications]
AS SELECT [dbo].[aspnet_Applications].[ApplicationName], [dbo].[aspnet_Applications].[LoweredApplicationName], [dbo].[aspnet_Applications].[ApplicationId], [dbo].[aspnet_Applications].[Description]
FROM [dbo].[aspnet_Applications]
')
END
-- Restore the permissions
EXEC [dbo].aspnet_Setup_RestorePermissions N'vw_aspnet_Applications'
GO
/*************************************************************/
/*************************************************************/
IF (NOT EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'vw_aspnet_Users')
AND (type = 'V')))
BEGIN
PRINT 'Creating the vw_aspnet_Users view...'
EXEC('
CREATE VIEW [dbo].[vw_aspnet_Users]
AS SELECT [dbo].[aspnet_Users].[ApplicationId], [dbo].[aspnet_Users].[UserId], [dbo].[aspnet_Users].[UserName], [dbo].[aspnet_Users].[LoweredUserName], [dbo].[aspnet_Users].[MobileAlias], [dbo].[aspnet_Users].[IsAnonymous], [dbo].[aspnet_Users].[LastActivityDate]
FROM [dbo].[aspnet_Users]
')
END
-- Restore the permissions
EXEC [dbo].aspnet_Setup_RestorePermissions N'vw_aspnet_Users'
GO
/*************************************************************/
/*************************************************************/
DECLARE @command nvarchar(4000)
SET @command = 'REVOKE EXECUTE ON [dbo].aspnet_Setup_RestorePermissions from ' + QUOTENAME(user)
EXEC (@command)
SET @command = 'REVOKE EXECUTE ON [dbo].aspnet_RegisterSchemaVersion from ' + QUOTENAME(user)
EXEC (@command)
GO
DROP TABLE #aspnet_Permissions
GO
PRINT '----------------------------------------'
PRINT 'Completed execution of InstallCommon.SQL'
PRINT '----------------------------------------'
/**********************************************************************/
/* InstallMembership.SQL */
/* */
/* Installs the tables, triggers and stored procedures necessary for */
/* supporting the aspnet feature of ASP.Net */
/* */
/* InstallCommon.sql must be run before running this file. */
/*
** Copyright Microsoft, Inc. 2002
** All Rights Reserved.
*/
/**********************************************************************/
PRINT '-------------------------------------------'
PRINT 'Starting execution of InstallMembership.SQL'
PRINT '-------------------------------------------'
GO
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON -- We don't want (NULL = NULL) == TRUE
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULL_DFLT_ON ON
GO
/*************************************************************/
/*************************************************************/
/*************************************************************/
/*************************************************************/
/*************************************************************/
DECLARE @dbname nvarchar(128)
SET @dbname = N'aspnetdb'
IF (NOT EXISTS (SELECT name
FROM master.dbo.sysdatabases
WHERE ('[' + name + ']' = @dbname OR name = @dbname)))
BEGIN
RAISERROR('The database ''%s'' cannot be found. Please run InstallCommon.sql first.', 18, 1, @dbname)
END
GO
USE [aspnetdb]
GO
IF (NOT EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_Applications')
AND (type = 'U')))
BEGIN
RAISERROR('The table ''aspnet_Applications'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
END
IF (NOT EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_Users')
AND (type = 'U')))
BEGIN
RAISERROR('The table ''aspnet_Users'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
END
IF (NOT EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_Applications_CreateApplication')
AND (type = 'P')))
BEGIN
RAISERROR('The stored procedure ''aspnet_Applications_CreateApplication'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
END
IF (NOT EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_Users_CreateUser')
AND (type = 'P')))
BEGIN
RAISERROR('The stored procedure ''aspnet_Users_CreateUser'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
END
IF (NOT EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_Users_DeleteUser')
AND (type = 'P')))
BEGIN
RAISERROR('The stored procedure ''aspnet_Users_DeleteUser'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
END
/*************************************************************/
/*************************************************************/
IF (NOT EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_Membership')
AND (type = 'U')))
BEGIN
PRINT 'Creating the aspnet_Membership table...'
CREATE TABLE dbo.aspnet_Membership (
ApplicationId uniqueidentifier NOT NULL FOREIGN KEY REFERENCES dbo.aspnet_Applications(ApplicationId),
UserId uniqueidentifier NOT NULL PRIMARY KEY NONCLUSTERED FOREIGN KEY REFERENCES dbo.aspnet_Users(UserId),
Password nvarchar(128) NOT NULL,
PasswordFormat int NOT NULL DEFAULT 0,
PasswordSalt nvarchar(128) NOT NULL,
MobilePIN nvarchar(16),
Email nvarchar(256),
LoweredEmail nvarchar(256),
PasswordQuestion nvarchar(256),
PasswordAnswer nvarchar(128),
IsApproved bit NOT NULL,
IsLockedOut bit NOT NULL,
CreateDate datetime NOT NULL,
LastLoginDate datetime NOT NULL,
LastPasswordChangedDate datetime NOT NULL,
LastLockoutDate datetime NOT NULL,
FailedPasswordAttemptCount int NOT NULL,
FailedPasswordAttemptWindowStart datetime NOT NULL,
FailedPasswordAnswerAttemptCount int NOT NULL,
FailedPasswordAnswerAttemptWindowStart datetime NOT NULL,
Comment ntext )
CREATE CLUSTERED INDEX aspnet_Membership_index ON aspnet_Membership(ApplicationId, LoweredEmail)
END
GO
/*************************************************************/
/*************************************************************/
/*************************************************************/
DECLARE @ver int
DECLARE @version nchar(100)
DECLARE @dot int
DECLARE @hyphen int
DECLARE @SqlToExec nchar(400)
SELECT @ver = 8
SELECT @version = @@Version
SELECT @hyphen = CHARINDEX(N' - ', @version)
IF (NOT(@hyphen IS NULL) AND @hyphen > 0)
BEGIN
SELECT @hyphen = @hyphen + 3
SELECT @dot = CHARINDEX(N'.', @version, @hyphen)
IF (NOT(@dot IS NULL) AND @dot > @hyphen)
BEGIN
SELECT @version = SUBSTRING(@version, @hyphen, @dot - @hyphen)
SELECT @ver = CONVERT(int, @version)
END
END
/*************************************************************/
IF (@ver >= 8)
EXEC sp_tableoption N'aspnet_Membership', 'text in row', 3000
/*************************************************************/
/*************************************************************/
IF (EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_Membership_CreateUser')
AND (type = 'P')))
DROP PROCEDURE dbo.aspnet_Membership_CreateUser
GO
CREATE PROCEDURE dbo.aspnet_Membership_CreateUser
@ApplicationName nvarchar(256),
@UserName nvarchar(256),
@Password nvarchar(128),
@PasswordSalt nvarchar(128),
@Email nvarchar(256),
@PasswordQuestion nvarchar(256),
@PasswordAnswer nvarchar(128),
@IsApproved bit,
@CurrentTimeUtc datetime,
@CreateDate datetime = NULL,
@UniqueEmail int = 0,
@PasswordFormat int = 0,
@UserId uniqueidentifier OUTPUT
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
DECLARE @NewUserId uniqueidentifier
SELECT @NewUserId = NULL
DECLARE @IsLockedOut bit
SET @IsLockedOut = 0
DECLARE @LastLockoutDate datetime
SET @LastLockoutDate = CONVERT( datetime, '17540101', 112 )
DECLARE @FailedPasswordAttemptCount int
SET @FailedPasswordAttemptCount = 0
DECLARE @FailedPasswordAttemptWindowStart datetime
SET @FailedPasswordAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
DECLARE @FailedPasswordAnswerAttemptCount int
SET @FailedPasswordAnswerAttemptCount = 0
DECLARE @FailedPasswordAnswerAttemptWindowStart datetime
SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
DECLARE @NewUserCreated bit
DECLARE @ReturnValue int
SET @ReturnValue = 0
DECLARE @ErrorCode int
SET @ErrorCode = 0
DECLARE @TranStarted bit
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
SET @CreateDate = @CurrentTimeUtc
SELECT @NewUserId = UserId FROM dbo.aspnet_Users WHERE LOWER(@UserName) = LoweredUserName AND @ApplicationId = ApplicationId
IF ( @NewUserId IS NULL )
BEGIN
SET @NewUserId = @UserId
EXEC @ReturnValue = dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, 0, @CreateDate, @NewUserId OUTPUT
SET @NewUserCreated = 1
END
ELSE
BEGIN
SET @NewUserCreated = 0
IF( @NewUserId <> @UserId AND @UserId IS NOT NULL )
BEGIN
SET @ErrorCode = 6
GOTO Cleanup
END
END
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
IF( @ReturnValue = -1 )
BEGIN
SET @ErrorCode = 10
GOTO Cleanup
END
IF ( EXISTS ( SELECT UserId
FROM dbo.aspnet_Membership
WHERE @NewUserId = UserId ) )
BEGIN
SET @ErrorCode = 6
GOTO Cleanup
END
SET @UserId = @NewUserId
IF (@UniqueEmail = 1)
BEGIN
IF (EXISTS (SELECT *
FROM dbo.aspnet_Membership m WITH ( UPDLOCK, HOLDLOCK )
WHERE ApplicationId = @ApplicationId AND LoweredEmail = LOWER(@Email)))
BEGIN
SET @ErrorCode = 7
GOTO Cleanup
END
END
IF (@NewUserCreated = 0)
BEGIN
UPDATE dbo.aspnet_Users
SET LastActivityDate = @CreateDate
WHERE @UserId = UserId
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
END
INSERT INTO dbo.aspnet_Membership
( ApplicationId,
UserId,
Password,
PasswordSalt,
Email,
LoweredEmail,
PasswordQuestion,
PasswordAnswer,
PasswordFormat,
IsApproved,
IsLockedOut,
CreateDate,
LastLoginDate,
LastPasswordChangedDate,
LastLockoutDate,
FailedPasswordAttemptCount,
FailedPasswordAttemptWindowStart,
FailedPasswordAnswerAttemptCount,
FailedPasswordAnswerAttemptWindowStart )
VALUES ( @ApplicationId,
@UserId,
@Password,
@PasswordSalt,
@Email,
LOWER(@Email),
@PasswordQuestion,
@PasswordAnswer,
@PasswordFormat,
@IsApproved,
@IsLockedOut,
@CreateDate,
@CreateDate,
@CreateDate,
@LastLockoutDate,
@FailedPasswordAttemptCount,
@FailedPasswordAttemptWindowStart,
@FailedPasswordAnswerAttemptCount,
@FailedPasswordAnswerAttemptWindowStart )
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END
RETURN 0
Cleanup:
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN @ErrorCode
END
GO
/*************************************************************/
/*************************************************************/
IF (EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_Membership_GetUserByName')
AND (type = 'P')))
DROP PROCEDURE dbo.aspnet_Membership_GetUserByName
GO
CREATE PROCEDURE dbo.aspnet_Membership_GetUserByName
@ApplicationName nvarchar(256),
@UserName nvarchar(256),
@CurrentTimeUtc datetime,
@UpdateLastActivity bit = 0
AS
BEGIN
DECLARE @UserId uniqueidentifier
IF (@UpdateLastActivity = 1)
BEGIN
SELECT TOP 1 m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
m.CreateDate, m.LastLoginDate, @CurrentTimeUtc, m.LastPasswordChangedDate,
u.UserId, m.IsLockedOut,m.LastLockoutDate
FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
WHERE LOWER(@ApplicationName) = a.LoweredApplicationName AND
u.ApplicationId = a.ApplicationId AND
LOWER(@UserName) = u.LoweredUserName AND u.UserId = m.UserId
IF (@@ROWCOUNT = 0) -- Username not found
RETURN -1
UPDATE dbo.aspnet_Users
SET LastActivityDate = @CurrentTimeUtc
WHERE @UserId = UserId
END
ELSE
BEGIN
SELECT TOP 1 m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate,
u.UserId, m.IsLockedOut,m.LastLockoutDate
FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
WHERE LOWER(@ApplicationName) = a.LoweredApplicationName AND
u.ApplicationId = a.ApplicationId AND
LOWER(@UserName) = u.LoweredUserName AND u.UserId = m.UserId
IF (@@ROWCOUNT = 0) -- Username not found
RETURN -1
END
RETURN 0
END
GO
/*************************************************************/
/*************************************************************/
IF (EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_Membership_GetUserByUserId')
AND (type = 'P')))
DROP PROCEDURE dbo.aspnet_Membership_GetUserByUserId
GO
CREATE PROCEDURE dbo.aspnet_Membership_GetUserByUserId
@UserId uniqueidentifier,
@CurrentTimeUtc datetime,
@UpdateLastActivity bit = 0
AS
BEGIN
IF ( @UpdateLastActivity = 1 )
BEGIN
UPDATE dbo.aspnet_Users
SET LastActivityDate = @CurrentTimeUtc
FROM dbo.aspnet_Users
WHERE @UserId = UserId
IF ( @@ROWCOUNT = 0 ) -- User ID not found
RETURN -1
END
SELECT m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
m.CreateDate, m.LastLoginDate, u.LastActivityDate,
m.LastPasswordChangedDate, u.UserName, m.IsLockedOut,
m.LastLockoutDate
FROM dbo.aspnet_Users u, dbo.aspnet_Membership m
WHERE @UserId = u.UserId AND u.UserId = m.UserId
IF ( @@ROWCOUNT = 0 ) -- User ID not found
RETURN -1
RETURN 0
END
GO
/*************************************************************/
/*************************************************************/
IF (EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_Membership_GetUserByEmail')
AND (type = 'P')))
DROP PROCEDURE dbo.aspnet_Membership_GetUserByEmail
GO
CREATE PROCEDURE dbo.aspnet_Membership_GetUserByEmail
@ApplicationName nvarchar(256),
@Email nvarchar(256)
AS
BEGIN
IF( @Email IS NULL )
SELECT u.UserName
FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
WHERE LOWER(@ApplicationName) = a.LoweredApplicationName AND
u.ApplicationId = a.ApplicationId AND
u.UserId = m.UserId AND
m.LoweredEmail IS NULL
ELSE
SELECT u.UserName
FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
WHERE LOWER(@ApplicationName) = a.LoweredApplicationName AND
u.ApplicationId = a.ApplicationId AND
u.UserId = m.UserId AND
LOWER(@Email) = m.LoweredEmail
IF (@@rowcount = 0)
RETURN(1)
RETURN(0)
END
GO
/*************************************************************/
/*************************************************************/
IF ( EXISTS( SELECT name
FROM sysobjects
WHERE ( name = N'aspnet_Membership_GetPasswordWithFormat' )
AND ( type = 'P' ) ) )
DROP PROCEDURE dbo.aspnet_Membership_GetPasswordWithFormat
GO
CREATE PROCEDURE dbo.aspnet_Membership_GetPasswordWithFormat
@ApplicationName nvarchar(256),
@UserName nvarchar(256),
@UpdateLastLoginActivityDate bit,
@CurrentTimeUtc datetime
AS
BEGIN
DECLARE @IsLockedOut bit
DECLARE @UserId uniqueidentifier
DECLARE @Password nvarchar(128)
DECLARE @PasswordSalt nvarchar(128)
DECLARE @PasswordFormat int
DECLARE @FailedPasswordAttemptCount int
DECLARE @FailedPasswordAnswerAttemptCount int
DECLARE @IsApproved bit
DECLARE @LastActivityDate datetime
DECLARE @LastLoginDate datetime
SELECT @UserId = NULL
SELECT @UserId = u.UserId, @IsLockedOut = m.IsLockedOut, @Password=Password, @PasswordFormat=PasswordFormat,
@PasswordSalt=PasswordSalt, @FailedPasswordAttemptCount=FailedPasswordAttemptCount,
@FailedPasswordAnswerAttemptCount=FailedPasswordAnswerAttemptCount, @IsApproved=IsApproved,
@LastActivityDate = LastActivityDate, @LastLoginDate = LastLoginDate
FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
WHERE LOWER(@ApplicationName) = a.LoweredApplicationName AND
u.ApplicationId = a.ApplicationId AND
u.UserId = m.UserId AND
LOWER(@UserName) = u.LoweredUserName
IF (@UserId IS NULL)
RETURN 1
IF (@IsLockedOut = 1)
RETURN 99
SELECT @Password, @PasswordFormat, @PasswordSalt, @FailedPasswordAttemptCount,
@FailedPasswordAnswerAttemptCount, @IsApproved, @LastLoginDate, @LastActivityDate
IF (@UpdateLastLoginActivityDate = 1 AND @IsApproved = 1)
BEGIN
UPDATE dbo.aspnet_Membership
SET LastLoginDate = @CurrentTimeUtc
WHERE UserId = @UserId
UPDATE dbo.aspnet_Users
SET LastActivityDate = @CurrentTimeUtc
WHERE @UserId = UserId
END
RETURN 0
END
GO
/*************************************************************/
/*************************************************************/
IF ( EXISTS( SELECT name
FROM sysobjects
WHERE ( name = N'aspnet_Membership_UpdateUserInfo' )
AND ( type = 'P' ) ) )
DROP PROCEDURE dbo.aspnet_Membership_UpdateUserInfo
GO
CREATE PROCEDURE dbo.aspnet_Membership_UpdateUserInfo
@ApplicationName nvarchar(256),
@UserName nvarchar(256),
@IsPasswordCorrect bit,
@UpdateLastLoginActivityDate bit,
@MaxInvalidPasswordAttempts int,
@PasswordAttemptWindow int,
@CurrentTimeUtc datetime,
@LastLoginDate datetime,
@LastActivityDate datetime
AS
BEGIN
DECLARE @UserId uniqueidentifier
DECLARE @IsApproved bit
DECLARE @IsLockedOut bit
DECLARE @LastLockoutDate datetime
DECLARE @FailedPasswordAttemptCount int
DECLARE @FailedPasswordAttemptWindowStart datetime
DECLARE @FailedPasswordAnswerAttemptCount int
DECLARE @FailedPasswordAnswerAttemptWindowStart datetime
DECLARE @ErrorCode int
SET @ErrorCode = 0
DECLARE @TranStarted bit
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
SELECT @UserId = u.UserId,
@IsApproved = m.IsApproved,
@IsLockedOut = m.IsLockedOut,
@LastLockoutDate = m.LastLockoutDate,
@FailedPasswordAttemptCount = m.FailedPasswordAttemptCount,
@FailedPasswordAttemptWindowStart = m.FailedPasswordAttemptWindowStart,
@FailedPasswordAnswerAttemptCount = m.FailedPasswordAnswerAttemptCount,
@FailedPasswordAnswerAttemptWindowStart = m.FailedPasswordAnswerAttemptWindowStart
FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m WITH ( UPDLOCK )
WHERE LOWER(@ApplicationName) = a.LoweredApplicationName AND
u.ApplicationId = a.ApplicationId AND
u.UserId = m.UserId AND
LOWER(@UserName) = u.LoweredUserName
IF ( @@rowcount = 0 )
BEGIN
SET @ErrorCode = 1
GOTO Cleanup
END
IF( @IsLockedOut = 1 )
BEGIN
GOTO Cleanup
END
IF( @IsPasswordCorrect = 0 )
BEGIN
IF( @CurrentTimeUtc > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAttemptWindowStart ) )
BEGIN
SET @FailedPasswordAttemptWindowStart = @CurrentTimeUtc
SET @FailedPasswordAttemptCount = 1
END
ELSE
BEGIN
SET @FailedPasswordAttemptWindowStart = @CurrentTimeUtc
SET @FailedPasswordAttemptCount = @FailedPasswordAttemptCount + 1
END
BEGIN
IF( @FailedPasswordAttemptCount >= @MaxInvalidPasswordAttempts )
BEGIN
SET @IsLockedOut = 1
SET @LastLockoutDate = @CurrentTimeUtc
END
END
END
ELSE
BEGIN
IF( @FailedPasswordAttemptCount > 0 OR @FailedPasswordAnswerAttemptCount > 0 )
BEGIN
SET @FailedPasswordAttemptCount = 0
SET @FailedPasswordAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
SET @FailedPasswordAnswerAttemptCount = 0
SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
SET @LastLockoutDate = CONVERT( datetime, '17540101', 112 )
END
END
IF( @UpdateLastLoginActivityDate = 1 )
BEGIN
UPDATE dbo.aspnet_Users
SET LastActivityDate = @LastActivityDate
WHERE @UserId = UserId
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
UPDATE dbo.aspnet_Membership
SET LastLoginDate = @LastLoginDate
WHERE UserId = @UserId
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
END
UPDATE dbo.aspnet_Membership
SET IsLockedOut = @IsLockedOut, LastLockoutDate = @LastLockoutDate,
FailedPasswordAttemptCount = @FailedPasswordAttemptCount,
FailedPasswordAttemptWindowStart = @FailedPasswordAttemptWindowStart,
FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount,
FailedPasswordAnswerAttemptWindowStart = @FailedPasswordAnswerAttemptWindowStart
WHERE @UserId = UserId
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END
RETURN @ErrorCode
Cleanup:
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN @ErrorCode
END
GO
/*************************************************************/
/*************************************************************/
IF (EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_Membership_GetPassword')
AND (type = 'P')))
DROP PROCEDURE dbo.aspnet_Membership_GetPassword
GO
CREATE PROCEDURE dbo.aspnet_Membership_GetPassword
@ApplicationName nvarchar(256),
@UserName nvarchar(256),
@MaxInvalidPasswordAttempts int,
@PasswordAttemptWindow int,
@CurrentTimeUtc datetime,
@PasswordAnswer nvarchar(128) = NULL
AS
BEGIN
DECLARE @UserId uniqueidentifier
DECLARE @PasswordFormat int
DECLARE @Password nvarchar(128)
DECLARE @passAns nvarchar(128)
DECLARE @IsLockedOut bit
DECLARE @LastLockoutDate datetime
DECLARE @FailedPasswordAttemptCount int
DECLARE @FailedPasswordAttemptWindowStart datetime
DECLARE @FailedPasswordAnswerAttemptCount int
DECLARE @FailedPasswordAnswerAttemptWindowStart datetime
DECLARE @ErrorCode int
SET @ErrorCode = 0
DECLARE @TranStarted bit
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
SELECT @UserId = u.UserId,
@Password = m.Password,
@passAns = m.PasswordAnswer,
@PasswordFormat = m.PasswordFormat,
@IsLockedOut = m.IsLockedOut,
@LastLockoutDate = m.LastLockoutDate,
@FailedPasswordAttemptCount = m.FailedPasswordAttemptCount,
@FailedPasswordAttemptWindowStart = m.FailedPasswordAttemptWindowStart,
@FailedPasswordAnswerAttemptCount = m.FailedPasswordAnswerAttemptCount,
@FailedPasswordAnswerAttemptWindowStart = m.FailedPasswordAnswerAttemptWindowStart
FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m WITH ( UPDLOCK )
WHERE LOWER(@ApplicationName) = a.LoweredApplicationName AND
u.ApplicationId = a.ApplicationId AND
u.UserId = m.UserId AND
LOWER(@UserName) = u.LoweredUserName
IF ( @@rowcount = 0 )
BEGIN
SET @ErrorCode = 1
GOTO Cleanup
END
IF( @IsLockedOut = 1 )
BEGIN
SET @ErrorCode = 99
GOTO Cleanup
END
IF ( NOT( @PasswordAnswer IS NULL ) )
BEGIN
IF( ( @passAns IS NULL ) OR ( LOWER( @passAns ) <> LOWER( @PasswordAnswer ) ) )
BEGIN
IF( @CurrentTimeUtc > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAnswerAttemptWindowStart ) )
BEGIN
SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc
SET @FailedPasswordAnswerAttemptCount = 1
END
ELSE
BEGIN
SET @FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount + 1
SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc
END
BEGIN
IF( @FailedPasswordAnswerAttemptCount >= @MaxInvalidPasswordAttempts )
BEGIN
SET @IsLockedOut = 1
SET @LastLockoutDate = @CurrentTimeUtc
END
END
SET @ErrorCode = 3
END
ELSE
BEGIN
IF( @FailedPasswordAnswerAttemptCount > 0 )
BEGIN
SET @FailedPasswordAnswerAttemptCount = 0
SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
END
END
UPDATE dbo.aspnet_Membership
SET IsLockedOut = @IsLockedOut, LastLockoutDate = @LastLockoutDate,
FailedPasswordAttemptCount = @FailedPasswordAttemptCount,
FailedPasswordAttemptWindowStart = @FailedPasswordAttemptWindowStart,
FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount,
FailedPasswordAnswerAttemptWindowStart = @FailedPasswordAnswerAttemptWindowStart
WHERE @UserId = UserId
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
END
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END
IF( @ErrorCode = 0 )
SELECT @Password, @PasswordFormat
RETURN @ErrorCode
Cleanup:
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN @ErrorCode
END
GO
/*************************************************************/
/*************************************************************/
IF (EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_Membership_SetPassword')
AND (type = 'P')))
DROP PROCEDURE dbo.aspnet_Membership_SetPassword
GO
CREATE PROCEDURE dbo.aspnet_Membership_SetPassword
@ApplicationName nvarchar(256),
@UserName nvarchar(256),
@NewPassword nvarchar(128),
@PasswordSalt nvarchar(128),
@CurrentTimeUtc datetime,
@PasswordFormat int = 0
AS
BEGIN
DECLARE @UserId uniqueidentifier
SELECT @UserId = NULL
SELECT @UserId = u.UserId
FROM dbo.aspnet_Users u, dbo.aspnet_Applications a, dbo.aspnet_Membership m
WHERE LoweredUserName = LOWER(@UserName) AND
u.ApplicationId = a.ApplicationId AND
LOWER(@ApplicationName) = a.LoweredApplicationName AND
u.UserId = m.UserId
IF (@UserId IS NULL)
RETURN(1)
UPDATE dbo.aspnet_Membership
SET Password = @NewPassword, PasswordFormat = @PasswordFormat, PasswordSalt = @PasswordSalt,
LastPasswordChangedDate = @CurrentTimeUtc
WHERE @UserId = UserId
RETURN(0)
END
GO
/*************************************************************/
/*************************************************************/
IF (EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_Membership_ResetPassword')
AND (type = 'P')))
DROP PROCEDURE dbo.aspnet_Membership_ResetPassword
GO
CREATE PROCEDURE dbo.aspnet_Membership_ResetPassword
@ApplicationName nvarchar(256),
@UserName nvarchar(256),
@NewPassword nvarchar(128),
@MaxInvalidPasswordAttempts int,
@PasswordAttemptWindow int,
@PasswordSalt nvarchar(128),
@CurrentTimeUtc datetime,
@PasswordFormat int = 0,
@PasswordAnswer nvarchar(128) = NULL
AS
BEGIN
DECLARE @IsLockedOut bit
DECLARE @LastLockoutDate datetime
DECLARE @FailedPasswordAttemptCount int
DECLARE @FailedPasswordAttemptWindowStart datetime
DECLARE @FailedPasswordAnswerAttemptCount int
DECLARE @FailedPasswordAnswerAttemptWindowStart datetime
DECLARE @UserId uniqueidentifier
SET @UserId = NULL
DECLARE @ErrorCode int
SET @ErrorCode = 0
DECLARE @TranStarted bit
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
SELECT @UserId = u.UserId
FROM dbo.aspnet_Users u, dbo.aspnet_Applications a, dbo.aspnet_Membership m
WHERE LoweredUserName = LOWER(@UserName) AND
u.ApplicationId = a.ApplicationId AND
LOWER(@ApplicationName) = a.LoweredApplicationName AND
u.UserId = m.UserId
IF ( @UserId IS NULL )
BEGIN
SET @ErrorCode = 1
GOTO Cleanup
END
SELECT @IsLockedOut = IsLockedOut,
@LastLockoutDate = LastLockoutDate,
@FailedPasswordAttemptCount = FailedPasswordAttemptCount,
@FailedPasswordAttemptWindowStart = FailedPasswordAttemptWindowStart,
@FailedPasswordAnswerAttemptCount = FailedPasswordAnswerAttemptCount,
@FailedPasswordAnswerAttemptWindowStart = FailedPasswordAnswerAttemptWindowStart
FROM dbo.aspnet_Membership WITH ( UPDLOCK )
WHERE @UserId = UserId
IF( @IsLockedOut = 1 )
BEGIN
SET @ErrorCode = 99
GOTO Cleanup
END
UPDATE dbo.aspnet_Membership
SET Password = @NewPassword,
LastPasswordChangedDate = @CurrentTimeUtc,
PasswordFormat = @PasswordFormat,
PasswordSalt = @PasswordSalt
WHERE @UserId = UserId AND
( ( @PasswordAnswer IS NULL ) OR ( LOWER( PasswordAnswer ) = LOWER( @PasswordAnswer ) ) )
IF ( @@ROWCOUNT = 0 )
BEGIN
IF( @CurrentTimeUtc > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAnswerAttemptWindowStart ) )
BEGIN
SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc
SET @FailedPasswordAnswerAttemptCount = 1
END
ELSE
BEGIN
SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc
SET @FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount + 1
END
BEGIN
IF( @FailedPasswordAnswerAttemptCount >= @MaxInvalidPasswordAttempts )
BEGIN
SET @IsLockedOut = 1
SET @LastLockoutDate = @CurrentTimeUtc
END
END
SET @ErrorCode = 3
END
ELSE
BEGIN
IF( @FailedPasswordAnswerAttemptCount > 0 )
BEGIN
SET @FailedPasswordAnswerAttemptCount = 0
SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
END
END
IF( NOT ( @PasswordAnswer IS NULL ) )
BEGIN
UPDATE dbo.aspnet_Membership
SET IsLockedOut = @IsLockedOut, LastLockoutDate = @LastLockoutDate,
FailedPasswordAttemptCount = @FailedPasswordAttemptCount,
FailedPasswordAttemptWindowStart = @FailedPasswordAttemptWindowStart,
FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount,
FailedPasswordAnswerAttemptWindowStart = @FailedPasswordAnswerAttemptWindowStart
WHERE @UserId = UserId
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
END
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END
RETURN @ErrorCode
Cleanup:
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN @ErrorCode
END
GO
/*************************************************************/
/*************************************************************/
IF (EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_Membership_UnlockUser')
AND (type = 'P')))
DROP PROCEDURE dbo.aspnet_Membership_UnlockUser
GO
CREATE PROCEDURE dbo.aspnet_Membership_UnlockUser
@ApplicationName nvarchar(256),
@UserName nvarchar(256)
AS
BEGIN
DECLARE @UserId uniqueidentifier
SELECT @UserId = NULL
SELECT @UserId = u.UserId
FROM dbo.aspnet_Users u, dbo.aspnet_Applications a, dbo.aspnet_Membership m
WHERE LoweredUserName = LOWER(@UserName) AND
u.ApplicationId = a.ApplicationId AND
LOWER(@ApplicationName) = a.LoweredApplicationName AND
u.UserId = m.UserId
IF ( @UserId IS NULL )
RETURN 1
UPDATE dbo.aspnet_Membership
SET IsLockedOut = 0,
FailedPasswordAttemptCount = 0,
FailedPasswordAttemptWindowStart = CONVERT( datetime, '17540101', 112 ),
FailedPasswordAnswerAttemptCount = 0,
FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 ),
LastLockoutDate = CONVERT( datetime, '17540101', 112 )
WHERE @UserId = UserId
RETURN 0
END
GO
/*************************************************************/
/*************************************************************/
IF (EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_Membership_UpdateUser')
AND (type = 'P')))
DROP PROCEDURE dbo.aspnet_Membership_UpdateUser
GO
CREATE PROCEDURE dbo.aspnet_Membership_UpdateUser
@ApplicationName nvarchar(256),
@UserName nvarchar(256),
@Email nvarchar(256),
@Comment ntext,
@IsApproved bit,
@LastLoginDate datetime,
@LastActivityDate datetime,
@UniqueEmail int,
@CurrentTimeUtc datetime
AS
BEGIN
DECLARE @UserId uniqueidentifier
DECLARE @ApplicationId uniqueidentifier
SELECT @UserId = NULL
SELECT @UserId = u.UserId, @ApplicationId = a.ApplicationId
FROM dbo.aspnet_Users u, dbo.aspnet_Applications a, dbo.aspnet_Membership m
WHERE LoweredUserName = LOWER(@UserName) AND
u.ApplicationId = a.ApplicationId AND
LOWER(@ApplicationName) = a.LoweredApplicationName AND
u.UserId = m.UserId
IF (@UserId IS NULL)
RETURN(1)
IF (@UniqueEmail = 1)
BEGIN
IF (EXISTS (SELECT *
FROM dbo.aspnet_Membership WITH (UPDLOCK, HOLDLOCK)
WHERE ApplicationId = @ApplicationId AND @UserId <> UserId AND LoweredEmail = LOWER(@Email)))
BEGIN
RETURN(7)
END
END
DECLARE @TranStarted bit
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
UPDATE dbo.aspnet_Users WITH (ROWLOCK)
SET
LastActivityDate = @LastActivityDate
WHERE
@UserId = UserId
IF( @@ERROR <> 0 )
GOTO Cleanup
UPDATE dbo.aspnet_Membership WITH (ROWLOCK)
SET
Email = @Email,
LoweredEmail = LOWER(@Email),
Comment = @Comment,
IsApproved = @IsApproved,
LastLoginDate = @LastLoginDate
WHERE
@UserId = UserId
IF( @@ERROR <> 0 )
GOTO Cleanup
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END
RETURN 0
Cleanup:
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN -1
END
GO
/*************************************************************/
/*************************************************************/
IF (EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_Membership_ChangePasswordQuestionAndAnswer')
AND (type = 'P')))
DROP PROCEDURE dbo.aspnet_Membership_ChangePasswordQuestionAndAnswer
GO
CREATE PROCEDURE dbo.aspnet_Membership_ChangePasswordQuestionAndAnswer
@ApplicationName nvarchar(256),
@UserName nvarchar(256),
@NewPasswordQuestion nvarchar(256),
@NewPasswordAnswer nvarchar(128)
AS
BEGIN
DECLARE @UserId uniqueidentifier
SELECT @UserId = NULL
SELECT @UserId = u.UserId
FROM dbo.aspnet_Membership m, dbo.aspnet_Users u, dbo.aspnet_Applications a
WHERE LoweredUserName = LOWER(@UserName) AND
u.ApplicationId = a.ApplicationId AND
LOWER(@ApplicationName) = a.LoweredApplicationName AND
u.UserId = m.UserId
IF (@UserId IS NULL)
BEGIN
RETURN(1)
END
UPDATE dbo.aspnet_Membership
SET PasswordQuestion = @NewPasswordQuestion, PasswordAnswer = @NewPasswordAnswer
WHERE UserId=@UserId
RETURN(0)
END
GO
/*************************************************************/
/*************************************************************/
IF (EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_Membership_GetAllUsers')
AND (type = 'P')))
DROP PROCEDURE dbo.aspnet_Membership_GetAllUsers
GO
CREATE PROCEDURE dbo.aspnet_Membership_GetAllUsers
@ApplicationName nvarchar(256),
@PageIndex int,
@PageSize int
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
SELECT @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF (@ApplicationId IS NULL)
RETURN 0
-- Set the page bounds
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @TotalRecords int
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
-- Create a temp table TO store the select results
CREATE TABLE #PageIndexForUsers
(
IndexId int IDENTITY (0, 1) NOT NULL,
UserId uniqueidentifier
)
-- Insert into our temp table
INSERT INTO #PageIndexForUsers (UserId)
SELECT u.UserId
FROM dbo.aspnet_Membership m, dbo.aspnet_Users u
WHERE u.ApplicationId = @ApplicationId AND u.UserId = m.UserId
ORDER BY u.UserName
SELECT @TotalRecords = @@ROWCOUNT
SELECT u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
m.CreateDate,
m.LastLoginDate,
u.LastActivityDate,
m.LastPasswordChangedDate,
u.UserId, m.IsLockedOut,
m.LastLockoutDate
FROM dbo.aspnet_Membership m, dbo.aspnet_Users u, #PageIndexForUsers p
WHERE u.UserId = p.UserId AND u.UserId = m.UserId AND
p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
ORDER BY u.UserName
RETURN @TotalRecords
END
GO
/*************************************************************/
/*************************************************************/
IF (EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_Membership_GetNumberOfUsersOnline')
AND (type = 'P')))
DROP PROCEDURE dbo.aspnet_Membership_GetNumberOfUsersOnline
GO
CREATE PROCEDURE dbo.aspnet_Membership_GetNumberOfUsersOnline
@ApplicationName nvarchar(256),
@MinutesSinceLastInActive int,
@CurrentTimeUtc datetime
AS
BEGIN
DECLARE @DateActive datetime
SELECT @DateActive = DATEADD(minute, -(@MinutesSinceLastInActive), @CurrentTimeUtc)
DECLARE @NumOnline int
SELECT @NumOnline = COUNT(*)
FROM dbo.aspnet_Users u(NOLOCK),
dbo.aspnet_Applications a(NOLOCK),
dbo.aspnet_Membership m(NOLOCK)
WHERE u.ApplicationId = a.ApplicationId AND
LastActivityDate > @DateActive AND
a.LoweredApplicationName = LOWER(@ApplicationName) AND
u.UserId = m.UserId
RETURN(@NumOnline)
END
GO
/*************************************************************/
/*************************************************************/
IF (EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_Membership_FindUsersByName')
AND (type = 'P')))
DROP PROCEDURE dbo.aspnet_Membership_FindUsersByName
GO
CREATE PROCEDURE dbo.aspnet_Membership_FindUsersByName
@ApplicationName nvarchar(256),
@UserNameToMatch nvarchar(256),
@PageIndex int,
@PageSize int
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
SELECT @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF (@ApplicationId IS NULL)
RETURN 0
-- Set the page bounds
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @TotalRecords int
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
-- Create a temp table TO store the select results
CREATE TABLE #PageIndexForUsers
(
IndexId int IDENTITY (0, 1) NOT NULL,
UserId uniqueidentifier
)
-- Insert into our temp table
INSERT INTO #PageIndexForUsers (UserId)
SELECT u.UserId
FROM dbo.aspnet_Users u, dbo.aspnet_Membership m
WHERE u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND u.LoweredUserName LIKE LOWER(@UserNameToMatch)
ORDER BY u.UserName
SELECT u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
m.CreateDate,
m.LastLoginDate,
u.LastActivityDate,
m.LastPasswordChangedDate,
u.UserId, m.IsLockedOut,
m.LastLockoutDate
FROM dbo.aspnet_Membership m, dbo.aspnet_Users u, #PageIndexForUsers p
WHERE u.UserId = p.UserId AND u.UserId = m.UserId AND
p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
ORDER BY u.UserName
SELECT @TotalRecords = COUNT(*)
FROM #PageIndexForUsers
RETURN @TotalRecords
END
GO
/*************************************************************/
/*************************************************************/
IF (EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_Membership_FindUsersByEmail')
AND (type = 'P')))
DROP PROCEDURE dbo.aspnet_Membership_FindUsersByEmail
GO
CREATE PROCEDURE dbo.aspnet_Membership_FindUsersByEmail
@ApplicationName nvarchar(256),
@EmailToMatch nvarchar(256),
@PageIndex int,
@PageSize int
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
SELECT @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF (@ApplicationId IS NULL)
RETURN 0
-- Set the page bounds
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @TotalRecords int
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
-- Create a temp table TO store the select results
CREATE TABLE #PageIndexForUsers
(
IndexId int IDENTITY (0, 1) NOT NULL,
UserId uniqueidentifier
)
-- Insert into our temp table
IF( @EmailToMatch IS NULL )
INSERT INTO #PageIndexForUsers (UserId)
SELECT u.UserId
FROM dbo.aspnet_Users u, dbo.aspnet_Membership m
WHERE u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND m.Email IS NULL
ORDER BY m.LoweredEmail
ELSE
INSERT INTO #PageIndexForUsers (UserId)
SELECT u.UserId
FROM dbo.aspnet_Users u, dbo.aspnet_Membership m
WHERE u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND m.LoweredEmail LIKE LOWER(@EmailToMatch)
ORDER BY m.LoweredEmail
SELECT u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
m.CreateDate,
m.LastLoginDate,
u.LastActivityDate,
m.LastPasswordChangedDate,
u.UserId, m.IsLockedOut,
m.LastLockoutDate
FROM dbo.aspnet_Membership m, dbo.aspnet_Users u, #PageIndexForUsers p
WHERE u.UserId = p.UserId AND u.UserId = m.UserId AND
p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
ORDER BY m.LoweredEmail
SELECT @TotalRecords = COUNT(*)
FROM #PageIndexForUsers
RETURN @TotalRecords
END
GO
/*************************************************************/
/*************************************************************/
IF (NOT EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'vw_aspnet_MembershipUsers')
AND (type = 'V')))
BEGIN
PRINT 'Creating the vw_aspnet_MembershipUsers view...'
EXEC('
CREATE VIEW [dbo].[vw_aspnet_MembershipUsers]
AS SELECT [dbo].[aspnet_Membership].[UserId],
[dbo].[aspnet_Membership].[PasswordFormat],
[dbo].[aspnet_Membership].[MobilePIN],
[dbo].[aspnet_Membership].[Email],
[dbo].[aspnet_Membership].[LoweredEmail],
[dbo].[aspnet_Membership].[PasswordQuestion],
[dbo].[aspnet_Membership].[PasswordAnswer],
[dbo].[aspnet_Membership].[IsApproved],
[dbo].[aspnet_Membership].[IsLockedOut],
[dbo].[aspnet_Membership].[CreateDate],
[dbo].[aspnet_Membership].[LastLoginDate],
[dbo].[aspnet_Membership].[LastPasswordChangedDate],
[dbo].[aspnet_Membership].[LastLockoutDate],
[dbo].[aspnet_Membership].[FailedPasswordAttemptCount],
[dbo].[aspnet_Membership].[FailedPasswordAttemptWindowStart],
[dbo].[aspnet_Membership].[FailedPasswordAnswerAttemptCount],
[dbo].[aspnet_Membership].[FailedPasswordAnswerAttemptWindowStart],
[dbo].[aspnet_Membership].[Comment],
[dbo].[aspnet_Users].[ApplicationId],
[dbo].[aspnet_Users].[UserName],
[dbo].[aspnet_Users].[MobileAlias],
[dbo].[aspnet_Users].[IsAnonymous],
[dbo].[aspnet_Users].[LastActivityDate]
FROM [dbo].[aspnet_Membership] INNER JOIN [dbo].[aspnet_Users]
ON [dbo].[aspnet_Membership].[UserId] = [dbo].[aspnet_Users].[UserId]
')
END
GO
/*************************************************************/
/*************************************************************/
--
--Create Membership schema version
--
DECLARE @command nvarchar(4000)
SET @command = 'GRANT EXECUTE ON [dbo].aspnet_RegisterSchemaVersion TO ' + QUOTENAME(user)
EXECUTE (@command)
GO
EXEC [dbo].aspnet_RegisterSchemaVersion N'Membership', N'1', 1, 1
GO
/*************************************************************/
/*************************************************************/
--
--Create Membership roles
--
IF ( NOT EXISTS ( SELECT name
FROM sysusers
WHERE issqlrole = 1
AND name = N'aspnet_Membership_FullAccess' ) )
EXEC sp_addrole N'aspnet_Membership_FullAccess'
IF ( NOT EXISTS ( SELECT name
FROM sysusers
WHERE issqlrole = 1
AND name = N'aspnet_Membership_BasicAccess' ) )
EXEC sp_addrole N'aspnet_Membership_BasicAccess'
IF ( NOT EXISTS ( SELECT name
FROM sysusers
WHERE issqlrole = 1
AND name = N'aspnet_Membership_ReportingAccess' ) )
EXEC sp_addrole N'aspnet_Membership_ReportingAccess'
GO
EXEC sp_addrolemember N'aspnet_Membership_BasicAccess', N'aspnet_Membership_FullAccess'
EXEC sp_addrolemember N'aspnet_Membership_ReportingAccess', N'aspnet_Membership_FullAccess'
GO
--
--Stored Procedure rights for BasicAcess
--
GRANT EXECUTE ON dbo.aspnet_Membership_GetUserByUserId TO aspnet_Membership_BasicAccess
GRANT EXECUTE ON dbo.aspnet_Membership_GetUserByName TO aspnet_Membership_BasicAccess
GRANT EXECUTE ON dbo.aspnet_Membership_GetUserByEmail TO aspnet_Membership_BasicAccess
GRANT EXECUTE ON dbo.aspnet_Membership_GetPassword TO aspnet_Membership_BasicAccess
GRANT EXECUTE ON dbo.aspnet_Membership_GetPasswordWithFormat TO aspnet_Membership_BasicAccess
GRANT EXECUTE ON dbo.aspnet_Membership_UpdateUserInfo TO aspnet_Membership_BasicAccess
GRANT EXECUTE ON dbo.aspnet_Membership_GetNumberOfUsersOnline TO aspnet_Membership_BasicAccess
GRANT EXECUTE ON dbo.aspnet_CheckSchemaVersion TO aspnet_Membership_BasicAccess
GRANT EXECUTE ON dbo.aspnet_RegisterSchemaVersion TO aspnet_Membership_BasicAccess
GRANT EXECUTE ON dbo.aspnet_UnRegisterSchemaVersion TO aspnet_Membership_BasicAccess
--
--Stored Procedure rights for ReportingAccess
--
GRANT EXECUTE ON dbo.aspnet_Membership_GetUserByUserId TO aspnet_Membership_ReportingAccess
GRANT EXECUTE ON dbo.aspnet_Membership_GetUserByName TO aspnet_Membership_ReportingAccess
GRANT EXECUTE ON dbo.aspnet_Membership_GetUserByEmail TO aspnet_Membership_ReportingAccess
GRANT EXECUTE ON dbo.aspnet_Membership_GetAllUsers TO aspnet_Membership_ReportingAccess
GRANT EXECUTE ON dbo.aspnet_Membership_GetNumberOfUsersOnline TO aspnet_Membership_ReportingAccess
GRANT EXECUTE ON dbo.aspnet_Membership_FindUsersByName TO aspnet_Membership_ReportingAccess
GRANT EXECUTE ON dbo.aspnet_Membership_FindUsersByEmail TO aspnet_Membership_ReportingAccess
GRANT EXECUTE ON dbo.aspnet_CheckSchemaVersion TO aspnet_Membership_ReportingAccess
GRANT EXECUTE ON dbo.aspnet_RegisterSchemaVersion TO aspnet_Membership_ReportingAccess
GRANT EXECUTE ON dbo.aspnet_UnRegisterSchemaVersion TO aspnet_Membership_ReportingAccess
--
--Additional stored procedure rights for FullAccess
--
GRANT EXECUTE ON dbo.aspnet_Users_DeleteUser TO aspnet_Membership_FullAccess
GRANT EXECUTE ON dbo.aspnet_Membership_CreateUser TO aspnet_Membership_FullAccess
GRANT EXECUTE ON dbo.aspnet_Membership_SetPassword TO aspnet_Membership_FullAccess
GRANT EXECUTE ON dbo.aspnet_Membership_ResetPassword TO aspnet_Membership_FullAccess
GRANT EXECUTE ON dbo.aspnet_Membership_UpdateUser TO aspnet_Membership_FullAccess
GRANT EXECUTE ON dbo.aspnet_Membership_ChangePasswordQuestionAndAnswer TO aspnet_Membership_FullAccess
GRANT EXECUTE ON dbo.aspnet_Membership_UnlockUser TO aspnet_Membership_FullAccess
--
--View rights
--
GRANT SELECT ON dbo.vw_aspnet_Applications TO aspnet_Membership_ReportingAccess
GRANT SELECT ON dbo.vw_aspnet_Users TO aspnet_Membership_ReportingAccess
GRANT SELECT ON dbo.vw_aspnet_MembershipUsers TO aspnet_Membership_ReportingAccess
/*************************************************************/
/*************************************************************/
/*************************************************************/
/*************************************************************/
DECLARE @command nvarchar(4000)
SET @command = 'REVOKE EXECUTE ON [dbo].aspnet_RegisterSchemaVersion FROM ' + QUOTENAME(user)
EXECUTE (@command)
GO
PRINT '--------------------------------------------'
PRINT 'Completed execution of InstallMembership.SQL'
PRINT '--------------------------------------------'
/**********************************************************************/
/* InstallRoles.SQL */
/* */
/* Installs the tables, triggers and stored procedures necessary for */
/* supporting the aspnet feature of ASP.Net */
/* */
/* InstallCommon.sql must be run before running this file. */
/*
** Copyright Microsoft, Inc. 2002
** All Rights Reserved.
*/
/**********************************************************************/
PRINT '--------------------------------------'
PRINT 'Starting execution of InstallRoles.SQL'
PRINT '--------------------------------------'
GO
SET QUOTED_IDENTIFIER OFF -- We don't use quoted identifiers
SET ANSI_NULLS ON -- We don't want (NULL = NULL) == TRUE
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULL_DFLT_ON ON
GO
/*************************************************************/
/*************************************************************/
/*************************************************************/
/*************************************************************/
/*************************************************************/
DECLARE @dbname nvarchar(128)
SET @dbname = N'aspnetdb'
IF (NOT EXISTS (SELECT name
FROM master.dbo.sysdatabases
WHERE ('[' + name + ']' = @dbname OR name = @dbname)))
BEGIN
RAISERROR('The database ''%s'' cannot be found. Please run InstallCommon.sql first.', 18, 1, @dbname)
END
GO
USE [aspnetdb]
GO
IF (NOT EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_Applications')
AND (type = 'U')))
BEGIN
RAISERROR('The table ''aspnet_Applications'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
END
IF (NOT EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_Users')
AND (type = 'U')))
BEGIN
RAISERROR('The table ''aspnet_Users'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
END
IF (NOT EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_Applications_CreateApplication')
AND (type = 'P')))
BEGIN
RAISERROR('The stored procedure ''aspnet_Applications_CreateApplication'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
END
IF (NOT EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_Users_CreateUser')
AND (type = 'P')))
BEGIN
RAISERROR('The stored procedure ''aspnet_Users_CreateUser'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
END
IF (NOT EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_Users_DeleteUser')
AND (type = 'P')))
BEGIN
RAISERROR('The stored procedure ''aspnet_Users_DeleteUser'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
END
/*************************************************************/
/*************************************************************/
IF (NOT EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_Roles')
AND (type = 'U')))
BEGIN
PRINT 'Creating the aspnet_Roles table...'
CREATE TABLE dbo.aspnet_Roles (
ApplicationId uniqueidentifier NOT NULL FOREIGN KEY REFERENCES dbo.aspnet_Applications(ApplicationId),
RoleId uniqueidentifier PRIMARY KEY NONCLUSTERED DEFAULT NEWID(),
RoleName nvarchar(256) NOT NULL,
LoweredRoleName nvarchar(256) NOT NULL,
Description nvarchar(256) )
CREATE UNIQUE CLUSTERED INDEX aspnet_Roles_index1 ON dbo.aspnet_Roles(ApplicationId, LoweredRoleName)
END
GO
/*************************************************************/
/*************************************************************/
IF (NOT EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_UsersInRoles')
AND (type = 'U')))
BEGIN
PRINT 'Creating the aspnet_UsersInRoles table...'
CREATE TABLE dbo.aspnet_UsersInRoles (
UserId uniqueidentifier NOT NULL PRIMARY KEY(UserId, RoleId) FOREIGN KEY REFERENCES dbo.aspnet_Users (UserId),
RoleId uniqueidentifier NOT NULL FOREIGN KEY REFERENCES dbo.aspnet_Roles (RoleId))
CREATE INDEX aspnet_UsersInRoles_index ON dbo.aspnet_UsersInRoles(RoleId)
END
/*************************************************************/
/*************************************************************/
/*************************************************************/
/*************************************************************/
IF (EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_UsersInRoles_IsUserInRole')
AND (type = 'P')))
DROP PROCEDURE dbo.aspnet_UsersInRoles_IsUserInRole
GO
CREATE PROCEDURE dbo.aspnet_UsersInRoles_IsUserInRole
@ApplicationName nvarchar(256),
@UserName nvarchar(256),
@RoleName nvarchar(256)
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
SELECT @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF (@ApplicationId IS NULL)
RETURN(2)
DECLARE @UserId uniqueidentifier
SELECT @UserId = NULL
DECLARE @RoleId uniqueidentifier
SELECT @RoleId = NULL
SELECT @UserId = UserId
FROM dbo.aspnet_Users
WHERE LoweredUserName = LOWER(@UserName) AND ApplicationId = @ApplicationId
IF (@UserId IS NULL)
RETURN(2)
SELECT @RoleId = RoleId
FROM dbo.aspnet_Roles
WHERE LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @ApplicationId
IF (@RoleId IS NULL)
RETURN(3)
IF (EXISTS( SELECT * FROM dbo.aspnet_UsersInRoles WHERE UserId = @UserId AND RoleId = @RoleId))
RETURN(1)
ELSE
RETURN(0)
END
GO
/*************************************************************/
/*************************************************************/
IF (EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_UsersInRoles_GetRolesForUser')
AND (type = 'P')))
DROP PROCEDURE dbo.aspnet_UsersInRoles_GetRolesForUser
GO
CREATE PROCEDURE dbo.aspnet_UsersInRoles_GetRolesForUser
@ApplicationName nvarchar(256),
@UserName nvarchar(256)
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
SELECT @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF (@ApplicationId IS NULL)
RETURN(1)
DECLARE @UserId uniqueidentifier
SELECT @UserId = NULL
SELECT @UserId = UserId
FROM dbo.aspnet_Users
WHERE LoweredUserName = LOWER(@UserName) AND ApplicationId = @ApplicationId
IF (@UserId IS NULL)
RETURN(1)
SELECT r.RoleName
FROM dbo.aspnet_Roles r, dbo.aspnet_UsersInRoles ur
WHERE r.RoleId = ur.RoleId AND r.ApplicationId = @ApplicationId AND ur.UserId = @UserId
ORDER BY r.RoleName
RETURN (0)
END
GO
/*************************************************************/
/*************************************************************/
IF (EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_Roles_CreateRole')
AND (type = 'P')))
DROP PROCEDURE dbo.aspnet_Roles_CreateRole
GO
CREATE PROCEDURE dbo.aspnet_Roles_CreateRole
@ApplicationName nvarchar(256),
@RoleName nvarchar(256)
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
DECLARE @ErrorCode int
SET @ErrorCode = 0
DECLARE @TranStarted bit
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
IF (EXISTS(SELECT RoleId FROM dbo.aspnet_Roles WHERE LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @ApplicationId))
BEGIN
SET @ErrorCode = 1
GOTO Cleanup
END
INSERT INTO dbo.aspnet_Roles
(ApplicationId, RoleName, LoweredRoleName)
VALUES (@ApplicationId, @RoleName, LOWER(@RoleName))
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END
RETURN(0)
Cleanup:
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN @ErrorCode
END
GO
/*************************************************************/
/*************************************************************/
IF (EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_Roles_DeleteRole')
AND (type = 'P')))
DROP PROCEDURE dbo.aspnet_Roles_DeleteRole
GO
CREATE PROCEDURE dbo.aspnet_Roles_DeleteRole
@ApplicationName nvarchar(256),
@RoleName nvarchar(256),
@DeleteOnlyIfRoleIsEmpty bit
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
SELECT @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF (@ApplicationId IS NULL)
RETURN(1)
DECLARE @ErrorCode int
SET @ErrorCode = 0
DECLARE @TranStarted bit
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
DECLARE @RoleId uniqueidentifier
SELECT @RoleId = NULL
SELECT @RoleId = RoleId FROM dbo.aspnet_Roles WHERE LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @ApplicationId
IF (@RoleId IS NULL)
BEGIN
SELECT @ErrorCode = 1
GOTO Cleanup
END
IF (@DeleteOnlyIfRoleIsEmpty <> 0)
BEGIN
IF (EXISTS (SELECT RoleId FROM dbo.aspnet_UsersInRoles WHERE @RoleId = RoleId))
BEGIN
SELECT @ErrorCode = 2
GOTO Cleanup
END
END
DELETE FROM dbo.aspnet_UsersInRoles WHERE @RoleId = RoleId
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
DELETE FROM dbo.aspnet_Roles WHERE @RoleId = RoleId AND ApplicationId = @ApplicationId
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END
RETURN(0)
Cleanup:
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN @ErrorCode
END
GO
/*************************************************************/
/*************************************************************/
IF (EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_Roles_RoleExists')
AND (type = 'P')))
DROP PROCEDURE dbo.aspnet_Roles_RoleExists
GO
CREATE PROCEDURE dbo.aspnet_Roles_RoleExists
@ApplicationName nvarchar(256),
@RoleName nvarchar(256)
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
SELECT @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF (@ApplicationId IS NULL)
RETURN(0)
IF (EXISTS (SELECT RoleName FROM dbo.aspnet_Roles WHERE LOWER(@RoleName) = LoweredRoleName AND ApplicationId = @ApplicationId ))
RETURN(1)
ELSE
RETURN(0)
END
GO
/*************************************************************/
/*************************************************************/
IF (EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_UsersInRoles_AddUsersToRoles')
AND (type = 'P')))
DROP PROCEDURE dbo.aspnet_UsersInRoles_AddUsersToRoles
GO
IF (EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_UsersInRoles_RemoveUsersFromRoles')
AND (type = 'P')))
DROP PROCEDURE dbo.aspnet_UsersInRoles_RemoveUsersFromRoles
GO
DECLARE @ver int
DECLARE @version nchar(100)
DECLARE @dot int
DECLARE @hyphen int
DECLARE @SqlToExec nchar(4000)
SELECT @ver = 7
SELECT @version = @@Version
SELECT @hyphen = CHARINDEX(N' - ', @version)
IF (NOT(@hyphen IS NULL) AND @hyphen > 0)
BEGIN
SELECT @hyphen = @hyphen + 3
SELECT @dot = CHARINDEX(N'.', @version, @hyphen)
IF (NOT(@dot IS NULL) AND @dot > @hyphen)
BEGIN
SELECT @version = SUBSTRING(@version, @hyphen, @dot - @hyphen)
SELECT @ver = CONVERT(int, @version)
END
END
IF (@ver > 7)
SELECT @SqlToExec = N'
CREATE PROCEDURE dbo.aspnet_UsersInRoles_AddUsersToRoles
@ApplicationName nvarchar(256),
@UserNames nvarchar(4000),
@RoleNames nvarchar(4000),
@CurrentTimeUtc datetime
AS
BEGIN
DECLARE @AppId uniqueidentifier
SELECT @AppId = NULL
SELECT @AppId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF (@AppId IS NULL)
RETURN(2)
DECLARE @TranStarted bit
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
DECLARE @tbNames table(Name nvarchar(256) NOT NULL PRIMARY KEY)
DECLARE @tbRoles table(RoleId uniqueidentifier NOT NULL PRIMARY KEY)
DECLARE @tbUsers table(UserId uniqueidentifier NOT NULL PRIMARY KEY)
DECLARE @Num int
DECLARE @Pos int
DECLARE @NextPos int
DECLARE @Name nvarchar(256)
SET @Num = 0
SET @Pos = 1
WHILE(@Pos <= LEN(@RoleNames))
BEGIN
SELECT @NextPos = CHARINDEX(N'','', @RoleNames, @Pos)
IF (@NextPos = 0 OR @NextPos IS NULL)
SELECT @NextPos = LEN(@RoleNames) + 1
SELECT @Name = RTRIM(LTRIM(SUBSTRING(@RoleNames, @Pos, @NextPos - @Pos)))
SELECT @Pos = @NextPos+1
INSERT INTO @tbNames VALUES (@Name)
SET @Num = @Num + 1
END
INSERT INTO @tbRoles
SELECT RoleId
FROM dbo.aspnet_Roles ar, @tbNames t
WHERE LOWER(t.Name) = ar.LoweredRoleName AND ar.ApplicationId = @AppId
IF (@@ROWCOUNT <> @Num)
BEGIN
SELECT TOP 1 Name
FROM @tbNames
WHERE LOWER(Name) NOT IN (SELECT ar.LoweredRoleName FROM dbo.aspnet_Roles ar, @tbRoles r WHERE r.RoleId = ar.RoleId)
IF( @TranStarted = 1 )
ROLLBACK TRANSACTION
RETURN(2)
END
DELETE FROM @tbNames WHERE 1=1
SET @Num = 0
SET @Pos = 1
WHILE(@Pos <= LEN(@UserNames))
BEGIN
SELECT @NextPos = CHARINDEX(N'','', @UserNames, @Pos)
IF (@NextPos = 0 OR @NextPos IS NULL)
SELECT @NextPos = LEN(@UserNames) + 1
SELECT @Name = RTRIM(LTRIM(SUBSTRING(@UserNames, @Pos, @NextPos - @Pos)))
SELECT @Pos = @NextPos+1
INSERT INTO @tbNames VALUES (@Name)
SET @Num = @Num + 1
END
INSERT INTO @tbUsers
SELECT UserId
FROM dbo.aspnet_Users ar, @tbNames t
WHERE LOWER(t.Name) = ar.LoweredUserName AND ar.ApplicationId = @AppId
IF (@@ROWCOUNT <> @Num)
BEGIN
DELETE FROM @tbNames
WHERE LOWER(Name) IN (SELECT LoweredUserName FROM dbo.aspnet_Users au, @tbUsers u WHERE au.UserId = u.UserId)
INSERT dbo.aspnet_Users (ApplicationId, UserId, UserName, LoweredUserName, IsAnonymous, LastActivityDate)
SELECT @AppId, NEWID(), Name, LOWER(Name), 0, @CurrentTimeUtc
FROM @tbNames
INSERT INTO @tbUsers
SELECT UserId
FROM dbo.aspnet_Users au, @tbNames t
WHERE LOWER(t.Name) = au.LoweredUserName AND au.ApplicationId = @AppId
END
IF (EXISTS (SELECT * FROM dbo.aspnet_UsersInRoles ur, @tbUsers tu, @tbRoles tr WHERE tu.UserId = ur.UserId AND tr.RoleId = ur.RoleId))
BEGIN
SELECT TOP 1 UserName, RoleName
FROM dbo.aspnet_UsersInRoles ur, @tbUsers tu, @tbRoles tr, aspnet_Users u, aspnet_Roles r
WHERE u.UserId = tu.UserId AND r.RoleId = tr.RoleId AND tu.UserId = ur.UserId AND tr.RoleId = ur.RoleId
IF( @TranStarted = 1 )
ROLLBACK TRANSACTION
RETURN(3)
END
INSERT INTO dbo.aspnet_UsersInRoles (UserId, RoleId)
SELECT UserId, RoleId
FROM @tbUsers, @tbRoles
IF( @TranStarted = 1 )
COMMIT TRANSACTION
RETURN(0)
END'
ELSE
SELECT @SqlToExec = N'
CREATE PROCEDURE dbo.aspnet_UsersInRoles_AddUsersToRoles
@ApplicationName nvarchar(256),
@UserNames nvarchar(4000),
@RoleNames nvarchar(4000),
@CurrentTimeUtc datetime
AS
BEGIN
DECLARE @AppId uniqueidentifier
SELECT @AppId = NULL
SELECT @AppId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF (@AppId IS NULL)
RETURN(2)
DECLARE @TranStarted bit
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
DECLARE @RoleId uniqueidentifier
DECLARE @UserId uniqueidentifier
DECLARE @UserName nvarchar(256)
DECLARE @RoleName nvarchar(256)
DECLARE @CurrentPosU int
DECLARE @NextPosU int
DECLARE @CurrentPosR int
DECLARE @NextPosR int
SELECT @CurrentPosU = 1
WHILE(@CurrentPosU <= LEN(@UserNames))
BEGIN
SELECT @NextPosU = CHARINDEX(N'','', @UserNames, @CurrentPosU)
IF (@NextPosU = 0 OR @NextPosU IS NULL)
SELECT @NextPosU = LEN(@UserNames) + 1
SELECT @UserName = SUBSTRING(@UserNames, @CurrentPosU, @NextPosU - @CurrentPosU)
SELECT @CurrentPosU = @NextPosU+1
SELECT @CurrentPosR = 1
WHILE(@CurrentPosR <= LEN(@RoleNames))
BEGIN
SELECT @NextPosR = CHARINDEX(N'','', @RoleNames, @CurrentPosR)
IF (@NextPosR = 0 OR @NextPosR IS NULL)
SELECT @NextPosR = LEN(@RoleNames) + 1
SELECT @RoleName = SUBSTRING(@RoleNames, @CurrentPosR, @NextPosR - @CurrentPosR)
SELECT @CurrentPosR = @NextPosR+1
SELECT @RoleId = NULL
SELECT @RoleId = RoleId FROM dbo.aspnet_Roles WHERE LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @AppId
IF (@RoleId IS NULL)
BEGIN
SELECT @RoleName
IF( @TranStarted = 1 )
ROLLBACK TRANSACTION
RETURN(2)
END
SELECT @UserId = NULL
SELECT @UserId = UserId FROM dbo.aspnet_Users WHERE LoweredUserName = LOWER(@UserName) AND ApplicationId = @AppId
IF (@UserId IS NULL)
BEGIN
EXEC dbo.aspnet_Users_CreateUser @AppId, @UserName, 0, @CurrentTimeUtc, @UserId OUTPUT
END
IF (EXISTS(SELECT * FROM dbo.aspnet_UsersInRoles WHERE @UserId = UserId AND @RoleId = RoleId))
BEGIN
SELECT @UserName, @RoleName
IF( @TranStarted = 1 )
ROLLBACK TRANSACTION
RETURN(3)
END
INSERT INTO dbo.aspnet_UsersInRoles (UserId, RoleId) VALUES(@UserId, @RoleId)
END
END
IF( @TranStarted = 1 )
COMMIT TRANSACTION
RETURN(0)
END'
EXEC sp_executesql @SqlToExec
IF (@ver > 7)
SELECT @SqlToExec = N'
CREATE PROCEDURE dbo.aspnet_UsersInRoles_RemoveUsersFromRoles
@ApplicationName nvarchar(256),
@UserNames nvarchar(4000),
@RoleNames nvarchar(4000)
AS
BEGIN
DECLARE @AppId uniqueidentifier
SELECT @AppId = NULL
SELECT @AppId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF (@AppId IS NULL)
RETURN(2)
DECLARE @TranStarted bit
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
DECLARE @tbNames table(Name nvarchar(256) NOT NULL PRIMARY KEY)
DECLARE @tbRoles table(RoleId uniqueidentifier NOT NULL PRIMARY KEY)
DECLARE @tbUsers table(UserId uniqueidentifier NOT NULL PRIMARY KEY)
DECLARE @Num int
DECLARE @Pos int
DECLARE @NextPos int
DECLARE @Name nvarchar(256)
DECLARE @CountAll int
DECLARE @CountU int
DECLARE @CountR int
SET @Num = 0
SET @Pos = 1
WHILE(@Pos <= LEN(@RoleNames))
BEGIN
SELECT @NextPos = CHARINDEX(N'','', @RoleNames, @Pos)
IF (@NextPos = 0 OR @NextPos IS NULL)
SELECT @NextPos = LEN(@RoleNames) + 1
SELECT @Name = RTRIM(LTRIM(SUBSTRING(@RoleNames, @Pos, @NextPos - @Pos)))
SELECT @Pos = @NextPos+1
INSERT INTO @tbNames VALUES (@Name)
SET @Num = @Num + 1
END
INSERT INTO @tbRoles
SELECT RoleId
FROM dbo.aspnet_Roles ar, @tbNames t
WHERE LOWER(t.Name) = ar.LoweredRoleName AND ar.ApplicationId = @AppId
SELECT @CountR = @@ROWCOUNT
IF (@CountR <> @Num)
BEGIN
SELECT TOP 1 N'''', Name
FROM @tbNames
WHERE LOWER(Name) NOT IN (SELECT ar.LoweredRoleName FROM dbo.aspnet_Roles ar, @tbRoles r WHERE r.RoleId = ar.RoleId)
IF( @TranStarted = 1 )
ROLLBACK TRANSACTION
RETURN(2)
END
DELETE FROM @tbNames WHERE 1=1
SET @Num = 0
SET @Pos = 1
WHILE(@Pos <= LEN(@UserNames))
BEGIN
SELECT @NextPos = CHARINDEX(N'','', @UserNames, @Pos)
IF (@NextPos = 0 OR @NextPos IS NULL)
SELECT @NextPos = LEN(@UserNames) + 1
SELECT @Name = RTRIM(LTRIM(SUBSTRING(@UserNames, @Pos, @NextPos - @Pos)))
SELECT @Pos = @NextPos+1
INSERT INTO @tbNames VALUES (@Name)
SET @Num = @Num + 1
END
INSERT INTO @tbUsers
SELECT UserId
FROM dbo.aspnet_Users ar, @tbNames t
WHERE LOWER(t.Name) = ar.LoweredUserName AND ar.ApplicationId = @AppId
SELECT @CountU = @@ROWCOUNT
IF (@CountU <> @Num)
BEGIN
SELECT TOP 1 Name, N''''
FROM @tbNames
WHERE LOWER(Name) NOT IN (SELECT au.LoweredUserName FROM dbo.aspnet_Users au, @tbUsers u WHERE u.UserId = au.UserId)
IF( @TranStarted = 1 )
ROLLBACK TRANSACTION
RETURN(1)
END
SELECT @CountAll = COUNT(*)
FROM dbo.aspnet_UsersInRoles ur, @tbUsers u, @tbRoles r
WHERE ur.UserId = u.UserId AND ur.RoleId = r.RoleId
IF (@CountAll <> @CountU * @CountR)
BEGIN
SELECT TOP 1 UserName, RoleName
FROM @tbUsers tu, @tbRoles tr, dbo.aspnet_Users u, dbo.aspnet_Roles r
WHERE u.UserId = tu.UserId AND r.RoleId = tr.RoleId AND
tu.UserId NOT IN (SELECT ur.UserId FROM dbo.aspnet_UsersInRoles ur WHERE ur.RoleId = tr.RoleId) AND
tr.RoleId NOT IN (SELECT ur.RoleId FROM dbo.aspnet_UsersInRoles ur WHERE ur.UserId = tu.UserId)
IF( @TranStarted = 1 )
ROLLBACK TRANSACTION
RETURN(3)
END
DELETE FROM dbo.aspnet_UsersInRoles
WHERE UserId IN (SELECT UserId FROM @tbUsers)
AND RoleId IN (SELECT RoleId FROM @tbRoles)
IF( @TranStarted = 1 )
COMMIT TRANSACTION
RETURN(0)
END
'
ELSE
SELECT @SqlToExec = N'
CREATE PROCEDURE dbo.aspnet_UsersInRoles_RemoveUsersFromRoles
@ApplicationName nvarchar(256),
@UserNames nvarchar(4000),
@RoleNames nvarchar(4000)
AS
BEGIN
DECLARE @AppId uniqueidentifier
SELECT @AppId = NULL
SELECT @AppId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF (@AppId IS NULL)
RETURN(2)
DECLARE @TranStarted bit
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
DECLARE @RoleId uniqueidentifier
DECLARE @UserId uniqueidentifier
DECLARE @UserName nvarchar(256)
DECLARE @RoleName nvarchar(256)
DECLARE @CurrentPosU int
DECLARE @NextPosU int
DECLARE @CurrentPosR int
DECLARE @NextPosR int
SELECT @CurrentPosU = 1
WHILE(@CurrentPosU <= LEN(@UserNames))
BEGIN
SELECT @NextPosU = CHARINDEX(N'','', @UserNames, @CurrentPosU)
IF (@NextPosU = 0 OR @NextPosU IS NULL)
SELECT @NextPosU = LEN(@UserNames)+1
SELECT @UserName = SUBSTRING(@UserNames, @CurrentPosU, @NextPosU - @CurrentPosU)
SELECT @CurrentPosU = @NextPosU+1
SELECT @CurrentPosR = 1
WHILE(@CurrentPosR <= LEN(@RoleNames))
BEGIN
SELECT @NextPosR = CHARINDEX(N'','', @RoleNames, @CurrentPosR)
IF (@NextPosR = 0 OR @NextPosR IS NULL)
SELECT @NextPosR = LEN(@RoleNames)+1
SELECT @RoleName = SUBSTRING(@RoleNames, @CurrentPosR, @NextPosR - @CurrentPosR)
SELECT @CurrentPosR = @NextPosR+1
SELECT @RoleId = NULL
SELECT @RoleId = RoleId FROM dbo.aspnet_Roles WHERE LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @AppId
IF (@RoleId IS NULL)
BEGIN
SELECT N'''', @RoleName
IF( @TranStarted = 1 )
ROLLBACK TRANSACTION
RETURN(2)
END
SELECT @UserId = NULL
SELECT @UserId = UserId FROM dbo.aspnet_Users WHERE LoweredUserName = LOWER(@UserName) AND ApplicationId = @AppId
IF (@UserId IS NULL)
BEGIN
SELECT @UserName, N''''
IF( @TranStarted = 1 )
ROLLBACK TRANSACTION
RETURN(1)
END
IF (NOT(EXISTS(SELECT * FROM dbo.aspnet_UsersInRoles WHERE @UserId = UserId AND @RoleId = RoleId)))
BEGIN
SELECT @UserName, @RoleName
IF( @TranStarted = 1 )
ROLLBACK TRANSACTION
RETURN(3)
END
DELETE FROM dbo.aspnet_UsersInRoles WHERE (UserId = @UserId AND RoleId = @RoleId)
END
END
IF( @TranStarted = 1 )
COMMIT TRANSACTION
RETURN(0)
END
'
EXEC sp_executesql @SqlToExec
GO
/*************************************************************/
/*************************************************************/
IF (EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_UsersInRoles_GetUsersInRoles')
AND (type = 'P')))
DROP PROCEDURE dbo.aspnet_UsersInRoles_GetUsersInRoles
GO
CREATE PROCEDURE dbo.aspnet_UsersInRoles_GetUsersInRoles
@ApplicationName nvarchar(256),
@RoleName nvarchar(256)
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
SELECT @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF (@ApplicationId IS NULL)
RETURN(1)
DECLARE @RoleId uniqueidentifier
SELECT @RoleId = NULL
SELECT @RoleId = RoleId
FROM dbo.aspnet_Roles
WHERE LOWER(@RoleName) = LoweredRoleName AND ApplicationId = @ApplicationId
IF (@RoleId IS NULL)
RETURN(1)
SELECT u.UserName
FROM dbo.aspnet_Users u, dbo.aspnet_UsersInRoles ur
WHERE u.UserId = ur.UserId AND @RoleId = ur.RoleId AND u.ApplicationId = @ApplicationId
ORDER BY u.UserName
RETURN(0)
END
GO
/*************************************************************/
/*************************************************************/
IF (EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_UsersInRoles_FindUsersInRole')
AND (type = 'P')))
DROP PROCEDURE dbo.aspnet_UsersInRoles_FindUsersInRole
GO
CREATE PROCEDURE dbo.aspnet_UsersInRoles_FindUsersInRole
@ApplicationName nvarchar(256),
@RoleName nvarchar(256),
@UserNameToMatch nvarchar(256)
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
SELECT @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF (@ApplicationId IS NULL)
RETURN(1)
DECLARE @RoleId uniqueidentifier
SELECT @RoleId = NULL
SELECT @RoleId = RoleId
FROM dbo.aspnet_Roles
WHERE LOWER(@RoleName) = LoweredRoleName AND ApplicationId = @ApplicationId
IF (@RoleId IS NULL)
RETURN(1)
SELECT u.UserName
FROM dbo.aspnet_Users u, dbo.aspnet_UsersInRoles ur
WHERE u.UserId = ur.UserId AND @RoleId = ur.RoleId AND u.ApplicationId = @ApplicationId AND LoweredUserName LIKE LOWER(@UserNameToMatch)
ORDER BY u.UserName
RETURN(0)
END
GO
/*************************************************************/
/*************************************************************/
IF (EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_Roles_GetAllRoles')
AND (type = 'P')))
DROP PROCEDURE dbo.aspnet_Roles_GetAllRoles
GO
CREATE PROCEDURE dbo.aspnet_Roles_GetAllRoles (
@ApplicationName nvarchar(256))
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
SELECT @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF (@ApplicationId IS NULL)
RETURN
SELECT RoleName
FROM dbo.aspnet_Roles WHERE ApplicationId = @ApplicationId
ORDER BY RoleName
END
GO
/*************************************************************/
/*************************************************************/
IF (NOT EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'vw_aspnet_Roles')
AND (type = 'V')))
BEGIN
PRINT 'Creating the vw_aspnet_Roles view...'
EXEC(N'
CREATE VIEW [dbo].[vw_aspnet_Roles]
AS SELECT [dbo].[aspnet_Roles].[ApplicationId], [dbo].[aspnet_Roles].[RoleId], [dbo].[aspnet_Roles].[RoleName], [dbo].[aspnet_Roles].[LoweredRoleName], [dbo].[aspnet_Roles].[Description]
FROM [dbo].[aspnet_Roles]
')
END
GO
/*************************************************************/
/*************************************************************/
IF (NOT EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'vw_aspnet_UsersInRoles')
AND (type = 'V')))
BEGIN
PRINT 'Creating the vw_aspnet_UsersInRoles view...'
EXEC(N'
CREATE VIEW [dbo].[vw_aspnet_UsersInRoles]
AS SELECT [dbo].[aspnet_UsersInRoles].[UserId], [dbo].[aspnet_UsersInRoles].[RoleId]
FROM [dbo].[aspnet_UsersInRoles]
')
END
GO
/*************************************************************/
/*************************************************************/
--
--Create Role Manager schema version
--
DECLARE @command nvarchar(4000)
SET @command = 'GRANT EXECUTE ON [dbo].aspnet_RegisterSchemaVersion TO ' + QUOTENAME(user)
EXECUTE (@command)
GO
EXEC [dbo].aspnet_RegisterSchemaVersion N'Role Manager', N'1', 1, 1
GO
/*************************************************************/
/*************************************************************/
--
--Create Role Manager roles
--
IF ( NOT EXISTS ( SELECT name
FROM sysusers
WHERE issqlrole = 1
AND name = N'aspnet_Roles_FullAccess' ) )
EXEC sp_addrole N'aspnet_Roles_FullAccess'
IF ( NOT EXISTS ( SELECT name
FROM sysusers
WHERE issqlrole = 1
AND name = N'aspnet_Roles_BasicAccess' ) )
EXEC sp_addrole N'aspnet_Roles_BasicAccess'
IF ( NOT EXISTS ( SELECT name
FROM sysusers
WHERE issqlrole = 1
AND name = N'aspnet_Roles_ReportingAccess' ) )
EXEC sp_addrole N'aspnet_Roles_ReportingAccess'
GO
EXEC sp_addrolemember N'aspnet_Roles_BasicAccess', N'aspnet_Roles_FullAccess'
EXEC sp_addrolemember N'aspnet_Roles_ReportingAccess', N'aspnet_Roles_FullAccess'
GO
--
--Stored Procedure rights for BasicAccess
--
GRANT EXECUTE ON dbo.aspnet_UsersInRoles_IsUserInRole TO aspnet_Roles_BasicAccess
GRANT EXECUTE ON dbo.aspnet_UsersInRoles_GetRolesForUser TO aspnet_Roles_BasicAccess
GRANT EXECUTE ON dbo.aspnet_CheckSchemaVersion TO aspnet_Roles_BasicAccess
GRANT EXECUTE ON dbo.aspnet_RegisterSchemaVersion TO aspnet_Roles_BasicAccess
GRANT EXECUTE ON dbo.aspnet_UnRegisterSchemaVersion TO aspnet_Roles_BasicAccess
--
--Stored Procedure rights for ReportingAccess
--
GRANT EXECUTE ON dbo.aspnet_UsersInRoles_IsUserInRole TO aspnet_Roles_ReportingAccess
GRANT EXECUTE ON dbo.aspnet_UsersInRoles_GetRolesForUser TO aspnet_Roles_ReportingAccess
GRANT EXECUTE ON dbo.aspnet_Roles_RoleExists TO aspnet_Roles_ReportingAccess
GRANT EXECUTE ON dbo.aspnet_UsersInRoles_GetUsersInRoles TO aspnet_Roles_ReportingAccess
GRANT EXECUTE ON dbo.aspnet_UsersInRoles_FindUsersInRole TO aspnet_Roles_ReportingAccess
GRANT EXECUTE ON dbo.aspnet_Roles_GetAllRoles TO aspnet_Roles_ReportingAccess
GRANT EXECUTE ON dbo.aspnet_CheckSchemaVersion TO aspnet_Roles_ReportingAccess
GRANT EXECUTE ON dbo.aspnet_RegisterSchemaVersion TO aspnet_Roles_ReportingAccess
GRANT EXECUTE ON dbo.aspnet_UnRegisterSchemaVersion TO aspnet_Roles_ReportingAccess
--
--Additional stored procedure rights for FullAccess
--
GRANT EXECUTE ON dbo.aspnet_Roles_CreateRole TO aspnet_Roles_FullAccess
GRANT EXECUTE ON dbo.aspnet_Roles_DeleteRole TO aspnet_Roles_FullAccess
GRANT EXECUTE ON dbo.aspnet_UsersInRoles_AddUsersToRoles TO aspnet_Roles_FullAccess
GRANT EXECUTE ON dbo.aspnet_UsersInRoles_RemoveUsersFromRoles TO aspnet_Roles_FullAccess
--
--View rights
--
GRANT SELECT ON dbo.vw_aspnet_Applications TO aspnet_Roles_ReportingAccess
GRANT SELECT ON dbo.vw_aspnet_Users TO aspnet_Roles_ReportingAccess
GRANT SELECT ON dbo.vw_aspnet_Roles TO aspnet_Roles_ReportingAccess
GRANT SELECT ON dbo.vw_aspnet_UsersInRoles TO aspnet_Roles_ReportingAccess
GO
/*************************************************************/
/*************************************************************/
/*************************************************************/
/*************************************************************/
DECLARE @command nvarchar(4000)
SET @command = 'REVOKE EXECUTE ON [dbo].aspnet_RegisterSchemaVersion FROM ' + QUOTENAME(user)
EXECUTE (@command)
GO
PRINT '---------------------------------------'
PRINT 'Completed execution of InstallRoles.SQL'
PRINT '---------------------------------------'