Permissions For Non Sa User To Execute Xp_cmdShell
Mar 6, 2008
Hi,
I want to execute BCP in Query Analyser in SQL Server 2005 Express for that i surf on net and find that i should execute BCP under xp_cmdShell, That works good for addministritative account on SQL. But i want the working will be done by a non administrative account or non 'sa' user.
How can i assign a non sa User permissions to execute xp_cmdShell? or just tell me any other alternative way to run BCP in Query Analyser or code behined.
To use Reporting Services as a rendering engine I want to configure a local user on the server that has only the minimum set of permissions and user rights. The server is W2K3 SP2 and SQL 9.0.3200.
In particular, this local user has been removed from the local "Users" group and so is the "Authenticated Users" built-in group. In Reporting Services, it is mapped to a role that only has the "Execute Report Definitions" task permission.
Then, following the details in http://support.microsoft.com/kb/812614/ (Default permissions and user rights for IIS 6.0) I added all file security and local user rights required for "Users" and also granted and propagated "Read&Execute" on the "Reporting Services" folder and verified this using "Effective Permissions" on the ReportService2005.asmx file.
However, I still get 401 Unauthorized, also after a complete restart of all related machines and services.
Once I add the user or "Authenticated Users" back to "Users" everything works fine.
What permissions might I be missing? Where could I find those permission requirements documented?
I tried analyzing the 401 using auditing file and object access security but to no avail. There are no Failure audit entries in the Security log.
How can I investigate the minimum permission set?
What is the risk of leaving the user in the "Users" local group?
Hey there, I have a procedure that runs a PERL script through xp_cmdshell. The PERL script opens Excel and has Excel open a document so that it can parse through it. When I run the PERL script directly from the command line, it works perfectly. When I run it from xp_cmdshell I get the following error: Win32::OLE(0.1502) error 0x800a03ec in METHOD/PROPERTYGET "Open" at c:perlexcelTestRead.pl line 10
Now I initially thought that this was a simple permissions problem, but the account that xp_cmdshell uses has full permissions on the directory the file's in and to the Excel application. Wierder still, I can use PERL to read and write files to my heart's content. I just can't use the OLE Excel object to open an Excel file.
Anyone encounter something like this before? I think the fact that it's PERL is coincidental. The issue is that I can't use the Win32 Excel.Application object to open Excel files when using xp_cmdshell to do so. Remember, this works when I run it from the command line.
Is there any way to allow a user to use the xp_cmdshell extendedstored procedure without giving that user execute permissions toxp_cmdshell in SQL server 6.5? Let me clarify. Lets say I (as thedbo) create a stored procedure called sp_send_err:CREATE PROCEDURE sp_send_err @CompID varchar(20) ASdeclare @strCMD varchar(255)select @strCMD = "master.dbo.xp_cmdshell 'net send " + @CompID + """ERROR!""', no_output"execute (@strCMD)GONow lest say I give "user1" execute permissions on sp_send_err, but nopermissions on xp_cmdshell. When I run sp_send_error I get thefollowing error:"EXECUTE permission denied on object xp_cmdshell, database master,owner dbo".Why doesn't this work? What else can I do?
Im having trouble getting xp_cmdshell to work after we changed the service account for our sql server. It was working perfectly before - so i know that execute permissions have been granted, and that we have a credential set up properly.
I have read that I need to ensure the service account has permissions to 'act as opertaing system' and 'replace a process level token'. I have granted these rights in the local security policy as well.
However, I still get :
A call to 'CreateProcessAsUser' failed with error code: '1314'.
Do I need to restart the service? Or the whole server? Or have I missed something else?
Hi allI have a stored procedure that has the lineEXEC master..xp_cmdshell 'dtsrun /Stestjob1 /N testdts /E'If I run the SP from an access front end as a trusted user or from ascheduled job it runs fine and exectues the dts.If I run the stored procedure using VB6 as a standard connection the dtsjobwont run. I get back Execute permissions denied on xp_cmd.. on databasemasterdb_connect_string = "Provider=SQLOLEDB.1;Persist Security Info=False;UserID=test_connect;PWD=pw1test;Initial Catalog=testdb;Data Source=" &database_name....Set cmd = New ADODB.Commandcmd.ActiveConnection = db_connect_stringcmd.CommandType = adCmdStoredProccmd.CommandText = "testStoredProcedure"cmd.ExecuteDo I need to give test_connect permisions to run the test stored procedure.I hoped that because the VB called a stored procedure and the connection hadpermissions to execute the SP then it would be the SP that called thexp_command....can anyone tell me the accepted way to do thismany thanksAndy
Can anyone help me please. Healp please. Does anybody know how to make xp cmdshell runnable for users other than Admin. It should be possible to grant execute to others, but i can't figure out how.
This is what i found:
Be aware that when you grant execute permission for xp_cmdshell to users, the users will be able to execute any operating-system command at the Windows NT command shell that the account running SQL Server (typically local system) has privilege to execute.
To restrict xp_cmdshell access to users who have administrator permission on the Windows NT-based computer where SQL Server is running, use SQL Setup or SQL Enterprise Manager to set the server options, selecting the "xp_cmdshell - Impersonates Client" option. With this option selected, only users who have connected to SQL Server via a trusted connection and are members of the local Administrators group on that computer are allowed to use xp_cmdshell. The commands run by xp_cmdshell continue to execute in the server's security context.
I have to execute stored procedures containing xp_cmdshell and certain system storedprocedures in msdb and master with a user who is not SA. (i.e iam able to execute stored procedures when i log as sa, but any other user cannot run them)
In SQL Server using xp_cmdshell we can excute any of the command or executable files which can be executed in command prompt. Here my problem is that .. I am trying to execute OSQL from the MSSQL(Query Analyser) using xp_cmdshell.. but its give error saying "'osql' is not recognized as an internal or external command, operable program or batch file." This error occours when it is not able to find the executable file... but same thing I am able to execute from the command prompt. So I feel this problem is some where related to the path setting of windows. If some one can solve this problem or sugesst the how to set the path for window it will be help full.. waiting for reply
I was trying to grant access for an application user for executing xp_cmdshell, but I got some error message saying that either doesn't the user exist, or I don't have the permissions to grant this. Does the user need to be a user in Master ? Or, don't I when logged in as "sa" have the sufficient permissions to grant execute on a SP in master?
I solved it by checking "Control server" under "Properties" > "Securables" for the login, but I don't actually want this login to have full control.
(And yes, I've read that allowing xp_cmdshell usage isn't recommended at all.)
A developer needs to execute an SSIS package from a stored procedure and I do not want to enable xp_cmdshell on SQL 2005.
One suggestion is to have the application invoke dtexec on the client PC, but this would cause the package to be executed on the client and also requires some SSIS components be installed on each client.
Another idea is to configure a Windows Service to execute dtexec, but I do not know the risks of this approach. Any thoughts?
i need to start an external program (vb.net) from within a trigger. via xp_cmdshell everything works fine except that the execution of the trigger waits for the vb-program to finish.
now i tried a workaround with a "starter application" which only calls the vb.net program (asynchronous) an ends.
this works fine from a dos prompt (command prompt returns immediately and vb.net task is running). but when i call the starter app from the trigger with xp_cmdshell, the behavior is the same as before: execution does not continue until the vb.net task ends.
any other ideas that could solve my problem? submitting an agent-job is not fine for me because i need to supply parameters to the called program and maybe i need call it more than once at the same time.
I've previous posted a message with error building the expression of the dtexec.exe string. But now, the problem is other, and I've decided to create other threat.
Here is what I'm doing: I've a SP that receives every parameter so I can build my dtexec.exe statement to be executed by a variable with something like this exec @rc = master.dbo.xp_cmdshell @CMD
Till now I have no problem executing it, not from a variable, but just with the statement itself, like this: exec master.dbo.xp_cmdshell 'C:PROGRA~2MICROS~290DTSBINNDTEXEC.EXE /SQ PACKAGE /SET "Package.Variables[PERIOD].Value";20070101'.
But now, I have this SP that builds the string into a variable and then I just want to execute it throught the variable like this: exec @rc = master.dbo.xp_cmdshell @CMD.
When I make a SELECT @CMD just before the exec @rc = master.dbo.xp_cmdshell @CMD, so it prints the builded string, copy it and pasted to exec master.dbo.xp_cmdshell 'copied string' it works fine. But the step of exec @rc = master.dbo.xp_cmdshell @CMD returns the error: The filename, directory name, or volume label syntax is incorrect.
Why is this, if I copy the generated string and execute it by the other way it works fine, but executing the generated string throught the variable it gives me that error?
Hi, I have been searching for an answer to allow me to execute xp_cmdshell from withing store prcedure by calling the store procedure from an aspx via click on a button. This is what I found from my reserch but was not able to know where to set the rights and what each of them means: "To run xp_cmdshell for a non-system administrator user, you must grant the following rights. MSSQLServer and SQLServerAgent Services Act as part of the Operating System. Increase Quotas. Replace a process level token. Log on as a batch job."
The above quote was from the following link: http://support.microsoft.com/default.aspx?scid=kb;en-us;264155
The bottom line is to be able to call a store procedure from an aspx page to execute the code which contain xp_cmdshell command, an example of such command is like: ------------------------------------------------------------ EXEC master..xp_cmdshell 'dir d:BT_importDatasales_option_price_report.csv'
Hi, I need to send a table data into flat and then ftp into different location. I was using xp_cmdshell via sql task but my network engineer is saying that this xp_cmdshell will break the security and recomond to use "Execute Process Task". If i'm using this task getting the below error. Could you advice me regrding network engineer thought and any solution for avoiding this error.
--------------------------- Execute Process Task: C:WINDOWSsystem32ftp.exe --------------------------- CreateProcessTask 'DTSTask_DTSCreateProcessTask_1': Process returned code 2, which does not match the specified SuccessReturnCode of 0. --------------------------- Thanks,
We connect to the remote database servers through the network from loca by using Query Analyzer. Previously we were able to execute the xp_cmdshell command from local Query Analyzer to fetch the remote databases data. But now we are unable to execute the xp_cmdshell command on remote databases from local Query Analyzer We do not know what happened but i think due to network updates this command is not able to execute...
For ex: Previously i was able to execute master..xp_cmdshell 'net start' from local Query Analyzer.But now not able to execute
Now my question is, is there any other way(Directly or indirectly) to execute the xp_cmdshell command on remote databases from local?
Note : we are able to execute this command on remote Query Analyzer but not from local QA
Hi,I am having some trouble copying data over my workgroup network from my Windows 2003 Server Machine (machineA with SQL SERVER 2005) to one of my network Machine's drive(MachineB).Here is the T-SQL code that I am trying to execute:EXEC xp_cmdshell 'copy D:Datafile.txt \MachineBDocuments'Whenever I tried to execute the above piece of code, I get the error message "Access is denied", but if I try to copy the file from the Command Prompt (cmd.exe) with the copy command, the file copies fine over the network.I have already searched over the internet and I found out that loads of people have the same issue, and they were suggested something like this:"Check in Services and make sure that the MSSQLServer service is run as a domain user and that domain user has rights to these network resources."Well it sounds plausible, but I don't know what are the exact steps to do this. How do I know which user is running the MSSQL Server service? Are they referring to the user which I use to connect to my SQL Server Database engine throuhg the SQL Server Management Studio?Also they are suggesting 'domain user', and as I said before I do not have domain network just regular simple workgroup network.Here are some details of the user that I use to login. I generally login into my Windows 2003 Server machine with user called 'User1' and I use the same 'User1' to connect to SQL Server through the Management Studio Screen.Should I create a user called 'User1' on my MachineB(Destination Machine)?I would really appreciate, if someone can give me detailed steps explaning how to solve this problem.Thank you very much once again.
I am having some trouble copying data over my workgroup network from my Windows 2003 Server Machine (machineA with SQL SERVER 2005) to one of my network Machine's drive(MachineB). Here is the T-SQL code that I am trying to execute:
Whenever I tried to execute the above piece of code, I get the error message "Access is denied", but if I try to copy the file from the Command Prompt (cmd.exe) with the copy command, the file copies fine over the network.
I have already searched over the internet and I found out that loads of people have the same issue, and they were suggested something like this: "Check in Services and make sure that the MSSQLServer service is run as a domain user and that domain user has rights to these network resources."
Well it sounds plausible, but I don't know what are the exact steps to do this. How do I know which user is running the MSSQL Server service? Are they referring to the user which I use to connect to my SQL Server Database engine throuhg the SQL Server Management Studio? Also they are suggesting 'domain user', and as I said before I do not have domain network just regular simple workgroup network.
Here are some details of the user that I use to login. I generally login into my Windows 2003 Server machine with user called 'User1' and I use the same 'User1' to connect to SQL Server through the Management Studio Screen. Should I create a user called 'User1' on my MachineB(Destination Machine)?
I would really appreciate, if someone can give me detailed steps explaning how to solve this problem.
I need the file created by my BCP QUERYOUT command I'm executing from XP_CmdShell in Transact-SQL to have a specific OWNER. I've tried using the sp_xp_cmdshell_proxy_account to setup the correct owner. The owner is always SERVERNAMEAdministrators.
I'm trying to deploy a project that I deployed yesterday just fine, but today I get the following error:
------ Deploy started: Project: Point Reports, Configuration: Debug ------
Deploying to http://reporting.companyname.com/reportserver
Deploying data source '/Data Sources/Srv24.FieldResponse2_1'.
The permissions granted to user 'DOMAINharley.p.bartman' are insufficient for performing this operation.
Deploy complete -- 1 errors, 0 warnings
This seems like a basic permission issue, except I'm not logged in as the user listed! I've never logged into my computer as the user. I did log in to the reporting services website yesterday as that user, but since have rebooted my machine and logged into bothe my computer and the reporting services website as me. Yesterday this report deployed fine. Today, this error message. I've even tried creating a new project and just creating a simple datasource and deploying just that, but still this message! Where is Visual Studio storing and reusing this user name during my deploy process???
I am trying to loop through & set execute permission on some UDFS but I cannot find an option for UDF's.
Private mobjSQL As SQLDMO.SQLServer Dim objDB As SQLDMO.Database
Private Sub GrantUDF()
Dim objDB As SQLDMO.Database Dim objUDFS As UserDefinedFunctions Dim objUDF As SQLDMO.UserDefinedFunction Dim lngUDFCount As Long Dim lngProg As Long
For Each objDB In mobjSQL.Databases If ListItemChecked(Me.lstDatabases, objDB.Name) Then lngUDFCount = objDB.???????????????????? lngProg = 0 For Each objUDF In objDB.?????????????????? GeneralProgress objUDF.Name, 0, lngUDFCount, lngProg objUDF.Grant SQLDMOPriv_Execute, "AGDB" lngProg = lngProg + 1 Next GeneralProgress "Finished", 0, lngUDFCount, lngProg End If Next
In this example ….. I have a sql user id called 'toronto' with the permissions it acquired by being added to the db_datareader (READ) & db_datawriter (ADD, CHANGE, DELETE) database roles in the 'getranet' database. However, the 'toronto' account is unable to execute or run any of the 240 stored procedures in the 'getranet' database with only these permissions, at least that's the results I'm getting.
Solution #1: If I add the toronto id to the db_dbowners role (PERFORM ANY ACTIVITY) in the 'getranet' database, the problem is resolved. I would rather not use this approach because with these permissions the id can delete the db, or tables etc… and I'm not the only one with the id and password.
Solution #2: I created a new database role (SP_EXECUTE) in the 'getranet' database, and granted the role 'execute' permissions to all 240 stored procedures in the database, added the 'toronto' id to the SP_EXECUTE role, the problem is resolved. This solution works fine but it will require more maintenance, anytime a stored procedure is added or modified I will have to remember to update the SP_EXECUTE database role in the getranet database. Hey, I will forget once in a while, apply thumb screws here.
So my question is …. Am I approaching this all wrong? Is there a 3rd, 4th or a better solution (White Elephant) that I just can't see? I just thought I would ask…..
I have created a stored procedure that executes when a service broker message is received, this sp then exec's sp_send_dbmail.
But i keep getting the error that execute permission is denied on sp_send_dbmail.
Fair enough but i have no idea who to grant the execute permissions to ? The sp is called by the endpoint service of the service broker queue, the sp is owned by dbo who is 'sa', i have tried altering the sp to execute as 'dbo' or as caller, but is still get the same error.
I have tried to exec sp_send_dbmail as 'sa' and it works.
I do not know under what user this sp executes when the service calls the sp, i also cannot get it to work with an execute as dbo statement even though dbo is 'sa' ?
Please help ?
Not sure if this should be posted under service broker becuase this post is more related to permissions ?
I have a group of users that I have given db_datareader permissions to in an SQL Server 2000 database. I am also creating web pages on an intranet site that pulls data from the table. If I just use a select statement to pull the data from the table, the users don’t have a problem. If I use a stored procedure with the exact same sql statement, the users get an error until I grant them execute permissions on the stored procedure.
I have heard that store procedures is the best way to handle data operations but having to make sure I assign execute permissions every time I create a stored procedures can be a pain. The only way I know of to make sure that they had permissions would be to make them a member of db_Owner which is definitely not an option.
Is this just the way it is, or is there some way to automatically grant them execute permissions on stored procedures that are nothing more than select statements and don’t violate db_datareader permissions?
I'm trying to use the SPROC below (courtesy of Erland!) to capture theerror message but it fails owing to insufficient permissions (I can'treproduce it just now, but I think it's because it can't get access tothe DBCC OUTPUTBUFFER).How do I give the SPROC permission to execute?Many thanksEdwardCREATE PROCEDURE stpShowErrorMessage @errmsg nvarchar(500) OUTPUT ASDECLARE @dbccrow nchar(77),@msglen int,@lenstr nchar(2),@sql nvarchar(2000),@s tinyint-- Catch the output buffer.CREATE TABLE #DBCCOUT (col1 nchar(77) NOT NULL)INSERT INTO #DBCCOUTEXEC ('DBCC OUTPUTBUFFER(@@spid)')-- Set up a cursor over the table. We skip the first-- row, because there is nothing of interest.DECLARE error_cursor CURSOR STATIC FORWARD_ONLY FORSELECT col1FROM #DBCCOUTWHERE left(col1, 8) <> replicate('0', 8)ORDER BY col1-- Init variable, and open cursor.SELECT @errmsg = ''OPEN error_cursorFETCH NEXT FROM error_cursor INTO @dbccrow-- On this first row we find the length.SELECT @lenstr = substring(@dbccrow, 15, 2)-- Convert hexstring to intSELECT @sql = 'SELECT @int = convert(int, 0x00' + @lenstr + ')'EXEC sp_executesql @sql, N'@int int OUTPUT', @msglen OUTPUT-- @s is where the text part of the buffer starts.SELECT @s = 62-- Now assemble rest of string.WHILE @@FETCH_STATUS = 0 AND datalength(@errmsg) - 1 < 2 * @msglenBEGINSELECT @errmsg = @errmsg + substring(@dbccrow, @s + 1, 1) +substring(@dbccrow, @s + 3, 1) +substring(@dbccrow, @s + 5, 1) +substring(@dbccrow, @s + 7, 1) +substring(@dbccrow, @s + 9, 1) +substring(@dbccrow, @s + 11, 1) +substring(@dbccrow, @s + 13, 1) +substring(@dbccrow, @s + 15, 1)FETCH NEXT FROM error_cursor INTO @dbccrowENDCLOSE error_cursorDEALLOCATE error_cursor-- Now chop first character which is the length, and cut after end.SELECT @errmsg = substring(@errmsg, 2, @msglen)GO
bulk insert SCORPIO_STAGE_BULK_DATAPDCC from 'd:BulkTestonmech_stat_apd_clark_credit.dat' with (formatfile = 'd:BulkTestDATAPDCC.fmt')
go
alter procedure jason_test_exec
with execute as 'bulk_insert_test_jcb'
as
bulk insert SCORPIO_STAGE_BULK_DATAPDCC from 'd:BulkTestonmech_stat_apd_clark_credit.dat' with (formatfile = 'd:BulkTestDATAPDCC.fmt')
go
Then, log into SQL Server via management stuido as the SQL user "bulk_insert_test_jcb" this user has server-level bulk admin rights and execute rights on both of these stored procs:
exec jason_test This works
exec jason_test_exec gives:
Msg 4834, Level 16, State 1, Procedure jason_test_exec, Line 4
You do not have permission to use the bulk load statement.
Can you help me with this? Why is the user prevented from running this bulk insert inside the stored proc with "execute as" ? The profiler trace from both of these stored procs have identical results for the SP: StmtStarting event.
Ive created a DAL called Artist.xsd. Ive used stored procedures to access the data. The wizard created a stored procedure called 'dbo.ArtistSelectCommand' Ive granted the ASPNET account execute permissions on this stored procedure When I run the application and try to execute the stored proc, I get this error EXECUTE permission denied on object 'ArtistSelectCommand', database 'EBSNet', owner 'dbo'.
as far as im aware ive givne the ASPNET account the correct permissions
