xp_cmdshell

[Bell1991]

I am attempting to set up a user to allow them to be able to execute the xp_cmdshell extended stored procedure and the user is not an admin. I have attempted to run:

exec xp_sqlagent_proxy_account N'SET',
N'builtin', 'userName', 'password'

and i get the following error:


No mapping between account names and security IDs was done.

Any suggestions?
Thanks,
-bell

 

[NilsBevaart]

If you want to grant execute permision to a stored procedure, use the GRANT EXECUTE ON xp_cmdshell TO @Login

If you grant this permission to a windows user who is not part of the administrator group, it will still - for security reasons - not work.

Nils Bevaart

 

[Catadmin]

Nils said "If you grant this permission to a windows user who is not part of the administrator group, it will still - for security reasons - not work. "

Really? Is this only true on this particular command / system extended stored procedures or is it because the user in question is a windows user not a SQL user?

I ask because yesterday we ran a test on Stored Procedures that we had created for a program, granting only Exec to a regular SQL user login (no admin rights and not mapped to a windows login) for the stored procedures, denied access to the tables on insert, select, delete, and update, and the user was able to login and run the stored procedures even though it didn't have permissions to the table itself.

Thanks for your input!



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[mrdenny]

xp_CMDShell is a special case. Microsoft setup security specific to this extended stored proc because it allows direct access to the OS.

When you granted the user rights to the proc, and deny'd rights to the table; the reason the proc worked was because it was supose to work. That is the purpose behind stored procedures. You grant access to the stored procedure, and not the table so that users can only access the data that you have decided to allow them to access. This helps prevent against text injection attacks.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[mrdenny]

I just realised that I never addressed the origional question.

I think you'll find it easier to configure the proxy account within the GUI than from t/sql. In Enterprise Manager go to Server>Management>SQL Server Agent. Right click on SQL Server Agent. Job System tab. It's down at the bottom.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)