Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Сбрасываются права доступа на tempdb  [new]
Mikhalich
Member

Откуда:
Сообщений: 291
SQL Server 2008 SP4 x64.
Возникла проблема с 1с.
Выводит ошибку:
Ошибка СУБД: Microsoft OLE DB Provider for SQL Server: SHOWPLAN permission denied in database ‘tempdb’.
HRESULT=80040E14, SQLSrvr: SQLSTATE=42000, state=4, Severity=E, native=262, line=1

Сдела как написано здесь - http://www.gilev.ru/deniedtempdb/
Но после перезапуска SQL Server права доступа на учетку опять сбрасываются.

Подскажите как можно исправить??
14 окт 14, 08:14    [16700081]     Ответить | Цитировать Сообщить модератору
 Re: Сбрасываются права доступа на tempdb  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31816
Mikhalich
Подскажите как можно исправить??
GRANT SHOWPLAN TO [1cuser]
14 окт 14, 09:18    [16700218]     Ответить | Цитировать Сообщить модератору
 Re: Сбрасываются права доступа на tempdb  [new]
vfalconwing
Member

Откуда:
Сообщений: 3
Создать job на ежедневной (например) основе.
В шаге на базе tempdb сделать что-нить типа

IF NOT EXISTS ( SELECT * FROM sys.sysusers u WHERE u.name = 'UserXXX' )
BEGIN
CREATE USER UserXXX FOR LOGIN UserXXX
ALTER USER UserXXX WITH DEFAULT_SCHEMA=[dbo]
GRANT SHOWPLAN TO UserXXX
END
14 окт 14, 09:20    [16700224]     Ответить | Цитировать Сообщить модератору
 Re: Сбрасываются права доступа на tempdb  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31816
vfalconwing
Создать job на ежедневной (например) основе.
В шаге на базе tempdb сделать что-нить типа
...
GRANT SHOWPLAN TO UserXXX
Зачем, разве GRANT SHOWPLAN - это привилегия уровня базы???
vfalconwing
В шаге на базе tempdb сделать что-нить типа

IF NOT EXISTS ( SELECT * FROM sys.sysusers u WHERE u.name = 'UserXXX' )
Откуда в базе tempdb появился пользователь UserXXX? Или его предлагается вначале создать?
14 окт 14, 10:05    [16700396]     Ответить | Цитировать Сообщить модератору
 Re: Сбрасываются права доступа на tempdb  [new]
Glory
Member

Откуда:
Сообщений: 104751
alexeyvg
Зачем, разве GRANT SHOWPLAN - это привилегия уровня базы???

таки да, базы
14 окт 14, 10:07    [16700405]     Ответить | Цитировать Сообщить модератору
 Re: Сбрасываются права доступа на tempdb  [new]
Гадя Петрович
Member

Откуда: планета Плюк, 215 в тентуре, галактика Кин-дза-дза в Спирали
Сообщений: 52912
http://technet.microsoft.com/en-us/library/ms191129(v=sql.105).aspx
14 окт 14, 11:06    [16700769]     Ответить | Цитировать Сообщить модератору
 Re: Сбрасываются права доступа на tempdb  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31816
Glory
alexeyvg
Зачем, разве GRANT SHOWPLAN - это привилегия уровня базы???

таки да, базы
Странно, никогда бы не подумал...

Можно, тогда дать разрешение ALTER TRACE, туда входит GRANT SHOWPLAN?

А то мне как то не нравится метод "давать права на tempdb при старте сервера".
14 окт 14, 20:31    [16704215]     Ответить | Цитировать Сообщить модератору
 Re: Сбрасываются права доступа на tempdb  [new]
Mikhalich
Member

Откуда:
Сообщений: 291
Гадя Петрович
http://technet.microsoft.com/en-us/library/ms191129(v=sql.105).aspx

Делаю:
use master;
go

create procedure dbo.sp_tempdb_add_usr1cv82
as
  use [tempdb]
  create user [DB1\USR1CV82] for login [DB1\USR1CV82]
  exec sp_addrolemember N'db_owner', N'DB1\USR1CV82'
go


чтобы потом сделать:
exec sp_procoption @ProcName = 'sp_tempdb_add_usr1cv82', @OptionName = 'startup', @OptionValue = 'true'
go

select * from sys.configurations
where name = 'scan for startup procs';
go


Но мне выдает ошибку:

Сообщение 154, уровень 15, состояние 1, процедура sp_tempdb_add_usr1cv82, строка 4
инструкция USE базы данных не разрешено в процедура, функция или триггер.

Подскажите пожалуйста как правильно сделать.
15 окт 14, 06:33    [16705345]     Ответить | Цитировать Сообщить модератору
 Re: Сбрасываются права доступа на tempdb  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31816
Mikhalich
Подскажите пожалуйста как правильно сделать.
Динамический SQL, в нём вначале написать use [tempdb]
15 окт 14, 08:52    [16705501]     Ответить | Цитировать Сообщить модератору
 Re: Сбрасываются права доступа на tempdb  [new]
invm
Member

Откуда: Москва
Сообщений: 9683
Mikhalich
Подскажите пожалуйста как правильно сделать.
create procedure dbo.usp_adjust_tempdb_security
as
begin
 set nocount on;

 while not exists(select 1 from sys.databases where name = 'tempdb' and state_desc = 'ONLINE')
  waitfor delay '00:00:01';

 exec('use tempdb; grant showplan to public;');
end;
15 окт 14, 09:20    [16705559]     Ответить | Цитировать Сообщить модератору
 Re: Сбрасываются права доступа на tempdb  [new]
Mikhalich
Member

Откуда:
Сообщений: 291
Спасибо!!!
15 окт 14, 10:59    [16706097]     Ответить | Цитировать Сообщить модератору
 Re: Сбрасываются права доступа на tempdb  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
Mikhalich
Но после перезапуска SQL Server права доступа на учетку опять сбрасываются.

Подскажите как можно исправить??


Грантовать необходимые права в model.
15 окт 14, 20:16    [16710056]     Ответить | Цитировать Сообщить модератору
 Re: Сбрасываются права доступа на tempdb  [new]
Гадя Петрович
Member

Откуда: планета Плюк, 215 в тентуре, галактика Кин-дза-дза в Спирали
Сообщений: 52912
pkarklin
Mikhalich
Но после перезапуска SQL Server права доступа на учетку опять сбрасываются.

Подскажите как можно исправить??


Грантовать необходимые права в model.
умирать, так с музыкой?
15 окт 14, 21:16    [16710261]     Ответить | Цитировать Сообщить модератору
 Re: Сбрасываются права доступа на tempdb  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
Гадя Петрович
умирать, так с музыкой?


Не понял пассажа. При старте tempdb "создается из model". Поэтому, если в tempdb необходимо наличие пользовательских объектов и отличных от дефолта прав, то кроме накатывания соответствующих скриптов (CREATE, ALTER, GRANT) на tempdb следует продублировать их в model, чтобы не заниматься велосипедостроением с автозапуском хп.
15 окт 14, 21:22    [16710292]     Ответить | Цитировать Сообщить модератору
 Re: Сбрасываются права доступа на tempdb  [new]
Mikhalich
Member

Откуда:
Сообщений: 291
pkarklin
Грантовать необходимые права в model.

Спасибо!
(но пока повременю с таким методом - возможно необходимость наличия прав SHOWPLAN на tempdb временная)



Подскажите пожалуйста еще тему.
Я сделал так, как посоветовал invm:
use tempdb; grant showplan to public;


Пробую проверить, что после перезапуска SQL Server нужные права установлены:
use tempdb;

/*
select * from sys.server_principals
select * from sys.database_permissions
*/

select dp.class_desc, dp.permission_name, dp.state_desc, sp1.name as grantee, sp2.name as grantor
from sys.database_permissions dp
     left outer join sys.server_principals sp1 on dp.grantee_principal_id = sp1.principal_id
     left outer join sys.server_principals sp2 on dp.grantor_principal_id = sp2.principal_id
where dp.permission_name = 'SHOWPLAN'


Но получаю:
class_desc   permission_name   state_desc   grantee    grantor
------------ ----------------- ------------ ---------- -----------
DATABASE SHOWPLAN GRANT NULL sa

Почему grantee пустой???
16 окт 14, 04:17    [16711422]     Ответить | Цитировать Сообщить модератору
 Re: Сбрасываются права доступа на tempdb  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31816
Mikhalich
Почему grantee пустой???
Видимо, потому что "to public"
16 окт 14, 08:45    [16711589]     Ответить | Цитировать Сообщить модератору
 Re: Сбрасываются права доступа на tempdb  [new]
Mikhalich
Member

Откуда:
Сообщений: 291
alexeyvg
Mikhalich
Почему grantee пустой???
Видимо, потому что "to public"


Не, у public-а есть свой идентификатор:
select * from sys.server_principals

name              principal_id
----------------- -------------
sa 1
public 2
sysadmin 3
securityadmin 4

а в таблице sys.database_permissions поле grantee_principal_id равно 0
16 окт 14, 08:59    [16711631]     Ответить | Цитировать Сообщить модератору
 Re: Сбрасываются права доступа на tempdb  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31816
Mikhalich
alexeyvg
пропущено...
Видимо, потому что "to public"


Не, у public-а есть свой идентификатор:
select * from sys.server_principals


name              principal_id
----------------- -------------
sa 1
public 2
sysadmin 3
securityadmin 4
а в таблице sys.database_permissions поле grantee_principal_id равно 0
Ну, видимо, когда даётся разрешение "всем", в grantee_principal_id написывается значение 0

public - это же в данном случае не пользователь базы, и не роль, и не логин сервера.

Считается, что public - это группа, для которой даётся право, однако в инструкции grant showplan to ... группу (роль) указывать нельзя. Так что, видимо, 0 - это такое специальное значение, что бы показать, что право дано группе public.
16 окт 14, 09:11    [16711661]     Ответить | Цитировать Сообщить модератору
 Re: Сбрасываются права доступа на tempdb  [new]
Гадя Петрович
Member

Откуда: планета Плюк, 215 в тентуре, галактика Кин-дза-дза в Спирали
Сообщений: 52912
pkarklin
Гадя Петрович
умирать, так с музыкой?


Не понял пассажа. При старте tempdb "создается из model". Поэтому, если в tempdb необходимо наличие пользовательских объектов и отличных от дефолта прав, то кроме накатывания соответствующих скриптов (CREATE, ALTER, GRANT) на tempdb следует продублировать их в model, чтобы не заниматься велосипедостроением с автозапуском хп.
из model создается не только tempdb, а вообще все базы
16 окт 14, 09:26    [16711714]     Ответить | Цитировать Сообщить модератору
 Re: Сбрасываются права доступа на tempdb  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
Гадя Петрович
из model создается не только tempdb, а вообще все базы


Спасибо, я в курсе! Осталось только выяснить у ТС, что у него чаще происходит, рестарт сервера или создание новой базы, в которой не нужны объекты\права из model.

Сообщение было отредактировано: 16 окт 14, 09:54
16 окт 14, 09:53    [16711842]     Ответить | Цитировать Сообщить модератору
 Re: Сбрасываются права доступа на tempdb  [new]
invm
Member

Откуда: Москва
Сообщений: 9683
Mikhalich
а в таблице sys.database_permissions поле grantee_principal_id равно 0
Это и есть public.
principal_id нужно смотреть в database_principals, а не server_principals.
16 окт 14, 09:53    [16711844]     Ответить | Цитировать Сообщить модератору
 Re: Сбрасываются права доступа на tempdb  [new]
Mikhalich
Member

Откуда:
Сообщений: 291
invm
Mikhalich
а в таблице sys.database_permissions поле grantee_principal_id равно 0
Это и есть public.
principal_id нужно смотреть в database_principals, а не server_principals.

Спасибо! Все получилось
use tempdb;

select dp.class_desc, dp.permission_name, dp.state_desc, dp1.name as grantee, dp2.name as grantor
from sys.database_permissions dp
     left outer join sys.database_principals dp1 on dp.grantee_principal_id = dp1.principal_id
     left outer join sys.database_principals dp2 on dp.grantor_principal_id = dp2.principal_id
where dp.permission_name = 'SHOWPLAN'

class_desc  permission_name  state_desc  grantee   grantor
------------ ----------------- ------------ --------- ---------
DATABASE SHOWPLAN GRANT public dbo
17 окт 14, 20:41    [16723303]     Ответить | Цитировать Сообщить модератору
 Re: Сбрасываются права доступа на tempdb  [new]
Mikhalich
Member

Откуда:
Сообщений: 291
pkarklin
Гадя Петрович
из model создается не только tempdb, а вообще все базы


Спасибо, я в курсе! Осталось только выяснить у ТС, что у него чаще происходит, рестарт сервера или создание новой базы, в которой не нужны объекты\права из model.


Спасибо! Буду в курсе и этой возможности!
Создание новой базы происходит крайне редко. А перезагрузки каждую ночь.
17 окт 14, 20:43    [16723308]     Ответить | Цитировать Сообщить модератору
 Re: Сбрасываются права доступа на tempdb  [new]
o-o
Guest
а никому не показалось странным, откуда вообще лезет такая ошибка?
вроде как Any user can create temporary objects in tempdb без всяких специальных телодвижений,
и к тем временным объектам SHOWPLAN тоже приложен.
т.е. создайте на сервере логин, не мапя его вообще и не выдавая никаких прав в tempdb,
и этот логин спокойно может выполнить:
create table #t (i int, col int);
 
insert into #t 
values(1, 1);

и любоваться на план.

а теперь сюрприз!
попробуйте создать индекс от имени того самого логина и смотреть при этом план:
create clustered index t on #t(col);

(таблица должна быть непустая, иначе все получится без проблем)
Msg 262, Level 14, State 4, Line 1
SHOWPLAN permission denied in database 'tempdb'.

и это в том числе и на
Microsoft SQL Server 2012 - 11.0.5058.0 (X64) May 14 2014 18:34:29 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

вроде пишут, пофиксили для 2014-ого, проверить не могу:
TEMPDB SHOWPLAN Issue when creating indexes on temp tables with existing data
18 окт 14, 08:10    [16724419]     Ответить | Цитировать Сообщить модератору
 Re: Сбрасываются права доступа на tempdb  [new]
Mikhalich
Member

Откуда:
Сообщений: 291
o-o
а никому не показалось странным, откуда вообще лезет такая ошибка?

в моем случае - это одын-эс с ее технологическим журналом и чем-то там еще...
18 окт 14, 08:57    [16724437]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить