SET NOCOUNT ON

В очередной раз наступил на эти грабли и решил написать – начинайте написание любой процедуры, триггера, просто длинного запроса со строки:

SET NOCOUNT ON

Без этой строки вы реально можете получить проблемы, например, в отчетах, быстрых поисках (если пишете запрос руками и там более одной конструкции), в стандартной функциональности (в случае использования триггеров).
Вот, что написано про эту конструкцию в документации:

Если значение инструкции SET NOCOUNT равно ON, то количество строк не возвращается. Если значение инструкции SET NOCOUNT равно OFF, то количество строк возвращается.
Функция @@ROWCOUNT обновляется, даже если значение SET NOCOUNT равно ON.
Инструкция SET NOCOUNT ON запрещает всем инструкциям хранимой процедуры отправлять клиенту сообщения DONE_IN_PROC. Для хранимых процедур с несколькими инструкциями, не возвращающих большое количество фактических данных, установка в инструкции SET NOCOUNT значения ON может значительно повысить производительность за счет существенного снижения объема сетевого трафика.
Инструкция SET NOCOUNT устанавливается во время выполнения, а не на этапе синтаксического анализа.

MS SQL Profiler

Profiler – очень мощный инструмент трассировки, упрощающий отладку новых процедур, решение проблем с доступом к данным, оптимизацию быстродействия и т.д.
Термины, связанные с трассировкой:

• Events/События – набор действий, происходящих на сервере, например, запуск хранимой процедуры, сходные события объединены в категории событий
• Data columns/Поля – каждое конкретное событие имеет набор свойств
• Filters/Фильтры – позволяют выбирать только нужные события

Сама программа обладает несколько архаичным интерфейсом. В результате использование ее не столь очевидно.
Пользоваться трассировщиком по умолчанию может пользователь SA и члены роли SYSADMINS. Начиная с версии SQL Server 2005 можно делегировать эти права любому пользователю:

1: -- разрешаем трассировку для LoginID
2: USE master
3: GRANT ALTER TRACE TO LoginID
4: GO
5: 
6: -- запрещаем трассировку для LoginID
7: USE master
8: REVOKE ALTER TRACE TO LoginID
9: GO
10: 

Для установки профайлера на рабочую станцию нужно воспользоваться дистрибутивом MS SQL, выбрав соответствующую опцию. Никаких дополнительных лицензий для этого не требуется.
Profiler после запуска начинает собирать информацию в соответствии с выбранными событиями, полями и фильтрами. Все это делается в памяти компьютера, на котором он запущен. Поэтому запускать длительную трассировку на основном сервере не лучшая идея, лучше сделать это с рабочей станции.
Запускаем трассировку:

1. Запускаем Profiler
2. File –> New Trace
3. Connect to server – стандартное окно выбора сервера (трассировать можно базу данных или аналитические сервисы)
4. Trace Properties – окно настройки трассировки. Позволяет выбрать события, поля событий и фильтры. Стоит обратить внимание на поле “Use the template”. Шаблон в данном случае – это набор предопределенных для характерных ситуаций настроек трассировки. Можно добавить свои шаблоны.
5. Жмем кнопку Run

Появляется основное окно трассировки. Оно разделено на две горизонтальные панели. В верхней панели выводятся захваченные события, каждой строке соответствует одно событие. В нижней панели выводится содержимое поля TextData выбранной строки верхней панели.
Стандартный набор команд позволяет поставить на паузу, остановить, запустить трассировку. Результаты трассировки можно сохранить (File –> Save).
Трассировка реально помогает в понимании как работает сервер баз данных, облегчает поиск проблем. Для эффективного использования, конечно, нужно понимать значение событий и полей, в связи с этим хочу обратить внимание, что если в окне Trace Properties навести курсор на поле либо на событие, то внизу окна появится краткое описание события и поля.
В завершении несколько фактов о Profiler:

