September 16, 2009 at 7:15 am
I have a developer who is working on an application that utilizes a sql server login that needs to execute xp_cmdshell. Am I correct in that this permission can only be given to a windows\sql login and not a sql server login?
September 16, 2009 at 8:16 am
The permission of xp_cmdshell execution can be given to Domain NT users as well as SQL Login users.
Permissions
Execute permissions for xp_cmdshell default to members of the sysadmin fixed server role, but can be granted to other users.
Important If you choose to use a Windows NT account that is not a member of the local administrator's group for the MSSQLServer service, users who are not members of the sysadmin fixed server role cannot execute xp_cmdshell.
If you want to execute this extended stored procedure, you will either need to be a member of the sysadmin role, or have the xp_sqlagent_proxy_account set up on your SQL Server. If a login executing this extended stored procedure is a member of the sysadmin role then the submitted command will run under the security context associated with the SQL Server Service account in which it runs. If the login executing this procedure is not a member of the sysadmin role, then the command uses the xp_sqlagent_proxy_account login security context for determining whether operating system commands can and cannot be run. If there is no xp_sqlagent_proxy_account then using this procedure will fail for all users not in the sysadmin role.
September 16, 2009 at 8:31 am
disregard the previous post, that extended stored procedure is only sql 2000.
This link explains what you need to know
http://www.databasejournal.com/features/mssql/article.php/3789881/Proxy-Accounts-in-SQL-Server.htm
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 16, 2009 at 8:35 am
So is there a way to do this for sql 2005
September 16, 2009 at 8:49 am
This is quoted from the link that I just gave you
In SQL Server 2005 and 2008, to allow a non-sysadmin login to execute xp_cmdshell, you will need to create a special system credential ##xp_cmdshell_proxy_account## by running the extended stored procedure sp_xp_cmdshell_proxy_account and specify a Windows account. This account will be used to run xp_cmdshell by users that are not members of the sysadmin role.
USE master
GO
-- Create a test login called testuser
CREATE LOGIN testuser WITH PASSWORD='P3h4jek@x'
-- Create a proxy credential for xp_cmdshell.
EXEC sp_xp_cmdshell_proxy_account 'PowerDomain\PowerUser', 'P@ssw0rd';
-- Grant database access to the SQL Server login account that you want to provide access.
EXEC sp_grantdbaccess 'testuser'
-- Grant execute permission on xp_cmdshell to the SQL Server login account.
GRANT exec ON sys.xp_cmdshell TO [testuser]
GO
To confirm that the ##xp_cmdshell_proxy_account## credential has been created, you can select the sys.credentials view.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 16, 2009 at 9:22 am
So I should understand this to mean that I can not use a sql login for this process?
September 16, 2009 at 9:27 am
timscronin (9/16/2009)
So I should understand this to mean that I can not use a sql login for this process?
No, you can use a sql login for this.
what the example i showed you does, is use a windows account to act as the proxy account. this proxy account is used when the login trying to run xp_cmdshell is not a sysadmin account. so in the example testuser can be used to run xp_cmdshell.
-- Grant execute permission on xp_cmdshell to the SQL Server login account.
GRANT exec ON sys.xp_cmdshell TO [testuser]
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 16, 2009 at 9:39 am
Just to expand a little. The issue is that whatever account runs xp_cmdShell needs to have permissions on the server since this stored procedure basically just gives you a command line. So in order for these permissions to be available to a SQL login you have to set up a proxy account. This account will need to be a windows account.
Hope that helps some.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply