sa user permission error

[scott]

When I use sp_configure on my SQL2K8 R2 server while using the "sa" user
account, I'm getting the below error. If the sa account has the sysadmin
role, why would sql tell me that the sa account doesn't have the permissions
to run an action?

ERROR:

Msg 15247, Level 16, State 1, Procedure sp_configure, Line 94

User does not have permission to perform this action.

CODE:

USE master
GO

EXEC sp_configure 'show advanced options', 1
GO

RECONFIGURE WITH OVERRIDE
GO

EXEC sp_configure 'xp_cmdshell', 1
GO

RECONFIGURE WITH OVERRIDE

GO

EXEC sp_configure 'show advanced options', 0

GO

[Erland Sommarskog]

scott (sba...@mileslumber.com) writes:
> When I use sp_configure on my SQL2K8 R2 server while using the "sa" user
> account, I'm getting the below error. If the sa account has the sysadmin
> role, why would sql tell me that the sa account doesn't have the
> permissions to run an action?

Interesting. Someone renamed the original sa account, and then created
a new, but did not tell you as a prank?

One possibility is that at some point impersonated some other account with
EXECUTE AS, and forgot to run REVERT. So what does

SELECT SYSTEM_USER, original_login(),
has_perms_by_name(NULL,NULL,'alter settings')

return? The call to has_perms_by_name() comes from sp_configure. If you run

SELECT * FROM sys.fn_my_permissions(NULL, NULL)

What do you see?

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[scott]

SELECT SYSTEM_USER, original_login(),
has_perms_by_name(NULL,NULL,'alter settings')

returns sa sa 1

=================================================================

SELECT * FROM sys.fn_my_permissions(NULL, NULL)

returns

entity_name permission_name

server CONNECT SQL
server SHUTDOWN
server CREATE ENDPOINT
server CREATE ANY DATABASE
server ALTER ANY LOGIN
server ALTER ANY CREDENTIAL
server ALTER ANY ENDPOINT
server ALTER ANY LINKED SERVER
server ALTER ANY CONNECTION
server ALTER ANY DATABASE
server ALTER RESOURCES
server ALTER SETTINGS
server ALTER TRACE
server ADMINISTER BULK OPERATIONS
server AUTHENTICATE SERVER
server EXTERNAL ACCESS ASSEMBLY
server VIEW ANY DATABASE
server VIEW ANY DEFINITION
server VIEW SERVER STATE
server CREATE DDL EVENT NOTIFICATION
server CREATE TRACE EVENT NOTIFICATION
server ALTER ANY EVENT NOTIFICATION
server ALTER SERVER STATE
server UNSAFE ASSEMBLY
server ALTER ANY SERVER AUDIT
server CONTROL SERVER

"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9DECE899A...@127.0.0.1...

[scott]

i think i was running it against a remote server, thx

"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9DECE899A...@127.0.0.1...

[John Bell]

On Tue, 7 Sep 2010 13:58:21 -0500, "scott" <sba...@mileslumber.com>
wrote:

Hi

I can only think of three possible reasons:

1. You are not sa

2. sa is not a sysadmin or serveradmin

3. You have denied ALTER SETTINGS permission to sa?

And an improbably fourth option is sp_configure has been changed!

John

[Erland Sommarskog]

That is what I would expect from sa. But if you say that you ran the command
on a different server, where you were not sa, this not very interesting.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[scott]

What happened was that I was connected to a remote server. SQL2K8 assumed I
was accessing the remote sa account.

"John Bell" <jbellne...@hotmail.com> wrote in message
news:1lcd86hl7stcvrtp1...@4ax.com...