Sql-server – Grant execute on xp_logevent to sa using sa user to do it

csignaturesql serversql-clr

Running a SQL Server 2012 CLR stored procedure with this code:

const string executeLog = "EXEC master..xp_logevent @errorNumber, @message, informational";
using (SqlCommand cmd = new SqlCommand(executeLog, connection))
{
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.AddWithValue("@errorNumber", 60001);
    cmd.Parameters.AddWithValue("@message", url);

    cmd.ExecuteScalar();
}

I get this error:

System.Data.SqlClient.SqlException: The EXECUTE permission was denied
on the object 'xp_logevent', database 'mssqlsystemresource', schema
'sys'.

I've tried to do this as sa user:

grant execute on xp_logevent to sa

But I get another error:

You can not find user 'sa' that does not exist or the user does not
have permission.

How can I grant execute permissions to sa user?

Best Answer

How can I grant execute permissions to sa user?

You don't need to grant execute anything to sa as sa is in the sysadmin server role and can pretty much do anything. Hence, if you are getting an error executing xp_logevent, then you aren't logging in as sa. Is the connection string using context connection = true; or a regular / external connection? If using context connection = true; then the permissions are based on whoever is executing the SQLCLR stored procedure. If using a regular connection, the permissions are based on the logon account for the SQL Server NT Service (MSSQLSERVER / SQLEXPRESS if it is a default instance) by default, else it can be the permissions of whoever is executing the SQLCLR stored procedure if code has been added to do impersonation.

So, how is the SqlConnection being made? The answer to that question will help determine the appropriate fix. If this SQLCLR stored procedure is to be called by anyone (meaning, Users who are not necessarily in the db_owner database role or the sysadmin server role), then you can do the following to grant this permission to the smallest scope possible.

In the database that contains the SQLCLR stored procedure:

  1. Create an Asymmetric Key (see CREATE ASYMMETRIC KEY)
  2. Create a User from that Asymmetric Key (see CREATE USER)
  3. Add this new User to the db_owner Database Role
  4. Create a T-SQL Stored Procedure (maybe call it dbo.LogEvent) that accepts the two parameters you are passing in (@errorNumber and @message) and simply executes EXEC xp_logevent @errorNumber, @message, informational;
  5. Grant EXECUTE on this new Stored Procedure to any Users and/or Database Roles that will be calling the SQLCLR Stored Procedure
  6. Sign this new Stored Procedure with the same Asymmetric Key used to create the new User (see ADD SIGNATURE)
  7. Update your SQLCLR code to execute this new T-SQL Stored Procedure (i.e. dbo.LogEvent) instead of xp_logevent
    1. You can then change your using statement to have new SqlCommand("dbo.LogEvent", connection)
    2. then change cmd.CommandType = CommandType.Text; to be cmd.CommandType = CommandType.StoredProcedure;
    3. remove the const string executeLog line

Other notes:

  • Please do not use AddWithValue as there are issues with it. Instead, just create the SqlParameter with an explicitly set datatype and then add it to the SqlCommand.SqlParameters collection.
  • No need to call ExecuteScalar if you are not capturing something from a result set. Instead, call ExecuteNonQuery
Related Question