Ошибка СУБД: 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

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 базы данных не разрешено в процедура, функция или триггер.

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;

use tempdb; grant showplan to public;

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

select * from sys.server_principals

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

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

create table #t (i int, col int);
 
insert into #t 
values(1, 1);

create clustered index t on #t(col);