· 8 years ago · Feb 10, 2018, 03:50 PM
1USE [Curso2Net2]
2GO
3/****** Object: Schema [SEG] Script Date: 10/02/2018 09:49:06 a. m. ******/
4CREATE SCHEMA [SEG]
5GO
6/****** Object: Table [SEG].[Roles] Script Date: 10/02/2018 09:49:06 a. m. ******/
7SET ANSI_NULLS ON
8GO
9SET QUOTED_IDENTIFIER ON
10GO
11CREATE TABLE [SEG].[Roles](
12 [IdRol] [uniqueidentifier] NOT NULL,
13 [RolNombre] [varchar](40) NULL,
14 CONSTRAINT [PK_SEGRoles] PRIMARY KEY CLUSTERED
15(
16 [IdRol] ASC
17)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
18) ON [PRIMARY]
19GO
20/****** Object: Table [SEG].[Usuarios] Script Date: 10/02/2018 09:49:06 a. m. ******/
21SET ANSI_NULLS ON
22GO
23SET QUOTED_IDENTIFIER ON
24GO
25CREATE TABLE [SEG].[Usuarios](
26 [IdUsuario] [uniqueidentifier] NOT NULL,
27 [Usuario] [varchar](40) NOT NULL,
28 [Contrasenna] [varchar](100) NOT NULL,
29 [NombreUsuario] [varchar](100) NULL,
30 CONSTRAINT [PK_SEGUsuarios] PRIMARY KEY CLUSTERED
31(
32 [IdUsuario] ASC
33)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
34) ON [PRIMARY]
35GO
36/****** Object: Table [SEG].[UsuariosRoles] Script Date: 10/02/2018 09:49:06 a. m. ******/
37SET ANSI_NULLS ON
38GO
39SET QUOTED_IDENTIFIER ON
40GO
41CREATE TABLE [SEG].[UsuariosRoles](
42 [IdUsuarioRol] [uniqueidentifier] NOT NULL,
43 [Idusuario] [uniqueidentifier] NULL,
44 [IdRol] [uniqueidentifier] NULL,
45 CONSTRAINT [PK_UsuarioRol] PRIMARY KEY CLUSTERED
46(
47 [IdUsuarioRol] ASC
48)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
49) ON [PRIMARY]
50GO
51ALTER TABLE [SEG].[Roles] ADD DEFAULT (newsequentialid()) FOR [IdRol]
52GO
53ALTER TABLE [SEG].[Usuarios] ADD DEFAULT (newsequentialid()) FOR [IdUsuario]
54GO
55ALTER TABLE [SEG].[UsuariosRoles] ADD DEFAULT (newsequentialid()) FOR [IdUsuarioRol]
56GO
57ALTER TABLE [SEG].[UsuariosRoles] WITH CHECK ADD CONSTRAINT [FK_Rol] FOREIGN KEY([IdRol])
58REFERENCES [SEG].[Roles] ([IdRol])
59GO
60ALTER TABLE [SEG].[UsuariosRoles] CHECK CONSTRAINT [FK_Rol]
61GO
62ALTER TABLE [SEG].[UsuariosRoles] WITH CHECK ADD CONSTRAINT [FK_Usuario] FOREIGN KEY([Idusuario])
63REFERENCES [SEG].[Usuarios] ([IdUsuario])
64GO
65ALTER TABLE [SEG].[UsuariosRoles] CHECK CONSTRAINT [FK_Usuario]
66GO
67/****** Object: StoredProcedure [SEG].[ValidaLogin] Script Date: 10/02/2018 09:49:06 a. m. ******/
68SET ANSI_NULLS ON
69GO
70SET QUOTED_IDENTIFIER ON
71GO
72CREATE PROC [SEG].[ValidaLogin] @usuario VARCHAR(40),
73 @contrasenna VARCHAR(40)
74AS
75 BEGIN
76 IF EXISTS
77(
78 SELECT 1
79 FROM seg.usuarios
80 WHERE usuario = @usuario
81 AND contrasenna = @Contrasenna
82)
83 BEGIN
84 SELECT 1;
85 END;
86 ELSE
87 BEGIN
88 SELECT 0;
89 END;
90 END;
91GO