• Начиная с версии MS SQL 2005 появилось новое событие Deadlock Graph. Оно значительно упрощает поиск взаимных блокировок, т.к. в графическом виде показывает суть конфликта.
• Профайлер позволяет не только захватывать события, но и воспроизводить их обратно на сервере. Так что его можно использовать как простейшее средство стрессового тестирования.
• Трассировку можно запускать программным способом. Причем в профайлере есть команда, облегчающая создания скрипта запуска трассировки. Для этого нужно просто запустить профайлер, создать новую трассировку, выставив все необходимы параметры. После этого запустите команду File –> Export –> Script Trace Definition –> For SQL Server… Вот для примера скрипт, созданный на основе стандартного шаблона:
  
  

1: /****************************************************/
2: /* Created by: SQL Server 2008 Profiler             */
3: /* Date: 17/07/2009  16:36:59         */
4: /****************************************************/
5: 
6: 
7: -- Create a Queue
8: declare @rc int
9: declare @TraceID int
10: declare @maxfilesize bigint
11: declare @DateTime datetime
12: 
13: set @DateTime = '2009-07-20 18:00:00.000'
14: set @maxfilesize = 5
15: 
16: -- Please replace the text InsertFileNameHere, with an appropriate
17: -- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
18: -- will be appended to the filename automatically. If you are writing from
19: -- remote server to local drive, please use UNC path and make sure server has
20: -- write access to your network share
21: 
22: exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, @Datetime
23: if (@rc != 0) goto error
24: 
25: -- Client side File and Table cannot be scripted
26: 
27: -- Set the events
28: declare @on bit
29: set @on = 1
30: exec sp_trace_setevent @TraceID, 14, 1, @on
31: exec sp_trace_setevent @TraceID, 14, 9, @on
32: exec sp_trace_setevent @TraceID, 14, 6, @on
33: exec sp_trace_setevent @TraceID, 14, 10, @on
34: exec sp_trace_setevent @TraceID, 14, 14, @on
35: exec sp_trace_setevent @TraceID, 14, 11, @on
36: exec sp_trace_setevent @TraceID, 14, 12, @on
37: exec sp_trace_setevent @TraceID, 15, 15, @on
38: exec sp_trace_setevent @TraceID, 15, 16, @on
39: exec sp_trace_setevent @TraceID, 15, 9, @on
40: exec sp_trace_setevent @TraceID, 15, 17, @on
41: exec sp_trace_setevent @TraceID, 15, 6, @on
42: exec sp_trace_setevent @TraceID, 15, 10, @on
43: exec sp_trace_setevent @TraceID, 15, 14, @on
44: exec sp_trace_setevent @TraceID, 15, 18, @on
45: exec sp_trace_setevent @TraceID, 15, 11, @on
46: exec sp_trace_setevent @TraceID, 15, 12, @on
47: exec sp_trace_setevent @TraceID, 15, 13, @on
48: exec sp_trace_setevent @TraceID, 17, 1, @on
49: exec sp_trace_setevent @TraceID, 17, 9, @on
50: exec sp_trace_setevent @TraceID, 17, 6, @on
51: exec sp_trace_setevent @TraceID, 17, 10, @on
52: exec sp_trace_setevent @TraceID, 17, 14, @on
53: exec sp_trace_setevent @TraceID, 17, 11, @on
54: exec sp_trace_setevent @TraceID, 17, 12, @on
55: exec sp_trace_setevent @TraceID, 10, 15, @on
56: exec sp_trace_setevent @TraceID, 10, 16, @on
57: exec sp_trace_setevent @TraceID, 10, 9, @on
58: exec sp_trace_setevent @TraceID, 10, 17, @on
59: exec sp_trace_setevent @TraceID, 10, 2, @on
60: exec sp_trace_setevent @TraceID, 10, 10, @on
61: exec sp_trace_setevent @TraceID, 10, 18, @on
62: exec sp_trace_setevent @TraceID, 10, 11, @on
63: exec sp_trace_setevent @TraceID, 10, 12, @on
64: exec sp_trace_setevent @TraceID, 10, 13, @on
65: exec sp_trace_setevent @TraceID, 10, 6, @on
66: exec sp_trace_setevent @TraceID, 10, 14, @on
67: exec sp_trace_setevent @TraceID, 12, 15, @on
68: exec sp_trace_setevent @TraceID, 12, 16, @on
69: exec sp_trace_setevent @TraceID, 12, 1, @on
70: exec sp_trace_setevent @TraceID, 12, 9, @on
71: exec sp_trace_setevent @TraceID, 12, 17, @on
72: exec sp_trace_setevent @TraceID, 12, 6, @on
73: exec sp_trace_setevent @TraceID, 12, 10, @on
74: exec sp_trace_setevent @TraceID, 12, 14, @on
75: exec sp_trace_setevent @TraceID, 12, 18, @on
76: exec sp_trace_setevent @TraceID, 12, 11, @on
77: exec sp_trace_setevent @TraceID, 12, 12, @on
78: exec sp_trace_setevent @TraceID, 12, 13, @on
79: exec sp_trace_setevent @TraceID, 13, 1, @on
80: exec sp_trace_setevent @TraceID, 13, 9, @on
81: exec sp_trace_setevent @TraceID, 13, 6, @on
82: exec sp_trace_setevent @TraceID, 13, 10, @on
83: exec sp_trace_setevent @TraceID, 13, 14, @on
84: exec sp_trace_setevent @TraceID, 13, 11, @on
85: exec sp_trace_setevent @TraceID, 13, 12, @on
86: 
87: 
88: -- Set the Filters
89: declare @intfilter int
90: declare @bigintfilter bigint
91: 
92: exec sp_trace_setfilter @TraceID, 6, 0, 6, N'User1'
93: exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - b384f0b7-6852-4661-86de-2da85ca97fdf'
94: -- Set the trace status to start
95: exec sp_trace_setstatus @TraceID, 1
96: 
97: -- display trace id for future references
98: select TraceID=@TraceID
99: goto finish
100: 
101: error: 
102: select ErrorCode=@rc
103: 
104: finish: 
105: go
106: 

