Reading Default Tracefile

Below are examples for Find Permission Changes In The Default Trace

Type1:

declare @tracefile varchar(100)

set @tracefile=(select * from ::fn_trace_getinfo(default) where traceid=1 and property=2 )

select * from sys.trace_events

select * from ::fn_trace_gettable('E:\MSSQL12.VAS1P\MSSQL\Log\log_15.trc',default) where TextData like ('%alter%')

Failed to initialize sqlcmd library with error number -2147467259. [SQLSTATE 42000] (Error 22050). The step failed

As part of one of requirements we have to configure a job in which we need to use DBMail part.

From the DBMail we need to send a query results. 

Query is like below:

exec sp_send_dbmail @profile_name='SQL Server Alerts',

@recipients = 'mailed@mailed.com;',

@subject ='Job failures on server',

@query='select [Job Name],[last start date] from jobstatus where [Job Name] NOT LIKE (''DBA -%'') and [last run message] like ''the job faile%''',

@body='Jobs Failure list';

But it is failed With below Error:

failed to initialize sqlcmd library with error number -2147467259. [SQLSTATE 42000] (Error 22050).  The step failed.

When I googled in some place I found one work around for this:

We need to mention the Table name as:

Dbname.owner.tablename

Here in my scenario it is:

msdb.dbo.jobstatus 

Query looks like below:

@query='select [Job Name],[last start date] from msdb.dbo.jobstatus where [Job Name] NOT LIKE (''DBA -%'') and [last run message] like ''the job faile%'''


This work around was very useful but at one of the server, again same error was thrown.
After researching and googling in one of the article mentioned to run the profiler, which resolved the issue.

SQL Server agent service accounts were mapped to local system account.

And the query was running by NT Authority/System user which have only public role, nothing else.

So I have given DBOwner privilages on MSDB database to that account.

Immediately issue got resolved.

Finding shared path information from Command Prmopt

From Command Prompt:

net share

Finding out SQL Server Service account using T-SQL

select * from sys.dm_server_services

Finding Sysadmin on a SQL Server Instance

T-SQL

SELECT NAME, isntname FROM SYSLOGINS
WHERE sysadmin = 1 and hasaccess = 1

EXEC sp_helpsrvrolemember 'sysadmin'