Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Access is denied (Creating a Folder)

Author  Topic 

gsgill76
Posting Yak Master

137 Posts

Posted - 2006-10-20 : 04:47:40
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
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-20 : 04:56:26
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

137 Posts

Posted - 2006-10-20 : 05:11:22
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

137 Posts

Posted - 2006-10-20 : 08:10:23
Adding more

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

again throws same error
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-20 : 08:15:17
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

137 Posts

Posted - 2006-10-30 : 02:52:48
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

30421 Posts

Posted - 2006-10-30 : 02:55:06
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

137 Posts

Posted - 2006-10-30 : 03:43:16
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

30421 Posts

Posted - 2006-10-30 : 03:50:53
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

137 Posts

Posted - 2006-10-30 : 03:58:08
Both SQL Server and SQL Server Agent having service account as 'Local System'
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-30 : 04:09:11
'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

137 Posts

Posted - 2006-10-30 : 04:34:44
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

30421 Posts

Posted - 2006-10-30 : 04:37:38
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)

17689 Posts

Posted - 2006-10-30 : 04:37:46
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

137 Posts

Posted - 2006-10-30 : 05:53:14
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

30421 Posts

Posted - 2006-10-30 : 05:55:41
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
Go to Top of Page

gsgill76
Posting Yak Master

137 Posts

Posted - 2006-10-30 : 06:02:30
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

30421 Posts

Posted - 2006-10-30 : 06:06:17
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

137 Posts

Posted - 2006-10-30 : 06:26:10
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

30421 Posts

Posted - 2006-10-30 : 06:35:50
Sigh.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

gsgill76
Posting Yak Master

137 Posts

Posted - 2006-10-30 : 06:40:19
What?
quote:
Originally posted by Peso

Sigh.


Peter Larsson
Helsingborg, Sweden

Go to Top of Page
    Next Page

- Advertisement -