Non-Sysadmins Running xp_cmdshell

I know about the security risks of running xp_cmdshell by
non-sysadmins and I am already taking those issues into
account. I have a question about a problem I am having
letting users run this extended stored procedure.
I have created an Active Directory group and added it to
our SQL Server as a login and made it a user on the master
database. The only thing this user has authority to do is
execute xp_cmdshell. I made one of our users a member of
this AD group and had him execute a stored procedure that
executes the xp_cmdshell procedure. (We are sure he has
execute permissions on the stored procedure that executes
xp_cmdshell.)
When he runs the stored procedure he gets an error that
access is denied.
Next I removed him from the AD group and added a login for
him, with Windows authentication, and added him as a user
to the master database, then I granted execute permissions
on xp_cmdshell. When he runs the stored procedure now, it
executes the xp_cmdshell procedure properly.
Is there some rule about how permissions can be granted to
xp_cmdshell? The only difference between my two scenarios
is in one case he is executing with a connection directly
based on his personal AD account and in the other case,
the permissions are set via an AD group.
Thanks for any help.In EM RClick SQL Server Agent/ props/ job system/ uncheck
only users with Sysadmin bla bla. Put in a valid Adimn
Login and Password. You never want to grant direct access
to xp_cmdshell. Users can do lots more than your intended
proc if you do this.
>--Original Message--
>I know about the security risks of running xp_cmdshell by
>non-sysadmins and I am already taking those issues into
>account. I have a question about a problem I am having
>letting users run this extended stored procedure.
>I have created an Active Directory group and added it to
>our SQL Server as a login and made it a user on the
master
>database. The only thing this user has authority to do
is
>execute xp_cmdshell. I made one of our users a member of
>this AD group and had him execute a stored procedure that
>executes the xp_cmdshell procedure. (We are sure he has
>execute permissions on the stored procedure that executes
>xp_cmdshell.)
>When he runs the stored procedure he gets an error that
>access is denied.
>Next I removed him from the AD group and added a login
for
>him, with Windows authentication, and added him as a user
>to the master database, then I granted execute
permissions
>on xp_cmdshell. When he runs the stored procedure now,
it
>executes the xp_cmdshell procedure properly.
>Is there some rule about how permissions can be granted
to
>xp_cmdshell? The only difference between my two
scenarios
>is in one case he is executing with a connection directly
>based on his personal AD account and in the other case,
>the permissions are set via an AD group.
>Thanks for any help.
>.
>|||Hi Jason,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
From you description, in your system, you have a login account in a group.
In the SQL Server, you set it to Windows authentication and you grant the
execute permission of 'xp_cmdshell' to the user, but it failed with an
error message 'access is denied', while when you grant the execute
permission of the 'xp_cmdshell' to the group the user is belong in, no
message with it. You wonder if there is any rules for these, right? If I
misunderstood you, pleae feel free to let me know.
Well, I create a user 'test1' and 'test2' on my system belongs to 'testing
group', in my SQL Server Enterprise Manager, I grant the permission of
execute 'xp_cmdshell' to 'test1' and 'testing group'. Then I login with
account 'test1' and 'test2', both could execute " xp_cmdshell 'dir c:\' ".
So, you could grant the permissions to either user or group. Well, as we
always emphasize, it strongly recommend that it should be run by limited,
system administrator account for security concern.
Could you tell me which statement you are running in the xp_cmdshell? When
this user login in you system(not sql server), could he run the same
statements in command prompt? I am looking forward to your information.
Thanks!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Two things. One, your understanding is backwards. When I
granted rights to individuals it worked. When I granted
rights to the group it did not work.
More importantly, however, IT STARTED WORKING TODAY! The
setup is still exactly as it was when I started having
this problem. I did make some changes about removing
permissions and re-adding permissions trying to make it
work but it ultimately went back to how it was in the
beginning.
Anyway, thanks for the responses.
>--Original Message--
>Hi Jason,
>Thank you for using MSDN Newsgroup! It's my pleasure to
assist you with
>your issue.
>From you description, in your system, you have a login
account in a group.
>In the SQL Server, you set it to Windows authentication
and you grant the
>execute permission of 'xp_cmdshell' to the user, but it
failed with an
>error message 'access is denied', while when you grant
the execute
>permission of the 'xp_cmdshell' to the group the user is
belong in, no
>message with it. You wonder if there is any rules for
these, right? If I
>misunderstood you, pleae feel free to let me know.
>Well, I create a user 'test1' and 'test2' on my system
belongs to 'testing
>group', in my SQL Server Enterprise Manager, I grant the
permission of
>execute 'xp_cmdshell' to 'test1' and 'testing group'.
Then I login with
>account 'test1' and 'test2', both could execute "
xp_cmdshell 'dir c:\' ".
>So, you could grant the permissions to either user or
group. Well, as we
>always emphasize, it strongly recommend that it should be
run by limited,
>system administrator account for security concern.
>Could you tell me which statement you are running in the
xp_cmdshell? When
>this user login in you system(not sql server), could he
run the same
>statements in command prompt? I am looking forward to
your information.
>Thanks!
>Best regards
>Baisong Wei
>Microsoft Online Support
>----
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>Please reply to newsgroups only. Thanks.
>.
>|||Hi Jason,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
Sorry for the misunderstanding for the first time. It is good to here that
you have solved the problem. Here I just want to add some information about
the logins and users in the SQL Server 2000. Windows accounts (users or
groups) must be granted permissions to connect to an instance of Microsoft
SQL Server? before they can access a database. You could grant the
permissions to connect the SQL Server by expand a server group, and then
expand a server, then expand Security, right-click Logins, and then click
New Login. In the Name box, enter the Windows account (in the form
DOMAIN\User) to be granted access to SQL Server. Under Authentication,
click Windows Authentication. Then, in a database, you add them as database
user or add in a database roles. Then you could grant, deny and revoke the
permissions to operations such as select, insert, update, execute a stored
procedure on the database objects to the database users or apply this on a
specified role.
Again, glad to hear that you solved your problem and if you have any
questions about SQL server, please feel free to post message here and I am
ready to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.