May 28, 2010 at 5:52 am
Hi,
I wish to allow non-sa users to perform BCP operations with XP_CMDSHELL. Now, there are a lot of articles to befound on how to do that, this is not my problem. What IS a problem is that default the use of XP_CMDSHELL is turned off. The sproc should be something like this:
CREATE PROCEDURE sp_procedure AS
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
EXECUTE xp_cmdshell 'some BCP command'
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE
GO
The sproc I made works fine, if I execute it (SA), but how to get this working for ordinary users... You need Server Control rights to run the sp_configure. I have tried constructions with credentials, I have found somewhere on a instance in our company exactly such a procedure which works, but cannot figure out how. If I look at the login which is used in this working construction, I see basically this login has EXECUTE RIGHTS on maste..xp_cmdshell and the sproc in a userDB, where the structure above is implemented. This works, but if I create a login with exactly the same permissions, then I get the message, that it's not allowed to execute the sp_configure.
So I miss something, but cannot find what. Any hint, any pointer to some article explaining how to do this is appreciated.
Greetz,
Hans Brouwer
May 28, 2010 at 6:14 am
If you want to do this, your user accounts need to have the "ALTER SETTINGS" permission set. This is a server-level permission, and, by default, is only set in the serveradmin and sysadmin roles.
To grant this permission to a user, you (as a system administrator) need to be in the master database, and to run "GRANT ALTER SETTINGS TO <username>".
Of course, this response doesn't address the issue of "why use BCP in this day & age..." 😉
Thomas Rushton
blog: https://thelonedba.wordpress.com
May 28, 2010 at 6:30 am
Of course, this response doesn't address the issue of "why use BCP in this day & age..."
You are right. But don't ask, I have stopped asking...
Then again, if you need to read millions of records, on a ad-hoc basis, any time of day and night... It is still the fastest way to transfer data in or out.
Tnx for answering, I'll play with that. Is there any way I can check if a login has this permission?
Greetz,
Hans Brouwer
May 28, 2010 at 6:43 am
FreeHansje (5/28/2010)
Is there any way I can check if a login has this permission?
The permission to "ALTER SETTINGS"? Log in as that user, and do a select * from sys.server_permissions. Look for permission type "ALST". However, if the user is a member of the serveradmin or sysadmin role, then that permission is granted explicitly so may not appear in the view.
http://msdn.microsoft.com/en-us/library/ms186260.aspx
I'm sure there's a way of doing it using the system base-tables, but I haven't figured it out yet! 😀
Thomas Rushton
blog: https://thelonedba.wordpress.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply