· 9 years ago · Oct 10, 2016, 04:24 PM
1/* nowa baza danych */
2CREATE DATABASE [db1-zad] ON PRIMARY
3( NAME = N'db1-zad', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\db1-zad.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
4 LOG ON
5( NAME = N'db1-zad_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\db1-zad_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
6GO
7ALTER DATABASE [db1-zad] SET COMPATIBILITY_LEVEL = 100
8GO
9ALTER DATABASE [db1-zad] SET ANSI_NULL_DEFAULT OFF
10GO
11ALTER DATABASE [db1-zad] SET ANSI_NULLS OFF
12GO
13ALTER DATABASE [db1-zad] SET ANSI_PADDING OFF
14GO
15ALTER DATABASE [db1-zad] SET ANSI_WARNINGS OFF
16GO
17ALTER DATABASE [db1-zad] SET ARITHABORT OFF
18GO
19ALTER DATABASE [db1-zad] SET AUTO_CLOSE OFF
20GO
21ALTER DATABASE [db1-zad] SET AUTO_CREATE_STATISTICS ON
22GO
23ALTER DATABASE [db1-zad] SET AUTO_SHRINK OFF
24GO
25ALTER DATABASE [db1-zad] SET AUTO_UPDATE_STATISTICS ON
26GO
27ALTER DATABASE [db1-zad] SET CURSOR_CLOSE_ON_COMMIT OFF
28GO
29ALTER DATABASE [db1-zad] SET CURSOR_DEFAULT GLOBAL
30GO
31ALTER DATABASE [db1-zad] SET CONCAT_NULL_YIELDS_NULL OFF
32GO
33ALTER DATABASE [db1-zad] SET NUMERIC_ROUNDABORT OFF
34GO
35ALTER DATABASE [db1-zad] SET QUOTED_IDENTIFIER OFF
36GO
37ALTER DATABASE [db1-zad] SET RECURSIVE_TRIGGERS OFF
38GO
39ALTER DATABASE [db1-zad] SET DISABLE_BROKER
40GO
41ALTER DATABASE [db1-zad] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
42GO
43ALTER DATABASE [db1-zad] SET DATE_CORRELATION_OPTIMIZATION OFF
44GO
45ALTER DATABASE [db1-zad] SET PARAMETERIZATION SIMPLE
46GO
47ALTER DATABASE [db1-zad] SET READ_WRITE
48GO
49ALTER DATABASE [db1-zad] SET RECOVERY FULL
50GO
51ALTER DATABASE [db1-zad] SET MULTI_USER
52GO
53ALTER DATABASE [db1-zad] SET PAGE_VERIFY CHECKSUM
54GO
55USE [db1-zad]
56GO
57IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [db1-zad] MODIFY FILEGROUP [PRIMARY] DEFAULT
58GO
59
60
61/* schema 1 */
62
63USE [db1-zad]
64GO
65
66/****** Object: Schema [schema1] Script Date: 10/10/2016 17:10:05 ******/
67CREATE SCHEMA [schema1] AUTHORIZATION [dbo]
68GO
69
70
71/* schema 2 */
72
73USE [db1-zad]
74GO
75
76/****** Object: Schema [schema1] Script Date: 10/10/2016 17:10:05 ******/
77CREATE SCHEMA [schema2] AUTHORIZATION [dbo]
78GO
79
80
81/* tabele */
82
83USE [db1-zad]
84GO
85
86SET ANSI_NULLS ON
87GO
88
89SET QUOTED_IDENTIFIER ON
90GO
91
92CREATE TABLE [schema1].[table1](
93 [id] [int] NOT NULL,
94 CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED
95(
96 [id] ASC
97)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
98) ON [PRIMARY]
99
100GO
101
102CREATE TABLE [schema1].[table2](
103 [id] [int] NOT NULL,
104 CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED
105(
106 [id] ASC
107)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
108) ON [PRIMARY]
109
110GO
111
112CREATE TABLE [schema2].[table3](
113 [id] [int] NOT NULL,
114 CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED
115(
116 [id] ASC
117)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
118) ON [PRIMARY]
119
120GO
121
122CREATE TABLE [schema2].[table4](
123 [id] [int] NOT NULL,
124 CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED
125(
126 [id] ASC
127)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
128) ON [PRIMARY]
129
130GO
131
132/* logins */
133
134CREATE LOGIN [MSSQLSERVER\winuser1] FROM WINDOWS WITH DEFAULT_DATABASE=[db1-zad], DEFAULT_LANGUAGE=[us_english]
135GO
136
137CREATE LOGIN [MSSQLSERVER\winuser2] FROM WINDOWS WITH DEFAULT_DATABASE=[db1-zad], DEFAULT_LANGUAGE=[us_english]
138GO
139
140CREATE LOGIN [MSSQLSERVER\winuser3] FROM WINDOWS WITH DEFAULT_DATABASE=[db1-zad], DEFAULT_LANGUAGE=[us_english]
141GO
142
143CREATE LOGIN [MSSQLSERVER\winuser4] FROM WINDOWS WITH DEFAULT_DATABASE=[db1-zad], DEFAULT_LANGUAGE=[us_english]
144GO
145
146CREATE LOGIN [MSSQLSERVER\winuser5] FROM WINDOWS WITH DEFAULT_DATABASE=[db1-zad], DEFAULT_LANGUAGE=[us_english]
147GO
148
149CREATE LOGIN [MSSQLSERVER\winuser6] FROM WINDOWS WITH DEFAULT_DATABASE=[db1-zad], DEFAULT_LANGUAGE=[us_english]
150GO
151
152
153/* users */
154
155USE [db1-zad]
156GO
157
158/****** Object: User [user1] Script Date: 10/10/2016 17:16:40 ******/
159GO
160
161CREATE USER [user1] FOR LOGIN [gr1\winuser1] WITH DEFAULT_SCHEMA=[schema1]
162GO
163
164CREATE USER [user2] FOR LOGIN [gr1\winuser2] WITH DEFAULT_SCHEMA=[schema1]
165GO
166
167CREATE USER [user3] FOR LOGIN [gr2\winuser3] WITH DEFAULT_SCHEMA=[schema1]
168GO
169
170CREATE USER [user4] FOR LOGIN [gr2\winuser4] WITH DEFAULT_SCHEMA=[schema1]
171GO
172
173CREATE USER [user5] FOR LOGIN [gr3\winuser5] WITH DEFAULT_SCHEMA=[schema1]
174GO
175
176CREATE USER [user6] FOR LOGIN [gr3\winuser6] WITH DEFAULT_SCHEMA=[schema1]
177GO