![]() ![]() I can not return any files if the caller is not a sys admin. I have tried it exactly like you listed here and using yourĪrticle. I am trying to use xp_dirtree to retrieve a list of files on another server. I have tried this to access files outside of SQL Server using a UNC path and I still can not make it work for SQL 2014. Nevertheless, I post my reply in case it can help people who find the thread. I researched his problem, but when I came around to post my reply, the post had been deleted. Provivded that the application is changeable that is.Įrland Sommarskog, SQL Server MVP, years later, someone posted a followup to this question. And create a user for the certificate.ĬREATE USER reloadcert_user FOR CERTIFICATE reloadcertĪDD SIGNATURE TO reload_sp BY CERTIFICATE reloadcertĮrland Sommarskog, SQL Server MVP, this includes access outside SQL Server, I was uncertain if the technique with certificate signing would work, but yes it does. WITH PRIVATE KEY (FILE = 'C:\temp\reloadcert.pvk',ĭECRYPTION BY PASSWORD = 'Tomorrow never knows',ĮNCRYPTION BY PASSWORD = 'A day in life')ĮXEC master.xp_cmdshell 'DEL C:\temp\reloadcert.*', 'no_output' Import the certificate we created in master into the test database.ĬREATE CERTIFICATE reloadcert FROM FILE = 'C:\temp\reloadcert.cer' Give test user right to execute the procedures. WITH PRIVATE KEY (FILE = 'C:\temp\reloadcert.pvk' ,ĮNCRYPTION BY PASSWORD = 'Tomorrow never knows',ĭECRYPTION BY PASSWORD = 'All you need is love') Grant rights for the certificate login.ĪLTER SERVER ROLE sysadmin ADD MEMBER reloadcert_loginīACKUP CERTIFICATE reloadcert TO FILE = 'C:\temp\reloadcert.cer' START_DATE = '20020101', EXPIRY_DATE = '20200101'ĬREATE LOGIN reloadcert_login FROM CERTIFICATE reloadcert WITH SUBJECT = 'For bulk-load privileges', The script below is an adaption on one the scripts in the article, which explains some names.ĬREATE LOGIN testuser WITH PASSWORD = 'CeRT=0=TeST'ĮNCRYPTION BY PASSWORD = 'All you need is love' (Normally I rely on EXECUTE AS LOGIN only for testing permissions.) That worked.įor more details on the technique as such, see this article on my web site: I also tested this by logging in directly as testuser. ![]() The script below shows this in a little more details. You create a login from that certificate, and make that login member of sysadmin. The technique is this: wrap the call xp_subdirs in a stored procedure, which you sign with a certificate. Provivded that the application is changeable that is. Since this includes access outside SQL Server, I was uncertain if the technique with certificate signing would work, but yes it does. returns folder data) without the sa membership) (for info, the procedure xp_dirtree works as expected (i.e. This is causing me a problem with a application that uses this procedure to enumerate folders and needs to run as a non-sysadmin.Ĭan anyone explain this behaviour or suggest any workaround / fix (other than making the user a sysadmin -)) Obviously if I add the user as a sysadmin the list of folders is returned. I have a requirement to run this as a non-sysadmin user, I have granted the user EXECUTE permission to the procedure and the user also has EXECUTE to the procedure xp_availablemedia and is a member of the diskadmin role.Īs a sysadmin user I get the list of root folders on the D drive returned as expected, however when I execute the same as the non-sysadmin user (with the above permissions) I get the message 'Command(s) completed successfully' but no I have a query regarding the undocumented extended stored procedure 'xp_subdirs'. ![]()
0 Comments
Leave a Reply. |