Return
-
Title
The account specified when you create a SQL DLA template requires sysadmin role. -
Description
Per the Change Auditor Release Notes, the required permissions SQL Data Level auditing templates require that the credentials for the account specified when you create a template has the following permissions:• Permission to open a connection to the targeted database.• Read permissions on targeted tables and system tables.• VIEW SERVER STATE permission.• For SQL 2012 and later: CONTROL SERVER permission andensure that the specified database can be opened on thetarget server.No SQL Data level events are audited, and the SqlDlaLog records the [WARN][ChangeAuditor.SqlDla.LogReaderSession.InstallLiteSpeedAgent(0)] Failed to detect/install LiteSpeed agent on 'SQL Server'\Exception:System.Data.SqlClient.SqlException (0x80131904): User 'public' does not have permission to run DBCC ADDEXTENDEDPROC for database 'master'.User 'public' does not have permission to run DBCC ADDEXTENDEDPROC for database 'master'.User 'public' does not have permission to run DBCC ADDEXTENDEDPROC for database 'master'.User 'public' does not have permission to run DBCC ADDEXTENDEDPROC for database 'master'.Changed database context to 'master'.at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()at Dell.ChangeAuditor.SqlDla.LogReaderSession.InstallLiteSpeedAgent()ClientConnectionId:2f02153a-7f9a-47c3-844c-b6be8e16810aError Number:7983,State:4,Class:16 -
Cause
According to Microsoft the user must have SysAdmin to use this command:
https://msdn.microsoft.com/en-us/library/ms188379.aspx
Permissions
Only members of the sysadmin fixed server role can execute sp_addextendedproc. -
Resolution
WORKAROUND:
Assign the sysadmin role to the specified user account .
Enhancement request TFS00691121 has been submitted to development to have the documentation revised to add the requirement that for SQL 2012 and later: CONTROL SERVER permission, and sysadmin role is required as well asensure that the specified database can be opened on the target server. -
Defect ID
TF00691121