Database Experiment 2

1. DBMS login account Management Create login sqluser with PASSWORD = ' pswdforuser_1 ' Create login SQLUSER2 with PASSWORD = ' pswdforuser_123 ' C reate login Uselessuser with PASSWORD = ' useless_123 ' ALTER login sqluser with NAME = SQLUSER1 ALTER login SQLUSER2 with PASSWORD = ' pswdforuser_2 ' DROP LOGIN uselessuser//2. Database user Management use Mobilephone create user SQLUSER1 with default_schema=dbo create user SQLUSER2 DROP user SQLUSER2 Create user Db_user for LOGIN SQLUSER2 with default_schema=dbo//3. Permissions/Reclaim permissions for database users use Mobilephone GRANT Select, UPDATE (CName) on company to SQLUSER1 with Grant OPTION GRANT select on Phon E to publicly grant all privileges on OperatingSystem to Db_user Grant ALTER on SCHEMA::D Bo to SQLUSER1 Grant CREATE VIEW , CREATE PROCEDURE, BACKUP DATABASE to SQLUSER1, Db_user REVOKE inserts, DELETE on OperatingSystem from Db_user REVOKE CR Eate TABLE from SQLUSER1, Db_user DENY CREATE VIEW to Db_user//4. Create roles, assign users and permissions to roles create role Updator AUTHORIZATION SQLUSER1 Create role DeletER AUTHORIZATION db_user EXEC sp_addrolemember ' deleter ', ' SQLUSER1 ' CREATE ROLE theotherrole AUTHORIZATION SQLUSER1 GR ANT UPDATE on phone to updator GRANT DELETE on phone to deleter EXEC sp_addrolemember ' updator ', Db_user DROP ROLE Theoth Errole//1. Creation, execution, and management of stored procedures create PROC phonecount @osName char, @cnt int output as SELECT @cnt = COUNT (phonename) F ROM Phone WHERE OS = @osName DECLARE @cnt int EXEC phonecount ' IOS ', @cnt output PRINT @cnt ALTER PROCEDURE Phonecou NT @introduced_year int, @cnt int output as SELECT @cnt = COUNT (phonename) from Phone WHERE introduced Year = @introduced_year drop PROCEDURE phonecount DECLARE @cnt int EXEC phonecount, @cnt output PRINT @cnt DROP PRO C Phonecount//2. Creation and management of triggers create TRIGGER tri_newphone on Phone INSTEAD of the INSERT as IF not EXISTS (SELECT * from INSERTED WHERE in Troducedyear > year (GETDATE ())) insert INTO phone SELECT * from INSERTED ELSE PRINT ' must not be plugged into the unlisted mobile phone ' DROP TriggER tri_newphone INSERT into Phone VALUES (' Newphone ', ' Apple ', ' IOS ', ' Fdd-lte ', 6.3, $) SELECT * from phone CREATE TR Igger tri_notphone on phone after INSERT as IF EXISTS (SELECT * from Phone WHERE displaysize not between 1.0 and 6 .2) ROLLBACK DROP TRIGGER tri_notphone//1. Complete, differential, log backup of the created database EXEC sp_addumpdevice ' disk ', ' Mobilephonebak ', ' C:\Program Files\Microsoft SQL Server\mssql10_50. Mssqlserver\mssql\backup\mp.bak ' Backup database mobilephone to Mobilephonebak with init Backup database mobilephone to M Obilephonebak with differential BACKUP LOG mobilephone to disk= ' C:\Program Files\Microsoft SQL Server\mssql10_50.mssqlse Rver\mssql\backup\mplog.dat ' use master Backup LOG mobilephone to disk= ' C:\Program Files\Microsoft SQL Server\mssql10_ 50.mssqlserver\mssql\backup\mplog.dat ' with NORECOVERY//2. Database recovery BACKUP LOG mobilephone to disk= ' C:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\backup\ MPlog.dat ' with NORECOVERY RESTORE DATABASE mobilephone FROM Mobilephonebak with file=1 BACKUP LOG mobilephone to disk= ' C:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserve R\mssql\backup\mplog2.dat ' with NORECOVERY RESTORE DATABASE mobilephone from Mobilephonebak with file=1, NORECOVERY RESTO RE DATABASE mobilephone from Mobilephonebak with file=2