Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- ALTER DATABASE [UTO2015] SET ANSI_NULL_DEFAULT OFF
- GO
- ALTER DATABASE [UTO2015] SET ANSI_NULLS OFF
- GO
- ALTER DATABASE [UTO2015] SET ANSI_PADDING OFF
- GO
- ALTER DATABASE [UTO2015] SET ANSI_WARNINGS OFF
- GO
- ALTER DATABASE [UTO2015] SET ARITHABORT OFF
- GO
- ALTER DATABASE [UTO2015] SET AUTO_CLOSE OFF
- GO
- ALTER DATABASE [UTO2015] SET AUTO_SHRINK OFF
- GO
- ALTER DATABASE [UTO2015] SET AUTO_UPDATE_STATISTICS ON
- GO
- ALTER DATABASE [UTO2015] SET CURSOR_CLOSE_ON_COMMIT OFF
- GO
- ALTER DATABASE [UTO2015] SET CURSOR_DEFAULT GLOBAL
- GO
- ALTER DATABASE [UTO2015] SET CONCAT_NULL_YIELDS_NULL OFF
- GO
- ALTER DATABASE [UTO2015] SET NUMERIC_ROUNDABORT OFF
- GO
- ALTER DATABASE [UTO2015] SET QUOTED_IDENTIFIER OFF
- GO
- ALTER DATABASE [UTO2015] SET RECURSIVE_TRIGGERS OFF
- GO
- ALTER DATABASE [UTO2015] SET DISABLE_BROKER
- GO
- ALTER DATABASE [UTO2015] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
- GO
- ALTER DATABASE [UTO2015] SET DATE_CORRELATION_OPTIMIZATION OFF
- GO
- ALTER DATABASE [UTO2015] SET TRUSTWORTHY OFF
- GO
- ALTER DATABASE [UTO2015] SET ALLOW_SNAPSHOT_ISOLATION OFF
- GO
- ALTER DATABASE [UTO2015] SET PARAMETERIZATION SIMPLE
- GO
- ALTER DATABASE [UTO2015] SET READ_COMMITTED_SNAPSHOT OFF
- GO
- ALTER DATABASE [UTO2015] SET HONOR_BROKER_PRIORITY OFF
- GO
- ALTER DATABASE [UTO2015] SET RECOVERY FULL
- GO
- ALTER DATABASE [UTO2015] SET MULTI_USER
- GO
- ALTER DATABASE [UTO2015] SET PAGE_VERIFY CHECKSUM
- GO
- ALTER DATABASE [UTO2015] SET DB_CHAINING OFF
- GO
- ALTER DATABASE [UTO2015] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
- GO
- ALTER DATABASE [UTO2015] SET TARGET_RECOVERY_TIME = 0 SECONDS
- GO
- ALTER DATABASE [UTO2015] SET DELAYED_DURABILITY = DISABLED
- GO
- USE [UTO2015]
- GO
- /****** Object: User [uto.web] Script Date: 16.2.2017. 14:59:48 ******/
- CREATE USER [uto.web] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]
- GO
- /****** Object: DatabaseRole [aspnet_WebEvent_FullAccess] Script Date: 16.2.2017. 14:59:48 ******/
- CREATE ROLE [aspnet_WebEvent_FullAccess]
- GO
- /****** Object: DatabaseRole [aspnet_Roles_ReportingAccess] Script Date: 16.2.2017. 14:59:48 ******/
- CREATE ROLE [aspnet_Roles_ReportingAccess]
- GO
- /****** Object: DatabaseRole [aspnet_Roles_FullAccess] Script Date: 16.2.2017. 14:59:48 ******/
- CREATE ROLE [aspnet_Roles_FullAccess]
- GO
- /****** Object: DatabaseRole [aspnet_Roles_BasicAccess] Script Date: 16.2.2017. 14:59:48 ******/
- CREATE ROLE [aspnet_Roles_BasicAccess]
- GO
- /****** Object: DatabaseRole [aspnet_Profile_ReportingAccess] Script Date: 16.2.2017. 14:59:48 ******/
- CREATE ROLE [aspnet_Profile_ReportingAccess]
- GO
- /****** Object: DatabaseRole [aspnet_Profile_FullAccess] Script Date: 16.2.2017. 14:59:48 ******/
- CREATE ROLE [aspnet_Profile_FullAccess]
- GO
- /****** Object: DatabaseRole [aspnet_Profile_BasicAccess] Script Date: 16.2.2017. 14:59:48 ******/
- CREATE ROLE [aspnet_Profile_BasicAccess]
- GO
- /****** Object: DatabaseRole [aspnet_Personalization_ReportingAccess] Script Date: 16.2.2017. 14:59:48 ******/
- CREATE ROLE [aspnet_Personalization_ReportingAccess]
- GO
- /****** Object: DatabaseRole [aspnet_Personalization_FullAccess] Script Date: 16.2.2017. 14:59:48 ******/
- CREATE ROLE [aspnet_Personalization_FullAccess]
- GO
- /****** Object: DatabaseRole [aspnet_Personalization_BasicAccess] Script Date: 16.2.2017. 14:59:48 ******/
- CREATE ROLE [aspnet_Personalization_BasicAccess]
- GO
- /****** Object: DatabaseRole [aspnet_Membership_ReportingAccess] Script Date: 16.2.2017. 14:59:48 ******/
- CREATE ROLE [aspnet_Membership_ReportingAccess]
- GO
- /****** Object: DatabaseRole [aspnet_Membership_FullAccess] Script Date: 16.2.2017. 14:59:48 ******/
- CREATE ROLE [aspnet_Membership_FullAccess]
- GO
- /****** Object: DatabaseRole [aspnet_Membership_BasicAccess] Script Date: 16.2.2017. 14:59:48 ******/
- CREATE ROLE [aspnet_Membership_BasicAccess]
- GO
- ALTER ROLE [db_owner] ADD MEMBER [uto.web]
- GO
- ALTER ROLE [aspnet_Roles_BasicAccess] ADD MEMBER [aspnet_Roles_FullAccess]
- GO
- ALTER ROLE [aspnet_Roles_ReportingAccess] ADD MEMBER [aspnet_Roles_FullAccess]
- GO
- ALTER ROLE [aspnet_Profile_BasicAccess] ADD MEMBER [aspnet_Profile_FullAccess]
- GO
- ALTER ROLE [aspnet_Profile_ReportingAccess] ADD MEMBER [aspnet_Profile_FullAccess]
- GO
- ALTER ROLE [aspnet_Personalization_BasicAccess] ADD MEMBER [aspnet_Personalization_FullAccess]
- GO
- ALTER ROLE [aspnet_Personalization_ReportingAccess] ADD MEMBER [aspnet_Personalization_FullAccess]
- GO
- ALTER ROLE [aspnet_Membership_BasicAccess] ADD MEMBER [aspnet_Membership_FullAccess]
- GO
- ALTER ROLE [aspnet_Membership_ReportingAccess] ADD MEMBER [aspnet_Membership_FullAccess]
- GO
- /****** Object: Schema [aspnet_Membership_BasicAccess] Script Date: 16.2.2017. 14:59:48 ******/
- CREATE SCHEMA [aspnet_Membership_BasicAccess]
- GO
- /****** Object: Schema [aspnet_Membership_FullAccess] Script Date: 16.2.2017. 14:59:48 ******/
- CREATE SCHEMA [aspnet_Membership_FullAccess]
- GO
- /****** Object: Schema [aspnet_Membership_ReportingAccess] Script Date: 16.2.2017. 14:59:48 ******/
- CREATE SCHEMA [aspnet_Membership_ReportingAccess]
- GO
- /****** Object: Schema [aspnet_Personalization_BasicAccess] Script Date: 16.2.2017. 14:59:48 ******/
- CREATE SCHEMA [aspnet_Personalization_BasicAccess]
- GO
- /****** Object: Schema [aspnet_Personalization_FullAccess] Script Date: 16.2.2017. 14:59:48 ******/
- CREATE SCHEMA [aspnet_Personalization_FullAccess]
- GO
- /****** Object: Schema [aspnet_Personalization_ReportingAccess] Script Date: 16.2.2017. 14:59:48 ******/
- CREATE SCHEMA [aspnet_Personalization_ReportingAccess]
- GO
- /****** Object: Schema [aspnet_Profile_BasicAccess] Script Date: 16.2.2017. 14:59:48 ******/
- CREATE SCHEMA [aspnet_Profile_BasicAccess]
- GO
- /****** Object: Schema [aspnet_Profile_FullAccess] Script Date: 16.2.2017. 14:59:48 ******/
- CREATE SCHEMA [aspnet_Profile_FullAccess]
- GO
- /****** Object: Schema [aspnet_Profile_ReportingAccess] Script Date: 16.2.2017. 14:59:48 ******/
- CREATE SCHEMA [aspnet_Profile_ReportingAccess]
- GO
- /****** Object: Schema [aspnet_Roles_BasicAccess] Script Date: 16.2.2017. 14:59:48 ******/
- CREATE SCHEMA [aspnet_Roles_BasicAccess]
- GO
- /****** Object: Schema [aspnet_Roles_FullAccess] Script Date: 16.2.2017. 14:59:48 ******/
- CREATE SCHEMA [aspnet_Roles_FullAccess]
- GO
- /****** Object: Schema [aspnet_Roles_ReportingAccess] Script Date: 16.2.2017. 14:59:48 ******/
- CREATE SCHEMA [aspnet_Roles_ReportingAccess]
- GO
- /****** Object: Schema [aspnet_WebEvent_FullAccess] Script Date: 16.2.2017. 14:59:48 ******/
- CREATE SCHEMA [aspnet_WebEvent_FullAccess]
- GO
- /****** Object: Table [dbo].[aspnet_Applications] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TABLE [dbo].[aspnet_Applications](
- [ApplicationName] [nvarchar](256) NOT NULL,
- [LoweredApplicationName] [nvarchar](256) NOT NULL,
- [ApplicationId] [uniqueidentifier] NOT NULL,
- [Description] [nvarchar](256) NULL,
- PRIMARY KEY NONCLUSTERED
- (
- [ApplicationId] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
- UNIQUE NONCLUSTERED
- (
- [LoweredApplicationName] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
- UNIQUE NONCLUSTERED
- (
- [ApplicationName] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- GO
- SET ANSI_PADDING ON
- GO
- /****** Object: Index [aspnet_Applications_Index] Script Date: 16.2.2017. 14:59:48 ******/
- CREATE CLUSTERED INDEX [aspnet_Applications_Index] ON [dbo].[aspnet_Applications]
- (
- [LoweredApplicationName] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- GO
- /****** Object: Table [dbo].[aspnet_Membership] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TABLE [dbo].[aspnet_Membership](
- [ApplicationId] [uniqueidentifier] NOT NULL,
- [UserId] [uniqueidentifier] NOT NULL,
- [Password] [nvarchar](128) NOT NULL,
- [PasswordFormat] [int] NOT NULL,
- [PasswordSalt] [nvarchar](128) NOT NULL,
- [MobilePIN] [nvarchar](16) NULL,
- [Email] [nvarchar](256) NULL,
- [LoweredEmail] [nvarchar](256) NULL,
- [PasswordQuestion] [nvarchar](256) NULL,
- [PasswordAnswer] [nvarchar](128) NULL,
- [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] NULL,
- PRIMARY KEY NONCLUSTERED
- (
- [UserId] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
- GO
- SET ANSI_PADDING ON
- GO
- /****** Object: Index [aspnet_Membership_index] Script Date: 16.2.2017. 14:59:48 ******/
- CREATE CLUSTERED INDEX [aspnet_Membership_index] ON [dbo].[aspnet_Membership]
- (
- [ApplicationId] ASC,
- [LoweredEmail] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- GO
- /****** Object: Table [dbo].[aspnet_Paths] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TABLE [dbo].[aspnet_Paths](
- [ApplicationId] [uniqueidentifier] NOT NULL,
- [PathId] [uniqueidentifier] NOT NULL,
- [Path] [nvarchar](256) NOT NULL,
- [LoweredPath] [nvarchar](256) NOT NULL,
- PRIMARY KEY NONCLUSTERED
- (
- [PathId] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- GO
- SET ANSI_PADDING ON
- GO
- /****** Object: Index [aspnet_Paths_index] Script Date: 16.2.2017. 14:59:48 ******/
- CREATE UNIQUE CLUSTERED INDEX [aspnet_Paths_index] ON [dbo].[aspnet_Paths]
- (
- [ApplicationId] ASC,
- [LoweredPath] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- GO
- /****** Object: Table [dbo].[aspnet_PersonalizationAllUsers] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TABLE [dbo].[aspnet_PersonalizationAllUsers](
- [PathId] [uniqueidentifier] NOT NULL,
- [PageSettings] [image] NOT NULL,
- [LastUpdatedDate] [datetime] NOT NULL,
- PRIMARY KEY CLUSTERED
- (
- [PathId] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
- GO
- /****** Object: Table [dbo].[aspnet_PersonalizationPerUser] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TABLE [dbo].[aspnet_PersonalizationPerUser](
- [Id] [uniqueidentifier] NOT NULL,
- [PathId] [uniqueidentifier] NULL,
- [UserId] [uniqueidentifier] NULL,
- [PageSettings] [image] NOT NULL,
- [LastUpdatedDate] [datetime] NOT NULL,
- PRIMARY KEY NONCLUSTERED
- (
- [Id] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
- GO
- /****** Object: Index [aspnet_PersonalizationPerUser_index1] Script Date: 16.2.2017. 14:59:48 ******/
- CREATE UNIQUE CLUSTERED INDEX [aspnet_PersonalizationPerUser_index1] ON [dbo].[aspnet_PersonalizationPerUser]
- (
- [PathId] ASC,
- [UserId] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- GO
- /****** Object: Table [dbo].[aspnet_Profile] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TABLE [dbo].[aspnet_Profile](
- [UserId] [uniqueidentifier] NOT NULL,
- [PropertyNames] [ntext] NOT NULL,
- [PropertyValuesString] [ntext] NOT NULL,
- [PropertyValuesBinary] [image] NOT NULL,
- [LastUpdatedDate] [datetime] NOT NULL,
- PRIMARY KEY CLUSTERED
- (
- [UserId] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
- GO
- /****** Object: Table [dbo].[aspnet_Roles] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TABLE [dbo].[aspnet_Roles](
- [ApplicationId] [uniqueidentifier] NOT NULL,
- [RoleId] [uniqueidentifier] NOT NULL,
- [RoleName] [nvarchar](256) NOT NULL,
- [LoweredRoleName] [nvarchar](256) NOT NULL,
- [Description] [nvarchar](256) NULL,
- PRIMARY KEY NONCLUSTERED
- (
- [RoleId] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- GO
- SET ANSI_PADDING ON
- GO
- /****** Object: Index [aspnet_Roles_index1] Script Date: 16.2.2017. 14:59:48 ******/
- CREATE UNIQUE CLUSTERED INDEX [aspnet_Roles_index1] ON [dbo].[aspnet_Roles]
- (
- [ApplicationId] ASC,
- [LoweredRoleName] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- GO
- /****** Object: Table [dbo].[aspnet_SchemaVersions] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TABLE [dbo].[aspnet_SchemaVersions](
- [Feature] [nvarchar](128) NOT NULL,
- [CompatibleSchemaVersion] [nvarchar](128) NOT NULL,
- [IsCurrentVersion] [bit] NOT NULL,
- PRIMARY KEY CLUSTERED
- (
- [Feature] ASC,
- [CompatibleSchemaVersion] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- GO
- /****** Object: Table [dbo].[aspnet_Users] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TABLE [dbo].[aspnet_Users](
- [ApplicationId] [uniqueidentifier] NOT NULL,
- [UserId] [uniqueidentifier] NOT NULL,
- [UserName] [nvarchar](256) NOT NULL,
- [LoweredUserName] [nvarchar](256) NOT NULL,
- [MobileAlias] [nvarchar](16) NULL,
- [IsAnonymous] [bit] NOT NULL,
- [LastActivityDate] [datetime] NOT NULL,
- PRIMARY KEY NONCLUSTERED
- (
- [UserId] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- GO
- SET ANSI_PADDING ON
- GO
- /****** Object: Index [aspnet_Users_Index] Script Date: 16.2.2017. 14:59:48 ******/
- CREATE UNIQUE CLUSTERED INDEX [aspnet_Users_Index] ON [dbo].[aspnet_Users]
- (
- [ApplicationId] ASC,
- [LoweredUserName] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- GO
- /****** Object: Table [dbo].[aspnet_UsersInRoles] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TABLE [dbo].[aspnet_UsersInRoles](
- [UserId] [uniqueidentifier] NOT NULL,
- [RoleId] [uniqueidentifier] NOT NULL,
- PRIMARY KEY CLUSTERED
- (
- [UserId] ASC,
- [RoleId] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- GO
- /****** Object: Table [dbo].[aspnet_WebEvent_Events] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- SET ANSI_PADDING ON
- GO
- CREATE TABLE [dbo].[aspnet_WebEvent_Events](
- [EventId] [char](32) NOT NULL,
- [EventTimeUtc] [datetime] NOT NULL,
- [EventTime] [datetime] NOT NULL,
- [EventType] [nvarchar](256) NOT NULL,
- [EventSequence] [decimal](19, 0) NOT NULL,
- [EventOccurrence] [decimal](19, 0) NOT NULL,
- [EventCode] [int] NOT NULL,
- [EventDetailCode] [int] NOT NULL,
- [Message] [nvarchar](1024) NULL,
- [ApplicationPath] [nvarchar](256) NULL,
- [ApplicationVirtualPath] [nvarchar](256) NULL,
- [MachineName] [nvarchar](256) NOT NULL,
- [RequestUrl] [nvarchar](1024) NULL,
- [ExceptionType] [nvarchar](256) NULL,
- [Details] [ntext] NULL,
- PRIMARY KEY CLUSTERED
- (
- [EventId] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
- GO
- SET ANSI_PADDING OFF
- GO
- /****** Object: Table [dbo].[Card] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TABLE [dbo].[Card](
- [ID] [int] NOT NULL,
- CONSTRAINT [PK_Card] PRIMARY KEY CLUSTERED
- (
- [ID] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- GO
- /****** Object: Table [dbo].[CardMap] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TABLE [dbo].[CardMap](
- [ID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
- [CardID] [int] NOT NULL,
- [SortOrder] [int] NOT NULL,
- [HasPopup] [bit] NOT NULL,
- [PopupTop] [int] NULL,
- [PopupLeft] [int] NULL,
- CONSTRAINT [PK_CardMap] PRIMARY KEY CLUSTERED
- (
- [ID] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- GO
- /****** Object: Table [dbo].[Contact] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TABLE [dbo].[Contact](
- [ID] [uniqueidentifier] NOT NULL,
- [TimeStamp] [datetime] NOT NULL,
- [IPAddress] [nvarchar](100) NOT NULL,
- [Name] [nvarchar](max) NOT NULL,
- [Company] [nvarchar](max) NULL,
- [Title] [nvarchar](100) NOT NULL,
- [City] [nvarchar](100) NULL,
- [ZIP] [nvarchar](10) NULL,
- [State] [nvarchar](100) NULL,
- [Country] [nvarchar](200) NULL,
- [Email] [nvarchar](200) NOT NULL,
- [Question] [nvarchar](max) NULL,
- [JoinMailing] [bit] NOT NULL,
- [RepresentativeContact] [bit] NOT NULL,
- CONSTRAINT [PK_Contact] PRIMARY KEY CLUSTERED
- (
- [ID] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
- GO
- /****** Object: Table [dbo].[GameEntry] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TABLE [dbo].[GameEntry](
- [ID] [uniqueidentifier] NOT NULL,
- [TimeStampStart] [datetime] NOT NULL,
- [TimeStampEnd] [datetime] NULL,
- [Finished] [bit] NOT NULL,
- [TotalSeconds] [int] NOT NULL,
- [IPAddress] [nvarchar](max) NOT NULL,
- [Started] [bit] NOT NULL,
- [Name] [nvarchar](200) NULL,
- [Company] [nvarchar](200) NULL,
- [Title] [nvarchar](200) NULL,
- [ZIP] [nvarchar](50) NULL,
- [Email] [nvarchar](200) NULL,
- [Newsletter] [bit] NOT NULL,
- [Rules] [bit] NOT NULL,
- [EntryDone] [bit] NOT NULL,
- [CardID] [int] NOT NULL,
- [Kind] [int] NOT NULL,
- [CustomerEmail] [nvarchar](200) NULL,
- [AnswerID] [uniqueidentifier] NULL,
- [Moves] [int] NOT NULL,
- [Score] [int] NOT NULL,
- [TwoMinuteClicks] [int] NOT NULL,
- CONSTRAINT [PK_GameEntry] PRIMARY KEY CLUSTERED
- (
- [ID] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
- GO
- /****** Object: Table [dbo].[QuizAnswer] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TABLE [dbo].[QuizAnswer](
- [ID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
- [QuestionID] [uniqueidentifier] NOT NULL,
- [Answer] [nvarchar](max) NOT NULL,
- [Correct] [bit] NOT NULL,
- [SortOrder] [int] NOT NULL,
- CONSTRAINT [PK_QuizAnswer] PRIMARY KEY CLUSTERED
- (
- [ID] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
- GO
- /****** Object: Table [dbo].[QuizQuestion] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TABLE [dbo].[QuizQuestion](
- [ID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
- [Question] [nvarchar](max) NOT NULL,
- [CardID] [int] NOT NULL,
- [SortOrder] [int] NOT NULL,
- [Response] [nvarchar](max) NULL,
- CONSTRAINT [PK_QuizQuestion] PRIMARY KEY CLUSTERED
- (
- [ID] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
- GO
- /****** Object: Table [dbo].[ReferralClicks] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TABLE [dbo].[ReferralClicks](
- [ID] [uniqueidentifier] NOT NULL,
- [URL] [nvarchar](max) NOT NULL,
- [TimeStamp] [datetime] NOT NULL,
- CONSTRAINT [PK_ReferralClicks] PRIMARY KEY CLUSTERED
- (
- [ID] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
- GO
- /****** Object: Table [dbo].[TwoMinuteClicks] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TABLE [dbo].[TwoMinuteClicks](
- [ID] [uniqueidentifier] NOT NULL,
- [IPAddress] [nvarchar](100) NOT NULL,
- [TimeStamp] [datetime] NOT NULL,
- [Link] [nvarchar](100) NOT NULL,
- [GameEntryID] [uniqueidentifier] NULL,
- CONSTRAINT [PK_TwoMinuteClicks] PRIMARY KEY CLUSTERED
- (
- [ID] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- GO
- /****** Object: Table [dbo].[Winner] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TABLE [dbo].[Winner](
- [ID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
- [Date] [date] NOT NULL,
- [Kind] [int] NOT NULL,
- [WinnerKind] [int] NOT NULL,
- [Name] [nvarchar](max) NOT NULL,
- [Company] [nvarchar](max) NOT NULL,
- [Email] [nvarchar](max) NOT NULL,
- CONSTRAINT [PK_Winner] PRIMARY KEY CLUSTERED
- (
- [ID] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
- GO
- /****** Object: View [dbo].[vw_aspnet_Applications] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- 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]
- GO
- /****** Object: View [dbo].[vw_aspnet_MembershipUsers] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- 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]
- GO
- /****** Object: View [dbo].[vw_aspnet_Profiles] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- CREATE VIEW [dbo].[vw_aspnet_Profiles]
- AS SELECT [dbo].[aspnet_Profile].[UserId], [dbo].[aspnet_Profile].[LastUpdatedDate],
- [DataSize]= DATALENGTH([dbo].[aspnet_Profile].[PropertyNames])
- + DATALENGTH([dbo].[aspnet_Profile].[PropertyValuesString])
- + DATALENGTH([dbo].[aspnet_Profile].[PropertyValuesBinary])
- FROM [dbo].[aspnet_Profile]
- GO
- /****** Object: View [dbo].[vw_aspnet_Roles] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- 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]
- GO
- /****** Object: View [dbo].[vw_aspnet_Users] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- 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]
- GO
- /****** Object: View [dbo].[vw_aspnet_UsersInRoles] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- CREATE VIEW [dbo].[vw_aspnet_UsersInRoles]
- AS SELECT [dbo].[aspnet_UsersInRoles].[UserId], [dbo].[aspnet_UsersInRoles].[RoleId]
- FROM [dbo].[aspnet_UsersInRoles]
- GO
- /****** Object: View [dbo].[vw_aspnet_WebPartState_Paths] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- CREATE VIEW [dbo].[vw_aspnet_WebPartState_Paths]
- AS SELECT [dbo].[aspnet_Paths].[ApplicationId], [dbo].[aspnet_Paths].[PathId], [dbo].[aspnet_Paths].[Path], [dbo].[aspnet_Paths].[LoweredPath]
- FROM [dbo].[aspnet_Paths]
- GO
- /****** Object: View [dbo].[vw_aspnet_WebPartState_Shared] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- CREATE VIEW [dbo].[vw_aspnet_WebPartState_Shared]
- AS SELECT [dbo].[aspnet_PersonalizationAllUsers].[PathId], [DataSize]=DATALENGTH([dbo].[aspnet_PersonalizationAllUsers].[PageSettings]), [dbo].[aspnet_PersonalizationAllUsers].[LastUpdatedDate]
- FROM [dbo].[aspnet_PersonalizationAllUsers]
- GO
- /****** Object: View [dbo].[vw_aspnet_WebPartState_User] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- CREATE VIEW [dbo].[vw_aspnet_WebPartState_User]
- AS SELECT [dbo].[aspnet_PersonalizationPerUser].[PathId], [dbo].[aspnet_PersonalizationPerUser].[UserId], [DataSize]=DATALENGTH([dbo].[aspnet_PersonalizationPerUser].[PageSettings]), [dbo].[aspnet_PersonalizationPerUser].[LastUpdatedDate]
- FROM [dbo].[aspnet_PersonalizationPerUser]
- GO
- /****** Object: Index [aspnet_PersonalizationPerUser_ncindex2] Script Date: 16.2.2017. 14:59:48 ******/
- CREATE UNIQUE NONCLUSTERED INDEX [aspnet_PersonalizationPerUser_ncindex2] ON [dbo].[aspnet_PersonalizationPerUser]
- (
- [UserId] ASC,
- [PathId] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- GO
- /****** Object: Index [aspnet_Users_Index2] Script Date: 16.2.2017. 14:59:48 ******/
- CREATE NONCLUSTERED INDEX [aspnet_Users_Index2] ON [dbo].[aspnet_Users]
- (
- [ApplicationId] ASC,
- [LastActivityDate] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- GO
- /****** Object: Index [aspnet_UsersInRoles_index] Script Date: 16.2.2017. 14:59:48 ******/
- CREATE NONCLUSTERED INDEX [aspnet_UsersInRoles_index] ON [dbo].[aspnet_UsersInRoles]
- (
- [RoleId] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- GO
- ALTER TABLE [dbo].[aspnet_Applications] ADD DEFAULT (newid()) FOR [ApplicationId]
- GO
- ALTER TABLE [dbo].[aspnet_Membership] ADD DEFAULT ((0)) FOR [PasswordFormat]
- GO
- ALTER TABLE [dbo].[aspnet_Paths] ADD DEFAULT (newid()) FOR [PathId]
- GO
- ALTER TABLE [dbo].[aspnet_PersonalizationPerUser] ADD DEFAULT (newid()) FOR [Id]
- GO
- ALTER TABLE [dbo].[aspnet_Roles] ADD DEFAULT (newid()) FOR [RoleId]
- GO
- ALTER TABLE [dbo].[aspnet_Users] ADD DEFAULT (newid()) FOR [UserId]
- GO
- ALTER TABLE [dbo].[aspnet_Users] ADD DEFAULT (NULL) FOR [MobileAlias]
- GO
- ALTER TABLE [dbo].[aspnet_Users] ADD DEFAULT ((0)) FOR [IsAnonymous]
- GO
- ALTER TABLE [dbo].[CardMap] ADD CONSTRAINT [DF_CardMap_ID] DEFAULT (newid()) FOR [ID]
- GO
- ALTER TABLE [dbo].[GameEntry] ADD CONSTRAINT [DF_GameEntry_Finished] DEFAULT ((0)) FOR [Finished]
- GO
- ALTER TABLE [dbo].[GameEntry] ADD CONSTRAINT [DF_GameEntry_Started] DEFAULT ((0)) FOR [Started]
- GO
- ALTER TABLE [dbo].[GameEntry] ADD CONSTRAINT [DF_GameEntry_Newsletter] DEFAULT ((0)) FOR [Newsletter]
- GO
- ALTER TABLE [dbo].[GameEntry] ADD CONSTRAINT [DF_GameEntry_Rules] DEFAULT ((0)) FOR [Rules]
- GO
- ALTER TABLE [dbo].[GameEntry] ADD CONSTRAINT [DF_GameEntry_EntryDone] DEFAULT ((0)) FOR [EntryDone]
- GO
- ALTER TABLE [dbo].[GameEntry] ADD CONSTRAINT [DF_GameEntry_CardID] DEFAULT ((1)) FOR [CardID]
- GO
- ALTER TABLE [dbo].[GameEntry] ADD CONSTRAINT [DF_GameEntry_Kind] DEFAULT ((1)) FOR [Kind]
- GO
- ALTER TABLE [dbo].[GameEntry] ADD CONSTRAINT [DF_GameEntry_Moves] DEFAULT ((0)) FOR [Moves]
- GO
- ALTER TABLE [dbo].[GameEntry] ADD CONSTRAINT [DF_GameEntry_Score] DEFAULT ((0)) FOR [Score]
- GO
- ALTER TABLE [dbo].[GameEntry] ADD CONSTRAINT [DF_GameEntry_TwoMinuteClicks] DEFAULT ((0)) FOR [TwoMinuteClicks]
- GO
- ALTER TABLE [dbo].[QuizAnswer] ADD CONSTRAINT [DF_QuizAnswer_ID] DEFAULT (newid()) FOR [ID]
- GO
- ALTER TABLE [dbo].[QuizAnswer] ADD CONSTRAINT [DF_QuizAnswer_Correct] DEFAULT ((0)) FOR [Correct]
- GO
- ALTER TABLE [dbo].[QuizAnswer] ADD CONSTRAINT [DF_QuizAnswer_SortOrder] DEFAULT ((0)) FOR [SortOrder]
- GO
- ALTER TABLE [dbo].[QuizQuestion] ADD CONSTRAINT [DF_QuizQuestion_ID] DEFAULT (newid()) FOR [ID]
- GO
- ALTER TABLE [dbo].[Winner] ADD CONSTRAINT [DF_Winner_ID] DEFAULT (newid()) FOR [ID]
- GO
- ALTER TABLE [dbo].[aspnet_Membership] WITH CHECK ADD FOREIGN KEY([ApplicationId])
- REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])
- GO
- ALTER TABLE [dbo].[aspnet_Membership] WITH CHECK ADD FOREIGN KEY([UserId])
- REFERENCES [dbo].[aspnet_Users] ([UserId])
- GO
- ALTER TABLE [dbo].[aspnet_Paths] WITH CHECK ADD FOREIGN KEY([ApplicationId])
- REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])
- GO
- ALTER TABLE [dbo].[aspnet_PersonalizationAllUsers] WITH CHECK ADD FOREIGN KEY([PathId])
- REFERENCES [dbo].[aspnet_Paths] ([PathId])
- GO
- ALTER TABLE [dbo].[aspnet_PersonalizationPerUser] WITH CHECK ADD FOREIGN KEY([PathId])
- REFERENCES [dbo].[aspnet_Paths] ([PathId])
- GO
- ALTER TABLE [dbo].[aspnet_PersonalizationPerUser] WITH CHECK ADD FOREIGN KEY([UserId])
- REFERENCES [dbo].[aspnet_Users] ([UserId])
- GO
- ALTER TABLE [dbo].[aspnet_Profile] WITH CHECK ADD FOREIGN KEY([UserId])
- REFERENCES [dbo].[aspnet_Users] ([UserId])
- GO
- ALTER TABLE [dbo].[aspnet_Roles] WITH CHECK ADD FOREIGN KEY([ApplicationId])
- REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])
- GO
- ALTER TABLE [dbo].[aspnet_Users] WITH CHECK ADD FOREIGN KEY([ApplicationId])
- REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])
- GO
- ALTER TABLE [dbo].[aspnet_UsersInRoles] WITH CHECK ADD FOREIGN KEY([RoleId])
- REFERENCES [dbo].[aspnet_Roles] ([RoleId])
- GO
- ALTER TABLE [dbo].[aspnet_UsersInRoles] WITH CHECK ADD FOREIGN KEY([UserId])
- REFERENCES [dbo].[aspnet_Users] ([UserId])
- GO
- ALTER TABLE [dbo].[CardMap] WITH CHECK ADD CONSTRAINT [FK_CardMap_Card] FOREIGN KEY([CardID])
- REFERENCES [dbo].[Card] ([ID])
- GO
- ALTER TABLE [dbo].[CardMap] CHECK CONSTRAINT [FK_CardMap_Card]
- GO
- ALTER TABLE [dbo].[GameEntry] WITH CHECK ADD CONSTRAINT [FK_GameEntry_Card] FOREIGN KEY([CardID])
- REFERENCES [dbo].[Card] ([ID])
- GO
- ALTER TABLE [dbo].[GameEntry] CHECK CONSTRAINT [FK_GameEntry_Card]
- GO
- ALTER TABLE [dbo].[GameEntry] WITH CHECK ADD CONSTRAINT [FK_GameEntry_QuizAnswer] FOREIGN KEY([AnswerID])
- REFERENCES [dbo].[QuizAnswer] ([ID])
- GO
- ALTER TABLE [dbo].[GameEntry] CHECK CONSTRAINT [FK_GameEntry_QuizAnswer]
- GO
- ALTER TABLE [dbo].[QuizAnswer] WITH CHECK ADD CONSTRAINT [FK_QuizAnswer_QuizQuestion] FOREIGN KEY([QuestionID])
- REFERENCES [dbo].[QuizQuestion] ([ID])
- GO
- ALTER TABLE [dbo].[QuizAnswer] CHECK CONSTRAINT [FK_QuizAnswer_QuizQuestion]
- GO
- ALTER TABLE [dbo].[TwoMinuteClicks] WITH CHECK ADD CONSTRAINT [FK_TwoMinuteClicks_GameEntry] FOREIGN KEY([GameEntryID])
- REFERENCES [dbo].[GameEntry] ([ID])
- GO
- ALTER TABLE [dbo].[TwoMinuteClicks] CHECK CONSTRAINT [FK_TwoMinuteClicks_GameEntry]
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_AnyDataInTables] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- 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
- /****** Object: StoredProcedure [dbo].[aspnet_Applications_CreateApplication] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- 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
- /****** Object: StoredProcedure [dbo].[aspnet_CheckSchemaVersion] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- 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
- /****** Object: StoredProcedure [dbo].[aspnet_Membership_ChangePasswordQuestionAndAnswer] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- 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
- /****** Object: StoredProcedure [dbo].[aspnet_Membership_CreateUser] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- 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
- /****** Object: StoredProcedure [dbo].[aspnet_Membership_FindUsersByEmail] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- 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
- /****** Object: StoredProcedure [dbo].[aspnet_Membership_FindUsersByName] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- 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
- /****** Object: StoredProcedure [dbo].[aspnet_Membership_GetAllUsers] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- 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
- /****** Object: StoredProcedure [dbo].[aspnet_Membership_GetNumberOfUsersOnline] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- 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
- /****** Object: StoredProcedure [dbo].[aspnet_Membership_GetPassword] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- 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
- /****** Object: StoredProcedure [dbo].[aspnet_Membership_GetPasswordWithFormat] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- 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
- /****** Object: StoredProcedure [dbo].[aspnet_Membership_GetUserByEmail] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- 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
- /****** Object: StoredProcedure [dbo].[aspnet_Membership_GetUserByName] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- 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 user ID from aspnet_users table
- SELECT TOP 1 @UserId = u.UserId
- 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
- SELECT 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 @UserId = u.UserId AND u.UserId = m.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
- /****** Object: StoredProcedure [dbo].[aspnet_Membership_GetUserByUserId] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- 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
- /****** Object: StoredProcedure [dbo].[aspnet_Membership_ResetPassword] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- 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
- /****** Object: StoredProcedure [dbo].[aspnet_Membership_SetPassword] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- 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
- /****** Object: StoredProcedure [dbo].[aspnet_Membership_UnlockUser] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- 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
- /****** Object: StoredProcedure [dbo].[aspnet_Membership_UpdateUser] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- 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
- /****** Object: StoredProcedure [dbo].[aspnet_Membership_UpdateUserInfo] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- 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
- /****** Object: StoredProcedure [dbo].[aspnet_Paths_CreatePath] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- CREATE PROCEDURE [dbo].[aspnet_Paths_CreatePath]
- @ApplicationId UNIQUEIDENTIFIER,
- @Path NVARCHAR(256),
- @PathId UNIQUEIDENTIFIER OUTPUT
- AS
- BEGIN
- BEGIN TRANSACTION
- IF (NOT EXISTS(SELECT * FROM dbo.aspnet_Paths WHERE LoweredPath = LOWER(@Path) AND ApplicationId = @ApplicationId))
- BEGIN
- INSERT dbo.aspnet_Paths (ApplicationId, Path, LoweredPath) VALUES (@ApplicationId, @Path, LOWER(@Path))
- END
- COMMIT TRANSACTION
- SELECT @PathId = PathId FROM dbo.aspnet_Paths WHERE LOWER(@Path) = LoweredPath AND ApplicationId = @ApplicationId
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_Personalization_GetApplicationId] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- CREATE PROCEDURE [dbo].[aspnet_Personalization_GetApplicationId] (
- @ApplicationName NVARCHAR(256),
- @ApplicationId UNIQUEIDENTIFIER OUT)
- AS
- BEGIN
- SELECT @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_PersonalizationAdministration_DeleteAllState] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- CREATE PROCEDURE [dbo].[aspnet_PersonalizationAdministration_DeleteAllState] (
- @AllUsersScope bit,
- @ApplicationName NVARCHAR(256),
- @Count int OUT)
- AS
- BEGIN
- DECLARE @ApplicationId UNIQUEIDENTIFIER
- EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
- IF (@ApplicationId IS NULL)
- SELECT @Count = 0
- ELSE
- BEGIN
- IF (@AllUsersScope = 1)
- DELETE FROM aspnet_PersonalizationAllUsers
- WHERE PathId IN
- (SELECT Paths.PathId
- FROM dbo.aspnet_Paths Paths
- WHERE Paths.ApplicationId = @ApplicationId)
- ELSE
- DELETE FROM aspnet_PersonalizationPerUser
- WHERE PathId IN
- (SELECT Paths.PathId
- FROM dbo.aspnet_Paths Paths
- WHERE Paths.ApplicationId = @ApplicationId)
- SELECT @Count = @@ROWCOUNT
- END
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_PersonalizationAdministration_FindState] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- CREATE PROCEDURE [dbo].[aspnet_PersonalizationAdministration_FindState] (
- @AllUsersScope bit,
- @ApplicationName NVARCHAR(256),
- @PageIndex INT,
- @PageSize INT,
- @Path NVARCHAR(256) = NULL,
- @UserName NVARCHAR(256) = NULL,
- @InactiveSinceDate DATETIME = NULL)
- AS
- BEGIN
- DECLARE @ApplicationId UNIQUEIDENTIFIER
- EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
- IF (@ApplicationId IS NULL)
- RETURN
- -- 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 selected results
- CREATE TABLE #PageIndex (
- IndexId int IDENTITY (0, 1) NOT NULL,
- ItemId UNIQUEIDENTIFIER
- )
- IF (@AllUsersScope = 1)
- BEGIN
- -- Insert into our temp table
- INSERT INTO #PageIndex (ItemId)
- SELECT Paths.PathId
- FROM dbo.aspnet_Paths Paths,
- ((SELECT Paths.PathId
- FROM dbo.aspnet_PersonalizationAllUsers AllUsers, dbo.aspnet_Paths Paths
- WHERE Paths.ApplicationId = @ApplicationId
- AND AllUsers.PathId = Paths.PathId
- AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path))
- ) AS SharedDataPerPath
- FULL OUTER JOIN
- (SELECT DISTINCT Paths.PathId
- FROM dbo.aspnet_PersonalizationPerUser PerUser, dbo.aspnet_Paths Paths
- WHERE Paths.ApplicationId = @ApplicationId
- AND PerUser.PathId = Paths.PathId
- AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path))
- ) AS UserDataPerPath
- ON SharedDataPerPath.PathId = UserDataPerPath.PathId
- )
- WHERE Paths.PathId = SharedDataPerPath.PathId OR Paths.PathId = UserDataPerPath.PathId
- ORDER BY Paths.Path ASC
- SELECT @TotalRecords = @@ROWCOUNT
- SELECT Paths.Path,
- SharedDataPerPath.LastUpdatedDate,
- SharedDataPerPath.SharedDataLength,
- UserDataPerPath.UserDataLength,
- UserDataPerPath.UserCount
- FROM dbo.aspnet_Paths Paths,
- ((SELECT PageIndex.ItemId AS PathId,
- AllUsers.LastUpdatedDate AS LastUpdatedDate,
- DATALENGTH(AllUsers.PageSettings) AS SharedDataLength
- FROM dbo.aspnet_PersonalizationAllUsers AllUsers, #PageIndex PageIndex
- WHERE AllUsers.PathId = PageIndex.ItemId
- AND PageIndex.IndexId >= @PageLowerBound AND PageIndex.IndexId <= @PageUpperBound
- ) AS SharedDataPerPath
- FULL OUTER JOIN
- (SELECT PageIndex.ItemId AS PathId,
- SUM(DATALENGTH(PerUser.PageSettings)) AS UserDataLength,
- COUNT(*) AS UserCount
- FROM aspnet_PersonalizationPerUser PerUser, #PageIndex PageIndex
- WHERE PerUser.PathId = PageIndex.ItemId
- AND PageIndex.IndexId >= @PageLowerBound AND PageIndex.IndexId <= @PageUpperBound
- GROUP BY PageIndex.ItemId
- ) AS UserDataPerPath
- ON SharedDataPerPath.PathId = UserDataPerPath.PathId
- )
- WHERE Paths.PathId = SharedDataPerPath.PathId OR Paths.PathId = UserDataPerPath.PathId
- ORDER BY Paths.Path ASC
- END
- ELSE
- BEGIN
- -- Insert into our temp table
- INSERT INTO #PageIndex (ItemId)
- SELECT PerUser.Id
- FROM dbo.aspnet_PersonalizationPerUser PerUser, dbo.aspnet_Users Users, dbo.aspnet_Paths Paths
- WHERE Paths.ApplicationId = @ApplicationId
- AND PerUser.UserId = Users.UserId
- AND PerUser.PathId = Paths.PathId
- AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path))
- AND (@UserName IS NULL OR Users.LoweredUserName LIKE LOWER(@UserName))
- AND (@InactiveSinceDate IS NULL OR Users.LastActivityDate <= @InactiveSinceDate)
- ORDER BY Paths.Path ASC, Users.UserName ASC
- SELECT @TotalRecords = @@ROWCOUNT
- SELECT Paths.Path, PerUser.LastUpdatedDate, DATALENGTH(PerUser.PageSettings), Users.UserName, Users.LastActivityDate
- FROM dbo.aspnet_PersonalizationPerUser PerUser, dbo.aspnet_Users Users, dbo.aspnet_Paths Paths, #PageIndex PageIndex
- WHERE PerUser.Id = PageIndex.ItemId
- AND PerUser.UserId = Users.UserId
- AND PerUser.PathId = Paths.PathId
- AND PageIndex.IndexId >= @PageLowerBound AND PageIndex.IndexId <= @PageUpperBound
- ORDER BY Paths.Path ASC, Users.UserName ASC
- END
- RETURN @TotalRecords
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_PersonalizationAdministration_GetCountOfState] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- CREATE PROCEDURE [dbo].[aspnet_PersonalizationAdministration_GetCountOfState] (
- @Count int OUT,
- @AllUsersScope bit,
- @ApplicationName NVARCHAR(256),
- @Path NVARCHAR(256) = NULL,
- @UserName NVARCHAR(256) = NULL,
- @InactiveSinceDate DATETIME = NULL)
- AS
- BEGIN
- DECLARE @ApplicationId UNIQUEIDENTIFIER
- EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
- IF (@ApplicationId IS NULL)
- SELECT @Count = 0
- ELSE
- IF (@AllUsersScope = 1)
- SELECT @Count = COUNT(*)
- FROM dbo.aspnet_PersonalizationAllUsers AllUsers, dbo.aspnet_Paths Paths
- WHERE Paths.ApplicationId = @ApplicationId
- AND AllUsers.PathId = Paths.PathId
- AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path))
- ELSE
- SELECT @Count = COUNT(*)
- FROM dbo.aspnet_PersonalizationPerUser PerUser, dbo.aspnet_Users Users, dbo.aspnet_Paths Paths
- WHERE Paths.ApplicationId = @ApplicationId
- AND PerUser.UserId = Users.UserId
- AND PerUser.PathId = Paths.PathId
- AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path))
- AND (@UserName IS NULL OR Users.LoweredUserName LIKE LOWER(@UserName))
- AND (@InactiveSinceDate IS NULL OR Users.LastActivityDate <= @InactiveSinceDate)
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_PersonalizationAdministration_ResetSharedState] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- CREATE PROCEDURE [dbo].[aspnet_PersonalizationAdministration_ResetSharedState] (
- @Count int OUT,
- @ApplicationName NVARCHAR(256),
- @Path NVARCHAR(256))
- AS
- BEGIN
- DECLARE @ApplicationId UNIQUEIDENTIFIER
- EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
- IF (@ApplicationId IS NULL)
- SELECT @Count = 0
- ELSE
- BEGIN
- DELETE FROM dbo.aspnet_PersonalizationAllUsers
- WHERE PathId IN
- (SELECT AllUsers.PathId
- FROM dbo.aspnet_PersonalizationAllUsers AllUsers, dbo.aspnet_Paths Paths
- WHERE Paths.ApplicationId = @ApplicationId
- AND AllUsers.PathId = Paths.PathId
- AND Paths.LoweredPath = LOWER(@Path))
- SELECT @Count = @@ROWCOUNT
- END
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_PersonalizationAdministration_ResetUserState] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- CREATE PROCEDURE [dbo].[aspnet_PersonalizationAdministration_ResetUserState] (
- @Count int OUT,
- @ApplicationName NVARCHAR(256),
- @InactiveSinceDate DATETIME = NULL,
- @UserName NVARCHAR(256) = NULL,
- @Path NVARCHAR(256) = NULL)
- AS
- BEGIN
- DECLARE @ApplicationId UNIQUEIDENTIFIER
- EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
- IF (@ApplicationId IS NULL)
- SELECT @Count = 0
- ELSE
- BEGIN
- DELETE FROM dbo.aspnet_PersonalizationPerUser
- WHERE Id IN (SELECT PerUser.Id
- FROM dbo.aspnet_PersonalizationPerUser PerUser, dbo.aspnet_Users Users, dbo.aspnet_Paths Paths
- WHERE Paths.ApplicationId = @ApplicationId
- AND PerUser.UserId = Users.UserId
- AND PerUser.PathId = Paths.PathId
- AND (@InactiveSinceDate IS NULL OR Users.LastActivityDate <= @InactiveSinceDate)
- AND (@UserName IS NULL OR Users.LoweredUserName = LOWER(@UserName))
- AND (@Path IS NULL OR Paths.LoweredPath = LOWER(@Path)))
- SELECT @Count = @@ROWCOUNT
- END
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_PersonalizationAllUsers_GetPageSettings] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- CREATE PROCEDURE [dbo].[aspnet_PersonalizationAllUsers_GetPageSettings] (
- @ApplicationName NVARCHAR(256),
- @Path NVARCHAR(256))
- AS
- BEGIN
- DECLARE @ApplicationId UNIQUEIDENTIFIER
- DECLARE @PathId UNIQUEIDENTIFIER
- SELECT @ApplicationId = NULL
- SELECT @PathId = NULL
- EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
- IF (@ApplicationId IS NULL)
- BEGIN
- RETURN
- END
- SELECT @PathId = u.PathId FROM dbo.aspnet_Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
- IF (@PathId IS NULL)
- BEGIN
- RETURN
- END
- SELECT p.PageSettings FROM dbo.aspnet_PersonalizationAllUsers p WHERE p.PathId = @PathId
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_PersonalizationAllUsers_ResetPageSettings] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- CREATE PROCEDURE [dbo].[aspnet_PersonalizationAllUsers_ResetPageSettings] (
- @ApplicationName NVARCHAR(256),
- @Path NVARCHAR(256))
- AS
- BEGIN
- DECLARE @ApplicationId UNIQUEIDENTIFIER
- DECLARE @PathId UNIQUEIDENTIFIER
- SELECT @ApplicationId = NULL
- SELECT @PathId = NULL
- EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
- IF (@ApplicationId IS NULL)
- BEGIN
- RETURN
- END
- SELECT @PathId = u.PathId FROM dbo.aspnet_Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
- IF (@PathId IS NULL)
- BEGIN
- RETURN
- END
- DELETE FROM dbo.aspnet_PersonalizationAllUsers WHERE PathId = @PathId
- RETURN 0
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_PersonalizationAllUsers_SetPageSettings] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- CREATE PROCEDURE [dbo].[aspnet_PersonalizationAllUsers_SetPageSettings] (
- @ApplicationName NVARCHAR(256),
- @Path NVARCHAR(256),
- @PageSettings IMAGE,
- @CurrentTimeUtc DATETIME)
- AS
- BEGIN
- DECLARE @ApplicationId UNIQUEIDENTIFIER
- DECLARE @PathId UNIQUEIDENTIFIER
- SELECT @ApplicationId = NULL
- SELECT @PathId = NULL
- EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT
- SELECT @PathId = u.PathId FROM dbo.aspnet_Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
- IF (@PathId IS NULL)
- BEGIN
- EXEC dbo.aspnet_Paths_CreatePath @ApplicationId, @Path, @PathId OUTPUT
- END
- IF (EXISTS(SELECT PathId FROM dbo.aspnet_PersonalizationAllUsers WHERE PathId = @PathId))
- UPDATE dbo.aspnet_PersonalizationAllUsers SET PageSettings = @PageSettings, LastUpdatedDate = @CurrentTimeUtc WHERE PathId = @PathId
- ELSE
- INSERT INTO dbo.aspnet_PersonalizationAllUsers(PathId, PageSettings, LastUpdatedDate) VALUES (@PathId, @PageSettings, @CurrentTimeUtc)
- RETURN 0
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_PersonalizationPerUser_GetPageSettings] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- CREATE PROCEDURE [dbo].[aspnet_PersonalizationPerUser_GetPageSettings] (
- @ApplicationName NVARCHAR(256),
- @UserName NVARCHAR(256),
- @Path NVARCHAR(256),
- @CurrentTimeUtc DATETIME)
- AS
- BEGIN
- DECLARE @ApplicationId UNIQUEIDENTIFIER
- DECLARE @PathId UNIQUEIDENTIFIER
- DECLARE @UserId UNIQUEIDENTIFIER
- SELECT @ApplicationId = NULL
- SELECT @PathId = NULL
- SELECT @UserId = NULL
- EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
- IF (@ApplicationId IS NULL)
- BEGIN
- RETURN
- END
- SELECT @PathId = u.PathId FROM dbo.aspnet_Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
- IF (@PathId IS NULL)
- BEGIN
- RETURN
- END
- SELECT @UserId = u.UserId FROM dbo.aspnet_Users u WHERE u.ApplicationId = @ApplicationId AND u.LoweredUserName = LOWER(@UserName)
- IF (@UserId IS NULL)
- BEGIN
- RETURN
- END
- UPDATE dbo.aspnet_Users WITH (ROWLOCK)
- SET LastActivityDate = @CurrentTimeUtc
- WHERE UserId = @UserId
- IF (@@ROWCOUNT = 0) -- Username not found
- RETURN
- SELECT p.PageSettings FROM dbo.aspnet_PersonalizationPerUser p WHERE p.PathId = @PathId AND p.UserId = @UserId
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_PersonalizationPerUser_ResetPageSettings] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- CREATE PROCEDURE [dbo].[aspnet_PersonalizationPerUser_ResetPageSettings] (
- @ApplicationName NVARCHAR(256),
- @UserName NVARCHAR(256),
- @Path NVARCHAR(256),
- @CurrentTimeUtc DATETIME)
- AS
- BEGIN
- DECLARE @ApplicationId UNIQUEIDENTIFIER
- DECLARE @PathId UNIQUEIDENTIFIER
- DECLARE @UserId UNIQUEIDENTIFIER
- SELECT @ApplicationId = NULL
- SELECT @PathId = NULL
- SELECT @UserId = NULL
- EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
- IF (@ApplicationId IS NULL)
- BEGIN
- RETURN
- END
- SELECT @PathId = u.PathId FROM dbo.aspnet_Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
- IF (@PathId IS NULL)
- BEGIN
- RETURN
- END
- SELECT @UserId = u.UserId FROM dbo.aspnet_Users u WHERE u.ApplicationId = @ApplicationId AND u.LoweredUserName = LOWER(@UserName)
- IF (@UserId IS NULL)
- BEGIN
- RETURN
- END
- UPDATE dbo.aspnet_Users WITH (ROWLOCK)
- SET LastActivityDate = @CurrentTimeUtc
- WHERE UserId = @UserId
- IF (@@ROWCOUNT = 0) -- Username not found
- RETURN
- DELETE FROM dbo.aspnet_PersonalizationPerUser WHERE PathId = @PathId AND UserId = @UserId
- RETURN 0
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_PersonalizationPerUser_SetPageSettings] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- CREATE PROCEDURE [dbo].[aspnet_PersonalizationPerUser_SetPageSettings] (
- @ApplicationName NVARCHAR(256),
- @UserName NVARCHAR(256),
- @Path NVARCHAR(256),
- @PageSettings IMAGE,
- @CurrentTimeUtc DATETIME)
- AS
- BEGIN
- DECLARE @ApplicationId UNIQUEIDENTIFIER
- DECLARE @PathId UNIQUEIDENTIFIER
- DECLARE @UserId UNIQUEIDENTIFIER
- SELECT @ApplicationId = NULL
- SELECT @PathId = NULL
- SELECT @UserId = NULL
- EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT
- SELECT @PathId = u.PathId FROM dbo.aspnet_Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
- IF (@PathId IS NULL)
- BEGIN
- EXEC dbo.aspnet_Paths_CreatePath @ApplicationId, @Path, @PathId OUTPUT
- END
- SELECT @UserId = u.UserId FROM dbo.aspnet_Users u WHERE u.ApplicationId = @ApplicationId AND u.LoweredUserName = LOWER(@UserName)
- IF (@UserId IS NULL)
- BEGIN
- EXEC dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, 0, @CurrentTimeUtc, @UserId OUTPUT
- END
- UPDATE dbo.aspnet_Users WITH (ROWLOCK)
- SET LastActivityDate = @CurrentTimeUtc
- WHERE UserId = @UserId
- IF (@@ROWCOUNT = 0) -- Username not found
- RETURN
- IF (EXISTS(SELECT PathId FROM dbo.aspnet_PersonalizationPerUser WHERE UserId = @UserId AND PathId = @PathId))
- UPDATE dbo.aspnet_PersonalizationPerUser SET PageSettings = @PageSettings, LastUpdatedDate = @CurrentTimeUtc WHERE UserId = @UserId AND PathId = @PathId
- ELSE
- INSERT INTO dbo.aspnet_PersonalizationPerUser(UserId, PathId, PageSettings, LastUpdatedDate) VALUES (@UserId, @PathId, @PageSettings, @CurrentTimeUtc)
- RETURN 0
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_Profile_DeleteInactiveProfiles] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- CREATE PROCEDURE [dbo].[aspnet_Profile_DeleteInactiveProfiles]
- @ApplicationName nvarchar(256),
- @ProfileAuthOptions int,
- @InactiveSinceDate datetime
- AS
- BEGIN
- DECLARE @ApplicationId uniqueidentifier
- SELECT @ApplicationId = NULL
- SELECT @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
- IF (@ApplicationId IS NULL)
- BEGIN
- SELECT 0
- RETURN
- END
- DELETE
- FROM dbo.aspnet_Profile
- WHERE UserId IN
- ( SELECT UserId
- FROM dbo.aspnet_Users u
- WHERE ApplicationId = @ApplicationId
- AND (LastActivityDate <= @InactiveSinceDate)
- AND (
- (@ProfileAuthOptions = 2)
- OR (@ProfileAuthOptions = 0 AND IsAnonymous = 1)
- OR (@ProfileAuthOptions = 1 AND IsAnonymous = 0)
- )
- )
- SELECT @@ROWCOUNT
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_Profile_DeleteProfiles] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- CREATE PROCEDURE [dbo].[aspnet_Profile_DeleteProfiles]
- @ApplicationName nvarchar(256),
- @UserNames nvarchar(4000)
- AS
- BEGIN
- DECLARE @UserName nvarchar(256)
- DECLARE @CurrentPos int
- DECLARE @NextPos int
- DECLARE @NumDeleted int
- DECLARE @DeletedUser int
- DECLARE @TranStarted bit
- DECLARE @ErrorCode int
- SET @ErrorCode = 0
- SET @CurrentPos = 1
- SET @NumDeleted = 0
- SET @TranStarted = 0
- IF( @@TRANCOUNT = 0 )
- BEGIN
- BEGIN TRANSACTION
- SET @TranStarted = 1
- END
- ELSE
- SET @TranStarted = 0
- WHILE (@CurrentPos <= LEN(@UserNames))
- BEGIN
- SELECT @NextPos = CHARINDEX(N',', @UserNames, @CurrentPos)
- IF (@NextPos = 0 OR @NextPos IS NULL)
- SELECT @NextPos = LEN(@UserNames) + 1
- SELECT @UserName = SUBSTRING(@UserNames, @CurrentPos, @NextPos - @CurrentPos)
- SELECT @CurrentPos = @NextPos+1
- IF (LEN(@UserName) > 0)
- BEGIN
- SELECT @DeletedUser = 0
- EXEC dbo.aspnet_Users_DeleteUser @ApplicationName, @UserName, 4, @DeletedUser OUTPUT
- IF( @@ERROR <> 0 )
- BEGIN
- SET @ErrorCode = -1
- GOTO Cleanup
- END
- IF (@DeletedUser <> 0)
- SELECT @NumDeleted = @NumDeleted + 1
- END
- END
- SELECT @NumDeleted
- IF (@TranStarted = 1)
- BEGIN
- SET @TranStarted = 0
- COMMIT TRANSACTION
- END
- SET @TranStarted = 0
- RETURN 0
- Cleanup:
- IF (@TranStarted = 1 )
- BEGIN
- SET @TranStarted = 0
- ROLLBACK TRANSACTION
- END
- RETURN @ErrorCode
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_Profile_GetNumberOfInactiveProfiles] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- CREATE PROCEDURE [dbo].[aspnet_Profile_GetNumberOfInactiveProfiles]
- @ApplicationName nvarchar(256),
- @ProfileAuthOptions int,
- @InactiveSinceDate datetime
- AS
- BEGIN
- DECLARE @ApplicationId uniqueidentifier
- SELECT @ApplicationId = NULL
- SELECT @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
- IF (@ApplicationId IS NULL)
- BEGIN
- SELECT 0
- RETURN
- END
- SELECT COUNT(*)
- FROM dbo.aspnet_Users u, dbo.aspnet_Profile p
- WHERE ApplicationId = @ApplicationId
- AND u.UserId = p.UserId
- AND (LastActivityDate <= @InactiveSinceDate)
- AND (
- (@ProfileAuthOptions = 2)
- OR (@ProfileAuthOptions = 0 AND IsAnonymous = 1)
- OR (@ProfileAuthOptions = 1 AND IsAnonymous = 0)
- )
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_Profile_GetProfiles] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- CREATE PROCEDURE [dbo].[aspnet_Profile_GetProfiles]
- @ApplicationName nvarchar(256),
- @ProfileAuthOptions int,
- @PageIndex int,
- @PageSize int,
- @UserNameToMatch nvarchar(256) = NULL,
- @InactiveSinceDate datetime = NULL
- AS
- BEGIN
- DECLARE @ApplicationId uniqueidentifier
- SELECT @ApplicationId = NULL
- SELECT @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
- IF (@ApplicationId IS NULL)
- RETURN
- -- 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_Profile p
- WHERE ApplicationId = @ApplicationId
- AND u.UserId = p.UserId
- AND (@InactiveSinceDate IS NULL OR LastActivityDate <= @InactiveSinceDate)
- AND ( (@ProfileAuthOptions = 2)
- OR (@ProfileAuthOptions = 0 AND IsAnonymous = 1)
- OR (@ProfileAuthOptions = 1 AND IsAnonymous = 0)
- )
- AND (@UserNameToMatch IS NULL OR LoweredUserName LIKE LOWER(@UserNameToMatch))
- ORDER BY UserName
- SELECT u.UserName, u.IsAnonymous, u.LastActivityDate, p.LastUpdatedDate,
- DATALENGTH(p.PropertyNames) + DATALENGTH(p.PropertyValuesString) + DATALENGTH(p.PropertyValuesBinary)
- FROM dbo.aspnet_Users u, dbo.aspnet_Profile p, #PageIndexForUsers i
- WHERE u.UserId = p.UserId AND p.UserId = i.UserId AND i.IndexId >= @PageLowerBound AND i.IndexId <= @PageUpperBound
- SELECT COUNT(*)
- FROM #PageIndexForUsers
- DROP TABLE #PageIndexForUsers
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_Profile_GetProperties] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- CREATE PROCEDURE [dbo].[aspnet_Profile_GetProperties]
- @ApplicationName nvarchar(256),
- @UserName nvarchar(256),
- @CurrentTimeUtc datetime
- AS
- BEGIN
- DECLARE @ApplicationId uniqueidentifier
- SELECT @ApplicationId = NULL
- SELECT @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
- IF (@ApplicationId IS NULL)
- RETURN
- DECLARE @UserId uniqueidentifier
- SELECT @UserId = NULL
- SELECT @UserId = UserId
- FROM dbo.aspnet_Users
- WHERE ApplicationId = @ApplicationId AND LoweredUserName = LOWER(@UserName)
- IF (@UserId IS NULL)
- RETURN
- SELECT TOP 1 PropertyNames, PropertyValuesString, PropertyValuesBinary
- FROM dbo.aspnet_Profile
- WHERE UserId = @UserId
- IF (@@ROWCOUNT > 0)
- BEGIN
- UPDATE dbo.aspnet_Users
- SET LastActivityDate=@CurrentTimeUtc
- WHERE UserId = @UserId
- END
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_Profile_SetProperties] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- CREATE PROCEDURE [dbo].[aspnet_Profile_SetProperties]
- @ApplicationName nvarchar(256),
- @PropertyNames ntext,
- @PropertyValuesString ntext,
- @PropertyValuesBinary image,
- @UserName nvarchar(256),
- @IsUserAnonymous bit,
- @CurrentTimeUtc datetime
- 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
- DECLARE @UserId uniqueidentifier
- DECLARE @LastActivityDate datetime
- SELECT @UserId = NULL
- SELECT @LastActivityDate = @CurrentTimeUtc
- SELECT @UserId = UserId
- FROM dbo.aspnet_Users
- WHERE ApplicationId = @ApplicationId AND LoweredUserName = LOWER(@UserName)
- IF (@UserId IS NULL)
- EXEC dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, @IsUserAnonymous, @LastActivityDate, @UserId OUTPUT
- IF( @@ERROR <> 0 )
- BEGIN
- SET @ErrorCode = -1
- GOTO Cleanup
- END
- UPDATE dbo.aspnet_Users
- SET LastActivityDate=@CurrentTimeUtc
- WHERE UserId = @UserId
- IF( @@ERROR <> 0 )
- BEGIN
- SET @ErrorCode = -1
- GOTO Cleanup
- END
- IF (EXISTS( SELECT *
- FROM dbo.aspnet_Profile
- WHERE UserId = @UserId))
- UPDATE dbo.aspnet_Profile
- SET PropertyNames=@PropertyNames, PropertyValuesString = @PropertyValuesString,
- PropertyValuesBinary = @PropertyValuesBinary, LastUpdatedDate=@CurrentTimeUtc
- WHERE UserId = @UserId
- ELSE
- INSERT INTO dbo.aspnet_Profile(UserId, PropertyNames, PropertyValuesString, PropertyValuesBinary, LastUpdatedDate)
- VALUES (@UserId, @PropertyNames, @PropertyValuesString, @PropertyValuesBinary, @CurrentTimeUtc)
- 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
- /****** Object: StoredProcedure [dbo].[aspnet_RegisterSchemaVersion] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- 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
- /****** Object: StoredProcedure [dbo].[aspnet_Roles_CreateRole] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- 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
- /****** Object: StoredProcedure [dbo].[aspnet_Roles_DeleteRole] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- 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
- /****** Object: StoredProcedure [dbo].[aspnet_Roles_GetAllRoles] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- 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
- /****** Object: StoredProcedure [dbo].[aspnet_Roles_RoleExists] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- 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
- /****** Object: StoredProcedure [dbo].[aspnet_Setup_RemoveAllRoleMembers] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- 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
- /****** Object: StoredProcedure [dbo].[aspnet_Setup_RestorePermissions] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- CREATE PROCEDURE [dbo].[aspnet_Setup_RestorePermissions]
- @name sysname
- AS
- BEGIN
- DECLARE @object sysname
- DECLARE @protectType char(10)
- DECLARE @action varchar(60)
- 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
- /****** Object: StoredProcedure [dbo].[aspnet_UnRegisterSchemaVersion] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- 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
- /****** Object: StoredProcedure [dbo].[aspnet_Users_CreateUser] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- 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
- /****** Object: StoredProcedure [dbo].[aspnet_Users_DeleteUser] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- 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
- /****** Object: StoredProcedure [dbo].[aspnet_UsersInRoles_AddUsersToRoles] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- 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
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_UsersInRoles_FindUsersInRole] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- 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
- /****** Object: StoredProcedure [dbo].[aspnet_UsersInRoles_GetRolesForUser] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- 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
- /****** Object: StoredProcedure [dbo].[aspnet_UsersInRoles_GetUsersInRoles] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- 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
- /****** Object: StoredProcedure [dbo].[aspnet_UsersInRoles_IsUserInRole] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- 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
- /****** Object: StoredProcedure [dbo].[aspnet_UsersInRoles_RemoveUsersFromRoles] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- 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
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_WebEvent_LogEvent] Script Date: 16.2.2017. 14:59:48 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- CREATE PROCEDURE [dbo].[aspnet_WebEvent_LogEvent]
- @EventId char(32),
- @EventTimeUtc datetime,
- @EventTime datetime,
- @EventType nvarchar(256),
- @EventSequence decimal(19,0),
- @EventOccurrence decimal(19,0),
- @EventCode int,
- @EventDetailCode int,
- @Message nvarchar(1024),
- @ApplicationPath nvarchar(256),
- @ApplicationVirtualPath nvarchar(256),
- @MachineName nvarchar(256),
- @RequestUrl nvarchar(1024),
- @ExceptionType nvarchar(256),
- @Details ntext
- AS
- BEGIN
- INSERT
- dbo.aspnet_WebEvent_Events
- (
- EventId,
- EventTimeUtc,
- EventTime,
- EventType,
- EventSequence,
- EventOccurrence,
- EventCode,
- EventDetailCode,
- Message,
- ApplicationPath,
- ApplicationVirtualPath,
- MachineName,
- RequestUrl,
- ExceptionType,
- Details
- )
- VALUES
- (
- @EventId,
- @EventTimeUtc,
- @EventTime,
- @EventType,
- @EventSequence,
- @EventOccurrence,
- @EventCode,
- @EventDetailCode,
- @Message,
- @ApplicationPath,
- @ApplicationVirtualPath,
- @MachineName,
- @RequestUrl,
- @ExceptionType,
- @Details
- )
- END
- GO
- USE [master]
- GO
- ALTER DATABASE [UTO2015] SET READ_WRITE
- GO
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement