SQL Server – Management Reports – Schema Change History – Permissions need

Background

Operationally I find myself connected to SQL Server running as a less privileged user.

A couple of days ago, I was running as one those less privileged users and experienced an error accessing Reports \ Schema Change Reports.

 

Error

The error we ran into is:

Image

ShowChangesMadeInTheSchemaOfTheObjectsbyDDLOperations

Textual

Unable to retrieve data for this section of the objects by DDL Operations.
Msg 8189, Level 4, State 2
You do not have permission to run sys.traces

 

Code

Here is the code invoked by SSMS when it tries to generate the report.

 


begin try  

	declare @enable int; 

	select top 1 @enable = convert(int,value_in_use)
	from sys.configurations
	where name = 'default trace enabled'  

	if @enable = 1
	begin 

        declare @d1 datetime;
        declare @diff int;
        declare @curr_tracefilename varchar(500);
        declare @base_tracefilename varchar(500);
        declare @indx int ;
        declare @temp_trace table
		(
			  obj_name nvarchar(256)
			, obj_id int
			, database_name nvarchar(256)
			, start_time datetime
			, event_class int
			, event_subclass int
			, object_type int
			, server_name nvarchar(256)
			, login_name nvarchar(256)
			, user_name nvarchar(256)
			, application_name nvarchar(256)
			, ddl_operation nvarchar(40)
        );

        select @curr_tracefilename = path
		from   sys.traces
		where is_default = 1
		;  

        set @curr_tracefilename = reverse(@curr_tracefilename)
        select @indx  = PATINDEX('%\%', @curr_tracefilename)
        set @curr_tracefilename = reverse(@curr_tracefilename)
        set @base_tracefilename = LEFT( @curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc'; 

        insert into @temp_trace
        select
			    ObjectName
        ,       ObjectID
        ,       DatabaseName
        ,       StartTime
        ,       EventClass
        ,       EventSubClass
        ,       ObjectType
        ,       ServerName
        ,       LoginName
        ,       NTUserName
        ,       ApplicationName
        ,       'temp'
        from ::fn_trace_gettable( @base_tracefilename, default )
        where EventClass in (46,47,164)
		and EventSubclass = 0
		and DatabaseID = db_id()
		;

        update @temp_trace set ddl_operation = 'CREATE' where event_class = 46;
        update @temp_trace set ddl_operation = 'DROP' where event_class = 47;
        update @temp_trace set ddl_operation = 'ALTER' where event_class = 164; 

        select @d1 = min(start_time) from @temp_trace
        set @diff= datediff(hh,@d1,getdate())
        set @diff=@diff/24; 

        select
				  @diff as difference
				, @d1 as date
				, object_type as obj_type_desc
				, (dense_rank() over (order by obj_name,object_type ) )%2 as l1
			    , (dense_rank() over (order by obj_name,object_type,start_time ))%2 as l2
				, *
        from @temp_trace
		where object_type not in (21587) -- don't bother with auto-statistics as it generates too much noise
        order by start_time desc;

end
else
begin
        Select top 0
				   1 as difference
				,  1 as date
				,  1 as obj_type_desc
				,  1 as l1
				,  1 as l2
				,  1 as obj_name
				,  1 as obj_id
				,  1 as database_name
				,  1 as start_time
				,  1 as event_class
				,  1 as event_subclass
				,  1 as object_type
				,  1 as server_name
				,  1 as login_name
				,  1 as user_name
				,  1 as application_name
				,  1 as ddl_operation
end  

end 

try  

begin
catch
select
		  -100 as difference
		, ERROR_NUMBER() as date
		, ERROR_SEVERITY() as obj_type_desc
		, 1 as l1
		, 1 as l2
		, ERROR_STATE() as obj_name
		, 1 as obj_id
		, ERROR_MESSAGE() as database_name
		, 1 as start_time
		, 1 as event_class
		, 1 as event_subclass
		, 1 as object_type
		, 1 as server_name
		, 1 as login_name
		, 1 as user_name
		, 1 as application_name
		, 1 as ddl_operation  

end catch

Breaking Code

There are a couple of places that the code break….

Get name of Default Trace

Code


select @curr_tracefilename = path
from   sys.traces
where is_default = 1
;  

Remediate


use master
go

grant select on sys.traces to -- user --;

 

Get name of Default Trace

Still breaking..

Code


select @curr_tracefilename = path
from   sys.traces
where is_default = 1
;  

Remediate


use master
go

grant alter trace to --user--;

Working Code

Here is the code we used to grant access.

Code


use master
go

if user_id('lowprivilegeUser') is null
begin

 exec('create user [lowprivilegeUser] from login [lowprivilegeUser] ')

end
go

grant alter trace to lowprivilegeUser;
go

References

  1. Security and Permissions – ALTER TRACE Permission
    https://msdn.microsoft.com/en-us/library/cc293611.aspx

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s