-- MARTA E. ZORRILLA PANTALEÓN
-- UNIVERSIDAD DE CANTABRIA
-- BASES DE DATOS ING. INFORMÁTICA
--------------------------------------
use hotel
go

-- b)	Crear un inicio de sesión con nombre “pepe” y contraseña “pepe1234”, base de datos por defecto “hotel” e idioma “Spanish”. No exigir cambio de contraseña en la siguiente conexión. No asignar ningún permiso. 

CREATE LOGIN pepe 
    WITH PASSWORD = 'pepe1234',
    default_database=hotel,
    default_language=spanish ;  

--c)	Conectarse con este usuario ¿qué puede hacer? ¿por qué?

-- si intenta conectarse se produce un error porque no es usuario de esa base de datos
-- hay que crearle un usuario de bases de datos asociado a este login

-- d)	Cree el usuario de base de datos “pepe” en la base de datos “hotel” asociado al inicio de sesión “pepe”. ¿Qué puede hacer? ¿se la ha asignado algún rol a “pepe”?

CREATE USER pepe FOR LOGIN pepe 
    WITH DEFAULT_SCHEMA = hotel;

--ahora ya podemos conectarnos pero nada más (ni consultar, ni actualizar,...)

--e)	Asignarle el rol de base de datos db_datareader para la base de datos “hotel”. Conectaros de nuevo con el usuario “pepe” ¿puede insertar datos en reservas? ¿y leerlos?


EXEC sp_addrolemember 'db_datareader', 'pepe'

--f)	Definir un rol de bases de datos “usuariosHotel” en la BD hotel. El propietario del rol será el esquema “dbo”. Otorgarle la posibilidad de manipular los datos de las tablas y ejecutar las funciones programadas.


CREATE ROLE [usuariosHotel] AUTHORIZATION [dbo]
GO

-- Con la siguiente consulta se crean los Grant necesarios utilizando el catálogo de la BD. Copiar y ejecutar

select 'GRANT delete, insert,update,select ON ' + name + ' TO [usuariosHotel];' from sysobjects where xtype='U'


GRANT delete, insert,update,select ON paises TO [usuariosHotel];
GRANT delete, insert,update,select ON clientes TO [usuariosHotel];
GRANT delete, insert,update,select ON tipo_habitacion TO [usuariosHotel];
GRANT delete, insert,update,select ON habitaciones TO [usuariosHotel];
GRANT delete, insert,update,select ON servicios TO [usuariosHotel];
GRANT delete, insert,update,select ON temporada TO [usuariosHotel];
GRANT delete, insert,update,select ON precio_habitacion TO [usuariosHotel];
GRANT delete, insert,update,select ON reserva_habitac TO [usuariosHotel];
GRANT delete, insert,update,select ON gastos TO [usuariosHotel];
GRANT delete, insert,update,select ON tipo_servicio TO [usuariosHotel];


select 'GRANT execute ON ' + name + ' TO [usuariosHotel];' from sysobjects where xtype='fn'


GRANT execute ON gastoReserva TO [usuariosHotel];
GRANT execute ON habReservada TO [usuariosHotel];


select 'GRANT select ON ' + name + ' TO [usuariosHotel];' from sysobjects where xtype='if'

GRANT select ON Ingresos TO [usuariosHotel];


--g)	Asignar al usuario “pepe” el rol (función de seguridad) “usuariosHotel”. Comprobar qué se le permite hacer ahora. 

EXEC sp_addrolemember 'usuariosHotel', 'pepe'

--h)	Crear el usuario “juan” y asignarle el rol “db_datawriter” y “db_datareader”. ¿Puede ejecutar funciones?

CREATE LOGIN juan 
    WITH PASSWORD = 'juan1234',
    default_database=hotel,
    default_language=spanish ;  
    
CREATE USER juan FOR LOGIN juan 
    WITH DEFAULT_SCHEMA = hotel;
    
EXEC sp_addrolemember 'db_datareader', 'juan';
EXEC sp_addrolemember 'db_datawriter', 'juan';

-- no puede ejecutar funciones escalares pero aquellas que devuelven tablas

--i)	Modificar el rol “usuariosHotel” dejándole únicamente la posibilidad de gestionar las reservas y clientes, y consultar el resto de información.

REVOKE delete, insert,update ON paises TO [usuariosHotel];
REVOKE delete, insert,update  ON tipo_habitacion TO [usuariosHotel];
REVOKE delete, insert,update ON habitaciones TO [usuariosHotel];
REVOKE delete, insert,update  ON servicios TO [usuariosHotel];
REVOKE delete, insert,update  ON temporada TO [usuariosHotel];
REVOKE delete, insert,update  ON precio_habitacion TO [usuariosHotel];
REVOKE delete, insert,update  ON gastos TO [usuariosHotel];
REVOKE delete, insert,update  ON tipo_servicio TO [usuariosHotel];

--j)	Conectaros como “pepe” y tratad de insertar un nuevo servicio. ¿Podéis? Hacedlo con la cuenta de “administrador” o usuario con esquema "dbo".
--k)	Borrar el rol “usuariosHotel” ¿se puede?
exec sp_droprole  '[usuariosHotel]' 

--No se puede hay usuarios con este rol asignado. Primero se ha de eliminar las vinculaciones con el rol y a continuación borrar éste.

BACKUP DATABASE [hotel] 
TO  DISK = N'C:\Archivos de programa\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\hotel.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'hotel-Completa Base de datos Copia de seguridad', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

use hotel;
create table ppp (a int)
go

RESTORE DATABASE [hotel]
 FROM  DISK = N'C:\Archivos de programa\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\hotel.bak' 
 WITH  FILE = 1,  NOUNLOAD,  STATS = 10