April 5, 2014 at 1:38 pm
Hi,
Is it possible for non-sysadmins to run xp_create_subdir on the Express version of 2008 R2? When researching the subject I have seen talk of adding a proxy account. Can I do that with 2008 R2 Express, or does it work only on versions that include the SQL Server Agent? A point to clear instructions would be greatly appreciated.
Thank you.
April 5, 2014 at 2:42 pm
jpoole (4/5/2014)
Hi,Is it possible for non-sysadmins to run xp_create_subdir on the Express version of 2008 R2? When researching the subject I have seen talk of adding a proxy account. Can I do that with 2008 R2 Express, or does it work only on versions that include the SQL Server Agent? A point to clear instructions would be greatly appreciated.
Thank you.
I haven't tried it on xp_create_subdir, but I have tried this on other things. Create a stored procedure that takes a parameter and have that stored procedure tun xp_create_subdir. The trick is to make sure the proc is create by someone with "SA" privs, that the database is owned by "SA" (or somelogin that has "SA" privs) even if the login is disabled (and it should be if it's "SA"), and include WITH EXECUTE AS OWNER in the proc. Grant privs to execute the new proc to the group or individual that you want to run the proc and you should be all set.
I haven't tried it yet but I would also think that giving someone db_backupoperator privs on the given database would allow them to use xp_create_subdir.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 7, 2014 at 4:44 am
Hi Jeff,
Sorry to Jump in here but I was facing the same issue and thought of continuing on the thread.
I have a Stored procedure created in MAster DB which uses xp_create_subdir. Owner of master database is 'sa'.
I have included the clause, "with execute as owner" in the sp -> doesn't work with non sysadmin login/user,
Included "with execute as dbo" in the sp -> doesn't work with non sysadmin login/user.
Created a separate login 'temp' with sysadmin rights, added it to master database as well and then modified the stored procedure to "execute as 'temp' ". -> Still doesn't work
The error msg that pops up everytime is
"Error executing 'xp_create_subdir': Permission denied. User must be a member of 'sysadmin' server role."
Do you have any idea if there is any trouble using impersonation for server roles using with execute as inside stored procedures?
Thanks..
April 15, 2014 at 7:03 pm
Hi,
I tried "WITH EXECUTE AS OWNER" that but it didn't seem to work. Perhaps I didn't put it in the correct location? Here is a slimmed down version of my stored procedure:
CREATE PROCEDURE dbo.usp_Create_Directories (@rootpath varchar(100)) WITH EXECUTE AS OWNER
AS
declare
@Path varchar(100)
BEGIN
SET NOCOUNT ON;
set @Path = @rootpath + '\SubDir1'
EXEC master.dbo.xp_create_subdir @Path
set @Path = @rootpath + '\SubDir2'
EXEC master.dbo.xp_create_subdir @Path
set @Path = @rootpath + '\SubDir3'
EXEC master.dbo.xp_create_subdir @Path
END
April 16, 2014 at 6:35 am
Did you check Jeff's other recommendations? The owner / creator of the proc being SA or someone with sysadmin perms? The database being owned by SA or an account with sysadmin perms? Does this security account actually have FULL CONTROL permissions on the drive used for the directory? (If the account only has Read, it won't work and might not work even with Read/Write).
Does the account accessing the stored procedure have the EXECUTE permission on the stored procedure? Yes, the proc has EXECUTE AS OWNER, but that does the user no good if (s)he can't actually start the proc to begin with.
April 16, 2014 at 6:39 am
rollercoaster43 (4/7/2014)
Hi Jeff,Sorry to Jump in here but I was facing the same issue and thought of continuing on the thread.
I have a Stored procedure created in MAster DB which uses xp_create_subdir. Owner of master database is 'sa'.
I have included the clause, "with execute as owner" in the sp -> doesn't work with non sysadmin login/user,
Included "with execute as dbo" in the sp -> doesn't work with non sysadmin login/user.
Created a separate login 'temp' with sysadmin rights, added it to master database as well and then modified the stored procedure to "execute as 'temp' ". -> Still doesn't work
The error msg that pops up everytime is
"Error executing 'xp_create_subdir': Permission denied. User must be a member of 'sysadmin' server role."
Do you have any idea if there is any trouble using impersonation for server roles using with execute as inside stored procedures?
I answered the OP before I fully read your post. Searching your error on Google, I found this article from TechNet. Does it help resolve your problem?
April 16, 2014 at 7:56 am
Brandie Tarvin (4/16/2014)
rollercoaster43 (4/7/2014)
Hi Jeff,Sorry to Jump in here but I was facing the same issue and thought of continuing on the thread.
I have a Stored procedure created in MAster DB which uses xp_create_subdir. Owner of master database is 'sa'.
I have included the clause, "with execute as owner" in the sp -> doesn't work with non sysadmin login/user,
Included "with execute as dbo" in the sp -> doesn't work with non sysadmin login/user.
Created a separate login 'temp' with sysadmin rights, added it to master database as well and then modified the stored procedure to "execute as 'temp' ". -> Still doesn't work
The error msg that pops up everytime is
"Error executing 'xp_create_subdir': Permission denied. User must be a member of 'sysadmin' server role."
Do you have any idea if there is any trouble using impersonation for server roles using with execute as inside stored procedures?
I answered the OP before I fully read your post. Searching your error on Google, I found this article from TechNet. Does it help resolve your problem?
Heh... I wish they'd take that article down. It's one of the major reasons why xp_CmdShell has gotten such a bad recommendation.
I lost track of this thread. I'll see what I can do but not until after work tonight.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 16, 2014 at 8:01 am
Jeff Moden (4/16/2014)
Brandie Tarvin (4/16/2014)
I answered the OP before I fully read your post. Searching your error on Google, I found this article from TechNet. Does it help resolve your problem?Heh... I wish they'd take that article down. It's one of the major reasons why xp_CmdShell has gotten such a bad recommendation.
I lost track of this thread. I'll see what I can do but not until after work tonight.
Now you've got my interest piqued. Is the article technically wrong or is it just very poorly phrased?
April 16, 2014 at 9:41 pm
Brandie Tarvin (4/16/2014)
Jeff Moden (4/16/2014)
Brandie Tarvin (4/16/2014)
I answered the OP before I fully read your post. Searching your error on Google, I found this article from TechNet. Does it help resolve your problem?Heh... I wish they'd take that article down. It's one of the major reasons why xp_CmdShell has gotten such a bad recommendation.
I lost track of this thread. I'll see what I can do but not until after work tonight.
Now you've got my interest piqued. Is the article technically wrong or is it just very poorly phrased?
The title starts off as "How to enable non-sysadmin accounts to execute the xp_cmdshell ...". What's the problem with that? From BOL...
The Windows process spawned by xp_cmdshell has the same security rights as the SQL Server service account.
Do you suppose that would allow the user to see and do things that they probably shouldn't?
I don't believe that any non_SA user should have such privs. It's easy enough to make a very well controlled stored procedure that will limit what a user can do with xp_CmdShell without actually having the privs to run xp_CmdShell directly in an unfettered manner with such elevated privs.
Yes, you can limit what a non-SA user can see or do by limiting the proxy account but there's only one proxy account. Users can see each other's stuff if they use xp_CmdShell directly.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 17, 2014 at 9:00 am
That is a good point that I didn't think fully through. Thank you for the reminder.
There's got to be a better way for the users to get what they need, then. And I'm curious as to the business case for users needing to use SQL Server to create subdirectorys.
April 18, 2014 at 3:27 pm
Brandie Tarvin (4/17/2014)
There's got to be a better way for the users to get what they need, then.
There is. It's called "stored procedures".
And I'm curious as to the business case for users needing to use SQL Server to create subdirectorys.
+1000 on that thought.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 19, 2014 at 8:55 am
Hi,
Some details on the application - this system collects data from a 3D laser scanner that is imbedded in a robotic manufacturing cell. Meta data is stored in the SQL server and the binary data files (about 10 MB each) are stored as files, not in the SQL DB. Each "lot" has a file structure, something like root\year\month\lot_code\LaserData. When a lot is opened I need the SQL database to also create the directory structure so that the 3D laser scanner has a place to store its files.
I'm afraid that my SQL skills are not as advanced as I would like them to be so I appreciate your help. The server is SQL 2008 R2 Express. The TechNet article talks about using a SQL server agent proxy account - but SQL Express does not include the SQL server agent, so I guess that this option is not possible?
I'm also thinking that I'm not using "with execute as owner" correctly. When logged in as SA I can not execute the proc after adding with execute as owner - I get this error "Msg 22048, Level 16, State 1, Line 0 Error executing 'xp_create_subdir': Permission denied. User must be a member of 'sysadmin' server role." On the permissions tab of the proc's properties the owner = dbo. Is this correct, or does it need to be SA?
Thanks,
Jonathan
April 19, 2014 at 2:29 pm
jpoole (4/19/2014)
Hi,Some details on the application - this system collects data from a 3D laser scanner that is imbedded in a robotic manufacturing cell. Meta data is stored in the SQL server and the binary data files (about 10 MB each) are stored as files, not in the SQL DB. Each "lot" has a file structure, something like root\year\month\lot_code\LaserData. When a lot is opened I need the SQL database to also create the directory structure so that the 3D laser scanner has a place to store its files.
I'm afraid that my SQL skills are not as advanced as I would like them to be so I appreciate your help. The server is SQL 2008 R2 Express. The TechNet article talks about using a SQL server agent proxy account - but SQL Express does not include the SQL server agent, so I guess that this option is not possible?
I'm also thinking that I'm not using "with execute as owner" correctly. When logged in as SA I can not execute the proc after adding with execute as owner - I get this error "Msg 22048, Level 16, State 1, Line 0 Error executing 'xp_create_subdir': Permission denied. User must be a member of 'sysadmin' server role." On the permissions tab of the proc's properties the owner = dbo. Is this correct, or does it need to be SA?
Thanks,
Jonathan
If only those with "SA" privs are going to be allowed to run the proc, then you don't need WITH EXECUTE AS OWNER. If you want to allow others to run (just) the proc and the proc to have "SA" privs, then you need to use the WITH EXECUTE AS OWNER in the proc and the DATABASE must be owned by someone/something that has "SA" privs. My standard practice (there are exceptions) is that the actual "SA" login is disabled but every database is owned by "SA".
--Jeff Moden
Change is inevitable... Change for the better is not.
April 21, 2014 at 4:28 am
Regarding the error... Are you sure that the accounts you are using have FULL CONTROL on the share where you're trying to create the subdirectories?
Definitely (just for kicks and giggles) verify all SA account permissions to make sure something hasn't been disabled. Also verify you don't have a broken ownership chain going on that might prevent you from running the proc.
At my workplace we use a product called OnBase for doing exactly what you're describing. The business users don't have SQL permissions at all, but the product is hooked up to our scanner printers so that when they scan stuff in, it gets properly sorted and stored in the directory system with the use of stored procedures that create subdirectories. So what you're trying to do can be done. (That's good news. @=).
June 7, 2018 at 7:41 am
Although this post is old,
In order to solve this issue, you should make sure that your database is Trustworthy - since the SP xp_create_subdir is on different DB
You still need to set "with Execute as 'dbo'"
alter database [DBNAME] set trustworthy on
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply