Описание создания ролей, прав и пользователей

--Создание ролей

create role manager_supply

create role manager_delivery

create role director

create role administrator

--Удаление ролей

drop role manager_supply

drop role manager_delivery

drop role director

drop role administrator

--Права ролей

grant execute on object:: dbo.View_good to manager_supply

grant execute on object:: dbo.Add_good to manager_supply

grant execute on object:: dbo.Update_qunt_good to manager_supply

grant execute on object:: dbo.Add_supplier to manager_supply

grant execute on object:: dbo.Del_supplier to manager_supply

grant execute on object:: dbo.View_supplier to manager_supply

grant execute on object:: dbo.View_good to manager_delivery

grant execute on object:: dbo.Del_good to manager_delivery

grant execute on object:: dbo.Update_qunt_good to manager_delivery

grant execute on object:: dbo.Add_client to manager_delivery

grant execute on object:: dbo.Del_client to manager_delivery

grant execute on object:: dbo.View_client to manager_delivery

grant execute on object:: dbo.View_table to director

grant execute on object:: dbo.View_ed_izm to director

grant execute on object:: dbo.View_group to director

grant execute on object:: dbo.View_good to director

grant execute on object:: dbo.View_client to director

grant execute on object:: dbo.View_supplier to director

grant execute on object:: dbo.View_delivery to director

grant execute on object:: dbo.View_cons_note to director

grant execute on object:: dbo.View_shipment to director

grant execute on object:: dbo.View_zakaz to director

grant execute on object:: dbo.View_hist_cost to director

grant execute on object:: dbo.View_table to administrator

grant execute on object:: dbo.View_ed_izm to administrator

grant execute on object:: dbo.View_group to administrator

grant execute on object:: dbo.View_good to administrator

grant execute on object:: dbo.View_client to administrator

grant execute on object:: dbo.View_supplier to administrator

grant execute on object:: dbo.View_delivery to administrator

grant execute on object:: dbo.View_cons_note to administrator

grant execute on object:: dbo.View_shipment to administrator

grant execute on object:: dbo.View_zakaz to administrator

grant execute on object:: dbo.View_hist_cost to administrator

grant execute on object:: dbo.Add_ed_izm to administrator

grant execute on object:: dbo.Add_group to administrator

grant execute on object:: dbo.Add_good to administrator

grant execute on object:: dbo.Add_client to administrator

grant execute on object:: dbo.Add_supplier to administrator

grant execute on object:: dbo.Add_delivery to administrator

grant execute on object:: dbo.Add_cons_note to administrator

grant execute on object:: dbo.Add_shipment to administrator

grant execute on object:: dbo.Add_zakaz to administrator

grant execute on object:: dbo.Add_hist_cost to administrator

grant execute on object:: dbo.Del_ed_izm to administrator

grant execute on object:: dbo.Del_group to administrator

grant execute on object:: dbo.Del_good to administrator

grant execute on object:: dbo.Del_client to administrator

grant execute on object:: dbo.Del_supplier to administrator

grant execute on object:: dbo.Del_delivery to administrator

grant execute on object:: dbo.Del_cons_note to administrator

grant execute on object:: dbo.Del_shipment to administrator

grant execute on object:: dbo.Del_zakaz to administrator

grant execute on object:: dbo.Del_hist_cost to administrator

grant execute on object:: dbo.Update_qunt_good to administrator

--Создание логинов для пользователей и пользователей

create login [Иванов] with password='1111', default_database=[db410]

create user [Иванов] for login [Иванов]

create login [Петров] with password='2222', default_database=[db410]

create user [Петров] for login [Петров]

create login [Сидоров] with password='3333', default_database=[db410]

create user [Сидоров] for login [Сидоров]

create login [Васечкин] with password='4444', default_database=[db410]

create user [Васечкин] for login [Васечкин]

create login [rrr] with password='123', default_database=[db410]

create user [rrr] for login [rrr]

--Назначение ролей пользователям

exec sp_addrolemember 'manager_supply', 'Иванов'

exec sp_addrolemember 'manager_delivery', 'Петров'

exec sp_addrolemember 'director', 'Сидоров'

exec sp_addrolemember 'administrator', 'Васечкин'

exec sp_addrolemember 'manager_supply', 'rrr'

Заключение

В данной курсовой работе разработана база данных для магазина детских товаров. База данных состоит из 10 таблиц:

· Единицы измерения

· Группы товаров

· Ассортимент

· История стоимости товаров

· Поставщики

· Поставки

· Накладные

· Клиенты

· Продажи

· Заказы

Каждая таблица имеет первичный ключ, состоящий из одного поля, кроме таблиц Накладные, Заказы и История стоимости товаров – в них первичный ключ состоит из двух полей. Кроме того каждая из таблиц связана как минимум с одной другой таблицей с помощью внешнего ключа. Наглядно это можно увидеть выше, в диаграмме. Для каждой из таблиц созданы хранимые процедуры добавления и удаления записей.

В базе данных созданы и другие хранимые процедуры, а также отчеты, триггеры и джоб согласно техническому заданию.

Использованная литература

1. Петкович Д. Microsoft SQL Server 2008. Руководство для начинающих: Пер. с англ. – СПб.: БХВ-Петербург, 2012.

2. Books Online. Электронная документация Microsoft SQL Server 2008.

3. https://msdn.microsoft.com/ru-ru/library/dd631854%28v=sql.10%29.aspx