Who, ME, an almighty Sysadmin getting a permission denied error?

This is the story of someone who claimed to be a member of the sysadmin fixed server role and, when he tried to run one stored procedure through a session in which any of the different settings that instruct the relational engine that the user is interested in retrieving the execution plan in one of the many output forms (showplan_xml, showplan_all, showplan_text, statistics xml, etc) he received a permission denied error like the following:

Msg 262, Level 14, State 4, Procedure <procedure_name>, Line <line_number> SHOWPLAN permission denied in database '<database>'.

It is true that he was connecting to SQL using a login which was a member of the sysadmin fixed server role. It is also true that the code SQL Server executes to check if a given principal has been granted the necessary privileges to do this or that operation is short-circuited for members of sysadmin, and they are granted full access to everything. But, this person was missing that even when you login into SQL as a sysadmin, not necessarily everything you run is executed under the context of that principal.

The following annotated script which resembles what this user was doing will help you understand the problem. In order to execute this, connect to your playground instance of SQL Server using an account which is member of the sysadmin fixed server role, and then run this:

create database SPDB
go
use SPDB
go
create table SPTable (SPColumn int)
go
insert into SPTable values (1)
go
create login SPLogin with password=N'AnEasyOne!', default_database=SPDB, check_expiration=off, check_policy=off
go
create user SPUser for login SPLogin
go
create procedure p_SP with execute as 'SPUser' as
begin
select SPColumn as c from SPTable
select is_srvrolemember('sysadmin') [Is Current Login Member of Sysadmin]
end
go
select is_srvrolemember('sysadmin') [Is Current Login Member of Sysadmin]
go
exec p_SP -- This attempt to execute the stored procedure succeeds.
go
set statistics xml on
go
exec p_SP – However, this attempt to execute the same stored procedure again will fail as soon as it tries to execute the SELECT inside the procedure. Why? Because the presence of the STATISTICS XML setting in the current session, forces SQL to do an additional database level operation it didn’t have to do in the previous execution: it has to produce the execution plan. Before initiating that operation, it must evaluate whether or not the principal being used in the current execution context has been granted the permission for the Showplan database operation. Since it hasn’t, it fails and raises error 262.
go
set statistics xml off
go
drop login SPLogin
go
use master
go
drop database SPDB
go

Following SQL trace serves as an additional aid for you to understand the sequence in which the events occur, what principal is being used in the execution context at every point in time, and why the error happens.

If you want to know further details about this, visit the Showplan Security topic from the documentation.

If you would like me to clarify anything further, just let me know through a comment in this post.