GRANT EXECUTE returns 'mssqlsystemresource' does not exist

Question

GRANT EXECUTE returns 'mssqlsystemresource' does not exist

aarded

Ten Centuries

Points: 1312
More actions
June 28, 2011 at 6:53 am

#389851

Why isn't this working?
USE [Master]
GO
CREATE LOGIN [test] WITH PASSWORD=N'[test]', DEFAULT_DATABASE=master
GO
CREATE USER [test] FOR LOGIN [test]
GO
GRANT EXECUTE ON xp_cmdshell TO [test]
GO
DROP USER [test]
DROP LOGIN [test]
GO
Executed by sa, the GRANT statement returns the error:
Database 'mssqlsystemresource' does not exist. Make sure that the name is entered correctly.
I have it also tried with another 'non sysadmin' SQL-user. Same error.
Tnx.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

Is xp_CmdShell enabled on the instance? Have you setup the proxy?

If I may ask, what's the goal of granting exec permissions to xp_CmdShell? Maybe there is a cleaner way to do what you're trying to do.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Here's an article discussing the steps referenced by opc.three

http://sqlblog.com/blogs/tibor_karaszi/archive/2007/08/23/xp-cmdshell-and-permissions.aspx

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

SQLRNNR (6/28/2011)
Here's an article discussing the steps referenced by opc.three
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/08/23/xp-cmdshell-and-permissions.aspx

Thanks Jason, I will keep this one to pass along, at least until Jeff publishes his article on how to manage xp_CmdShell securely.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

opc.three (6/28/2011)
SQLRNNR (6/28/2011)
Here's an article discussing the steps referenced by opc.three
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/08/23/xp-cmdshell-and-permissions.aspx
Thanks Jason, I will keep this one to pass along, at least until Jeff publishes his article on how to manage xp_CmdShell securely.

I have a feeling it may be an article on several ways to do it via other means and not use xp_cmdshell.;-)

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

GSquared SSC Guru Points: 260824 More actions · Answer 5

What version of SQL Server are you using?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

aarded Ten Centuries Points: 1312 More actions · Answer 6

A lot of questions, and here are my answers:

- xp_cmdshell is enabled

- SQL server version is 2008 (forum topic = 2008)

- I have the error with version 2008 and 2008 R2

- I have tried the script from http://sqlblog.com/blogs/tibor_karaszi/archive/2007/08/23/xp-cmdshell-and-permissions.aspx and when I execute 'GRANT EXECUTE ON xp_cmdshell TO JohnDoe', I get the same error !!!

- I have setup a proxy account for SQLserver Agent, and this works fine.

- The goal is to set up a temporarly account which can copy files from server A to server B, for migration purposes.

I have a workaround with the SQL Agent proxy account, but I was wondering why I am getting the error. Am I the only one with this error, is there something wrong with my installations, or what is gioing on.

Eric.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 7

aarded (6/29/2011)
...
- I have setup a proxy account for SQLserver Agent, and this works fine.
...

Not a SQL Agent proxy, the xp_CmdShell proxy.

-- remove proxy

EXEC sys.sp_xp_cmdshell_proxy_account NULL

GO

-- create proxy (shows up in Object Explorer under /Server/Security/Credentials)

EXEC sys.sp_xp_cmdshell_proxy_account 'domani\user', 'pwd'

GO

Please see section starting with "xp_cmdshell Proxy Account" here: http://technet.microsoft.com/en-us/library/ms175046(SQL.110).aspx

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

aarded Ten Centuries Points: 1312 More actions · Answer 8

I kow!

But I mean that it works with SQL server agent and a proxy account.

I use this as a workaround.

The problem is: I can't set the 'GRANT EXECUTE' permission.

This returns the error:

Executed by sa, the GRANT statement returns the error:

Database 'mssqlsystemresource' does not exist. Make sure that the name is entered correctly

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

aarded (6/30/2011)
I kow!
But I mean that it works with SQL server agent and a proxy account.
I use this as a workaround.
The problem is: I can't set the 'GRANT EXECUTE' permission.
This returns the error:
Executed by sa, the GRANT statement returns the error:
Database 'mssqlsystemresource' does not exist. Make sure that the name is entered correctly

Is there a different user you can connect to the db as? A different user that has sa access. It seems that maybe somebody might have hardened security on that server.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 10

SQLRNNR (6/30/2011)
aarded (6/30/2011)
I kow!
But I mean that it works with SQL server agent and a proxy account.
I use this as a workaround.
The problem is: I can't set the 'GRANT EXECUTE' permission.
This returns the error:
Executed by sa, the GRANT statement returns the error:
Database 'mssqlsystemresource' does not exist. Make sure that the name is entered correctly
Is there a different user you can connect to the db as? A different user that has sa access. It seems that maybe somebody might have hardened security on that server.

Agreed, it does not add up.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

aarded Ten Centuries Points: 1312 More actions · Answer 11

Yes it is possible to connect with other users.

There many users, admins and others, windos and sql, which can connect.

And with a proxy account, it works with SQL agent.

So I think this can't be a server problem.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 12

SQLRNNR (6/28/2011)
opc.three (6/28/2011)
SQLRNNR (6/28/2011)
Here's an article discussing the steps referenced by opc.three
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/08/23/xp-cmdshell-and-permissions.aspx
Thanks Jason, I will keep this one to pass along, at least until Jeff publishes his article on how to manage xp_CmdShell securely.
I have a feeling it may be an article on several ways to do it via other means and not use xp_cmdshell.;-)

Quite the contrary, actually. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 13

Executed by sa, the GRANT statement returns the error:

It works perfectly fine on my system.