· 9 years ago · Jan 20, 2017, 04:36 PM
1--Marta Lerka 195004
2
3
4--0
5CREATE DATABASE [Baza2017]
6 CONTAINMENT = NONE
7 ON PRIMARY
8( NAME = N'Baza2017', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Baza2017.mdf' , SIZE = 5120KB , FILEGROWTH = 1024KB )
9 LOG ON
10( NAME = N'Baza2017_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Baza2017_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
11GO
12ALTER DATABASE [Baza2017] SET COMPATIBILITY_LEVEL = 110
13GO
14ALTER DATABASE [Baza2017] SET ANSI_NULL_DEFAULT OFF
15GO
16ALTER DATABASE [Baza2017] SET ANSI_NULLS OFF
17GO
18ALTER DATABASE [Baza2017] SET ANSI_PADDING OFF
19GO
20ALTER DATABASE [Baza2017] SET ANSI_WARNINGS OFF
21GO
22ALTER DATABASE [Baza2017] SET ARITHABORT OFF
23GO
24ALTER DATABASE [Baza2017] SET AUTO_CLOSE OFF
25GO
26ALTER DATABASE [Baza2017] SET AUTO_CREATE_STATISTICS ON
27GO
28ALTER DATABASE [Baza2017] SET AUTO_SHRINK OFF
29GO
30ALTER DATABASE [Baza2017] SET AUTO_UPDATE_STATISTICS ON
31GO
32ALTER DATABASE [Baza2017] SET CURSOR_CLOSE_ON_COMMIT OFF
33GO
34ALTER DATABASE [Baza2017] SET CURSOR_DEFAULT GLOBAL
35GO
36ALTER DATABASE [Baza2017] SET CONCAT_NULL_YIELDS_NULL OFF
37GO
38ALTER DATABASE [Baza2017] SET NUMERIC_ROUNDABORT OFF
39GO
40ALTER DATABASE [Baza2017] SET QUOTED_IDENTIFIER OFF
41GO
42ALTER DATABASE [Baza2017] SET RECURSIVE_TRIGGERS OFF
43GO
44ALTER DATABASE [Baza2017] SET DISABLE_BROKER
45GO
46ALTER DATABASE [Baza2017] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
47GO
48ALTER DATABASE [Baza2017] SET DATE_CORRELATION_OPTIMIZATION OFF
49GO
50ALTER DATABASE [Baza2017] SET PARAMETERIZATION SIMPLE
51GO
52ALTER DATABASE [Baza2017] SET READ_COMMITTED_SNAPSHOT OFF
53GO
54ALTER DATABASE [Baza2017] SET READ_WRITE
55GO
56ALTER DATABASE [Baza2017] SET RECOVERY FULL
57GO
58ALTER DATABASE [Baza2017] SET MULTI_USER
59GO
60ALTER DATABASE [Baza2017] SET PAGE_VERIFY CHECKSUM
61GO
62ALTER DATABASE [Baza2017] SET TARGET_RECOVERY_TIME = 0 SECONDS
63GO
64USE [Baza2017]
65GO
66IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [Baza2017] MODIFY FILEGROUP [PRIMARY] DEFAULT
67GO
68
69--1
70USE [master]
71GO
72CREATE LOGIN [USER2017] WITH PASSWORD=N'12345', DEFAULT_DATABASE=[Northwind], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
73GO
74USE [Baza2017]
75GO
76CREATE USER [USER2017] FOR LOGIN [USER2017]
77GO
78USE [Baza2017]
79GO
80ALTER ROLE [db_backupoperator] ADD MEMBER [USER2017]
81GO
82USE [Baza2017]
83GO
84ALTER ROLE [db_datareader] ADD MEMBER [USER2017]
85GO
86
87
88--2
89
90USE [master]
91GO
92EXEC master.dbo.sp_addumpdevice @devtype = N'disk', @logicalname = N'DANE2017', @physicalname = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\DANE2017.bak'
93GO
94
95BACKUP DATABASE [Baza2017] TO [DANE2017] WITH NOFORMAT, INIT, NAME = N'full', SKIP, NOREWIND, NOUNLOAD, STATS = 10
96GO
97
98
99BACKUP LOG [Baza2017] TO [DANE2017] WITH NOFORMAT, NOINIT, NAME = N'Log1', SKIP, NOREWIND, NOUNLOAD, STATS = 10
100GO
101
102BACKUP DATABASE [Baza2017] TO [DANE2017] WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'Dif2', SKIP, NOREWIND, NOUNLOAD, STATS = 10
103GO
104
105BACKUP LOG [Baza2017] TO [DANE2017] WITH NOFORMAT, NOINIT, NAME = N'Log2', SKIP, NOREWIND, NOUNLOAD, STATS = 10
106GO
107
108BACKUP DATABASE [Baza2017] TO[DANE2017] WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'Dif2', SKIP, NOREWIND, NOUNLOAD, STATS = 10
109GO
110
111
112BACKUP LOG [Baza2017] TO [DANE2017] WITH NOFORMAT, NOINIT, NAME = N'Log3', SKIP, NOREWIND, NOUNLOAD, STATS = 10
113GO
114
115
116
117--3
118
119USE [master]
120RESTORE DATABASE [BAZA_KOPIA] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\Baza2017.bak' WITH FILE = 1, MOVE N'Baza2017' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BAZA_KOPIA.mdf', MOVE N'Baza2017_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BAZA_KOPIA_log.ldf', NORECOVERY, NOUNLOAD, STATS = 5
121RESTORE DATABASE [BAZA_KOPIA] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\Baza2017.bak' WITH FILE = 4, NORECOVERY, NOUNLOAD, STATS = 5
122RESTORE LOG [BAZA_KOPIA] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\Baza2017.bak' WITH FILE = 5, NOUNLOAD, STATS = 5
123
124GO
125
126
127--4
128grant create proc to USER2017;
129GO
130
131
132create table USER2017.X (Name varchar(20),City varchar(20));
133insert into USER2017.X values ('Cust1','A'),('Cust2','B'),('Cust3','C');
134go
135
136create proc P1 with execute as caller
137as begin select * from USER2017.X; end;
138
139use [Baza2017]
140GO
141DENY SELECT ON [dbo].[x] TO [USER2017]
142GO
143
144--5
145
146
147--6
148
149
150
151--7