EXECUTE AS

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

Варианты следующие:

1. Выбираем нужные таблицы и даем пользователю право читать их. Неудобно то, что все нужно будет повторять для каждого отчета и пользователя. Введение специальных ролей тоже не особо поможет, т.к. для каждого отчета набор таблиц обычно разный, плюс со временем может меняться как сам набор таблиц, так и пользователи, которым разрешен запуск отчета.
2. Включаем пользователя в роль db_datareader. В этом случае проблем с администрированием почти никаких, но пользователь будет иметь возможность читать все таблицы базы данных. В большинстве случаев политика безопасности предприятия не позволяет делать так.
3. Дать пользователю только право запускать хранимую процедуру, а уже внутри процедуры переключить контекст с помощью конструкции EXECUTE AS. В этом случае пользователь не будет иметь никаких избыточных прав, а администрирование сводится только к выдаче прав на конкретную процедуру.

Пример процедуры, реализующей последний вариант:

  1: USE iScalaDB
  2: 
  3: -- создаем новый логин
  4: CREATE LOGIN ReportUserLogin WITH PASSWORD='ERFss$#45%$'
  5: GO
  6: 
  7: -- создаем пользователя, на которого будем переключать контекст
  8: CREATE USER ReportUser FOR LOGIN ReportUserLogin
  9: GO
 10: 
 11: -- даем вспомогательному пользователю право читать всю базу данных
 12: EXEC sp_addrolemember 'db_datareader','ReportUser'
 13: GO
 14: 
 15: -- создаем проверочную процедру
 16: CREATE PROCEDURE dbo.TestExecuteAS
 17: WITH EXECUTE AS 'ReportUser'
 18: AS
 19: BEGIN
 20:     SET NOCOUNT ON
 21: 
 22:     SELECT *
 23:     FROM GL060108
 24: END
 25: GO
 26: 

Конструкция EXECUTE AS появилась в MS SQL2005, в более ранних версиях была команда SETUSER, но она имела ряд ограничений, которые не позволяли использовать ее в нашем сценарии.