SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Access is denied (Creating a Folder)
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 4

gsgill76
Posting Yak Master

India
137 Posts

Posted - 10/20/2006 :  04:47:40  Show Profile  Click to see gsgill76's MSN Messenger address  Send gsgill76 a Yahoo! Message  Reply with Quote
Hi, i am try to create at folder (login as 'sa', with default permission/rights)

declare @sMD varchar(100),@sPath varchar(100)
SET @sPath= '\\vsnet5\c$\newww'
SET @sMD = 'MD ' + @sPath
PRINT @sMD
EXEC xp_cmdshell @sMD

Returns:
Access is denied.

I had checked the network permissions, they are fine(i mean i can create the folder with 'MD \\vsnet5\c$\newww' command at c:\, from SQL Server)

I know some of permission/rights need to set(i SQL Server), but i dont know which of them.

Please tell me, what need to do for this to work?
Regards,
Thanks.
Gurpreet S. Gill

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 10/20/2006 :  04:56:26  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
Have you tried to create folder using same syntax from command line (command prompt) ? Is it working?

Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

gsgill76
Posting Yak Master

India
137 Posts

Posted - 10/20/2006 :  05:11:22  Show Profile  Click to see gsgill76's MSN Messenger address  Send gsgill76 a Yahoo! Message  Reply with Quote
yes, its working, at server(VSNET1) i write at command prompt:
C:\MD \\vsnet5\c$\newww
this create the folder at VSNET5 with name 'newww'

I think some of pemission/rights for the SQL Server account need to set to access the path.
Regards,
Go to Top of Page

gsgill76
Posting Yak Master

India
137 Posts

Posted - 10/20/2006 :  08:10:23  Show Profile  Click to see gsgill76's MSN Messenger address  Send gsgill76 a Yahoo! Message  Reply with Quote
Adding more

EXEC master..xp_cmdshell 'DIR \\vsnet5\c'

again throws same error
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17593 Posts

Posted - 10/20/2006 :  08:15:17  Show Profile  Reply with Quote
From BOL on xp_cmdshell
quote:

Remarks
xp_cmdshell operates synchronously. Control is not returned until the command shell command completes.

When you grant execute permissions to users, the users can execute any operating-system command at the Microsoft Windows NT® command shell that the account running Microsoft SQL Server™ has the needed privileges to execute.

By default, only members of the sysadmin fixed server role can execute this extended stored procedure. You may, however, grant other users permission to execute this stored procedure.

When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running. When the user is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account. If the proxy account is not available, xp_cmdshell will fail. This is true only for Microsoft® Windows NT® 4.0 and Windows 2000. On Windows 9.x, there is no impersonation and xp_cmdshell is always executed under the security context of the Windows 9.x user who started SQL Server.




KH

Go to Top of Page

gsgill76
Posting Yak Master

India
137 Posts

Posted - 10/30/2006 :  02:52:48  Show Profile  Click to see gsgill76's MSN Messenger address  Send gsgill76 a Yahoo! Message  Reply with Quote
Hi khtan
I understand that but how to set the rights/permission to do that?
I am using the 'sa' user, what need to change?
Regards,
Thanks.
Gurpreet S. Gill
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30176 Posts

Posted - 10/30/2006 :  02:55:06  Show Profile  Visit SwePeso's Homepage  Reply with Quote
He means the account that you use to run SQL Server, not the account you use to connect to sql server.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

gsgill76
Posting Yak Master

India
137 Posts

Posted - 10/30/2006 :  03:43:16  Show Profile  Click to see gsgill76's MSN Messenger address  Send gsgill76 a Yahoo! Message  Reply with Quote
hi Peter Larsson

still i am in fix, let me tell you, i am connected to SQL Server using SSMS(client tool only) by using login 'sa'. i am not using other login. This 'sa' login having all the default permissions/rights.

So, now what i need to further to do that?
Regards,
Thanks.
Gurpreet S. Gill
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30176 Posts

Posted - 10/30/2006 :  03:50:53  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You need to investigate how SQL Server is running!
Which account is used for SQL Server and SQL Server Agent?

Check under SERVICES...


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

gsgill76
Posting Yak Master

India
137 Posts

Posted - 10/30/2006 :  03:58:08  Show Profile  Click to see gsgill76's MSN Messenger address  Send gsgill76 a Yahoo! Message  Reply with Quote
Both SQL Server and SQL Server Agent having service account as 'Local System'
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17593 Posts

Posted - 10/30/2006 :  04:09:11  Show Profile  Reply with Quote
'Local System' account does not has rights to access network drive / resources

Create a windows account and make sure it has rights to access the \\vsnet5\c and use the a/c to startup the SQL Server Agent


KH

Go to Top of Page

gsgill76
Posting Yak Master

India
137 Posts

Posted - 10/30/2006 :  04:34:44  Show Profile  Click to see gsgill76's MSN Messenger address  Send gsgill76 a Yahoo! Message  Reply with Quote
Hi khtan

i did create the Windows Account(with Administrator rights) & set the SQL Server Agent to that account(i mean to logon to that service, using newly created account), Restart both SQL Server & SQL Server Agent services, but getting the same Respose.

EXEC xp_cmdshell 'DIR \\vsnet5\c'
OUTPUT:
Access is denied.
NULL

What else could be?

Regards,
Thanks.
Gurpreet S. Gill
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30176 Posts

Posted - 10/30/2006 :  04:37:38  Show Profile  Visit SwePeso's Homepage  Reply with Quote
on the vsnet5 server, add the account used for sql server to the security tab and allow all to this account.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17593 Posts

Posted - 10/30/2006 :  04:37:46  Show Profile  Reply with Quote
login to the Windows Server using the Windows Account. Go to command prompt and try to execute 'DIR \\vsnet5\c'.


KH

Go to Top of Page

gsgill76
Posting Yak Master

India
137 Posts

Posted - 10/30/2006 :  05:53:14  Show Profile  Click to see gsgill76's MSN Messenger address  Send gsgill76 a Yahoo! Message  Reply with Quote
Hi khtan
YES, I am able to execute 'DIR \\vsnet5\c'(logon as newly created account)

Hi Peso
I had created the new Login(using newly created account 'Rep'), now my Login is as 'VSNET5\Rep', but i am not able to login(using windows Auth. mode).

Still with same.

Regards,
Thanks.
Gurpreet S. Gill
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30176 Posts

Posted - 10/30/2006 :  05:55:41  Show Profile  Visit SwePeso's Homepage  Reply with Quote
No, no, no...
Add to the c share on the vsnet5 machine, the account used on the SQL Server. Do NOT create a new account...


Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 10/30/2006 05:58:11
Go to Top of Page

gsgill76
Posting Yak Master

India
137 Posts

Posted - 10/30/2006 :  06:02:30  Show Profile  Click to see gsgill76's MSN Messenger address  Send gsgill76 a Yahoo! Message  Reply with Quote
Hi Peter Larsson
I think i am very near to solve the problem, could you throw bit more light on this.
I have SQL Server M/C named as 'VSNET5' & login 'sa', newly created Windows account 'Rep'.
Please tell me where to add? what to Add?
Regards,
Thanks.
Gurpreet S. Gill
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30176 Posts

Posted - 10/30/2006 :  06:06:17  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Goto Windows Explorer and select the directory to where you want to export files from the SQL Server machine.
Right click and select Sharing and Security.
Add the SQL Server Windows account Rep to the tab and set permissions to Change in the security tab.
Click ok.
done.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

gsgill76
Posting Yak Master

India
137 Posts

Posted - 10/30/2006 :  06:26:10  Show Profile  Click to see gsgill76's MSN Messenger address  Send gsgill76 a Yahoo! Message  Reply with Quote
Peter Larsson
my SQL Server M/C is 'VSNET5' & i have client (SSMS) tool whos name is VSNET1. now when i write command in SSMS at Client(VSNET1) as

EXEC xp_cmdshell 'DIR \\vsnet5\c' --this is Derive at SQL Server
its working fine.

but

EXEC xp_cmdshell 'DIR \\vsnet1\c' -- this is Drive at Client(where i want to store files)
this throws the same.

Regarding the
quote:
Originally posted by Peso

Goto Windows Explorer and select the directory to where you want to export files from the SQL Server machine.
Right click and select Sharing and Security.
Add the SQL Server Windows account Rep to the tab and set permissions to Change in the security tab.
Click ok.done.
Helsingborg, Sweden



I am not able to create the share Folder, as the folder is at the client end(VSNET1) & user(Rep) is at the server(VSNET5). so now ?

Regards,
Thanks.
Gurpreet S. Gill
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30176 Posts

Posted - 10/30/2006 :  06:35:50  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Sigh.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

gsgill76
Posting Yak Master

India
137 Posts

Posted - 10/30/2006 :  06:40:19  Show Profile  Click to see gsgill76's MSN Messenger address  Send gsgill76 a Yahoo! Message  Reply with Quote
What?
quote:
Originally posted by Peso

Sigh.


Peter Larsson
Helsingborg, Sweden

Go to Top of Page
Page: of 4 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000