1

Topic: Error at creation in a full-sphere

Good afternoon, colleagues.
The help with resolution of a problem with creation of backup copies in network directories is necessary. A situation following:
SQL by the machine the computer on which SQL, has the rights to a full-sphere the full works under NT Service.
On SQL at start it is launched  which does . And so - at its automatic start an error if I launch manually - all well.
Help to resolve a problem.

2

Re: Error at creation in a full-sphere

Automatic start I do with the help sp_procoption. The agent is not present, SQL Server Express 2008 R2

3

Re: Error at creation in a full-sphere

At adding ' select suser_name () ' shows ' sa '.
However, here such nuances:
If I change a call context  on sa fulfills normally. If I produce a command ' execute as user = ' dbo ", procedure on grew ' select suser_name () ' shows ' sa ', but the directory is already inaccessible.

4

Re: Error at creation in a full-sphere

What way to full-spheres use?
The rights to a full-sphere are at ? I do not know as with NT Service, to launch a SQL Server from domain Ouse easier and the rights to a full-sphere to it to produce.

5

Re: Error at creation in a full-sphere

The computer with SQL: Moscow time-pc1
Full-sphere: \\Moscow time-pc2\backup
The computer has Moscow time-pc1 rights to reading and change to a full-sphere \\Moscow time-pc2\backup

6

Re: Error at creation in a full-sphere

gerogekochkin;
At the computer of the right to record can be. But writes that  not the computer.
also think - who  is engaged?

7

Re: Error at creation in a full-sphere

, and so who at us on dba.stackexchange is George K !
Badly, a companion to hold back the certain facts.
At it not  breaks off, and Ola Hallengren-skaja ;
With  :

wrote:

Msg 50000, Level 16, State 1, Procedure DatabaseBackup, Line 786 [Batch Start Line 0] The directory \*** does not exist

Guess, what it for a line such in procedure?
And it is check of existence of a way on a full-sphere, and check by means of not documented

xp_fileexist

And at this  known issue with check on a full-sphere when under the system administrator it is fulfilled.
But it is pleasant to me, as something dobavljaetsja/disappears depending on target audience.
Here the question original:
Ola Hallengren DatabaseBackup error on locating Windows directory

8

Re: Error at creation in a full-sphere

Yes and here target audience???!!!
And what about here this nuance? -
If I change a call context  on sa fulfills normally. If I produce a command ' execute as user = ' dbo ", procedure on grew ' select suser_name () ' shows ' sa ', but the directory is already inaccessible.

9

Re: Error at creation in a full-sphere

gerogekochkin wrote:

yes and here target audience???!!!

Thus that at that forum it is thrown out both an error, and a context, and on ours - continuous deception

gerogekochkin wrote:

and what about here this nuance? -
If I change a call context  on sa fulfills normally. If I produce a command ' execute as user = ' dbo ", procedure on grew ' select suser_name () ' shows ' sa ', but the directory is already inaccessible.

And here all is simple, it is necessary to read the documentation.
Does not happen with execute as login , is only execute as user .
And why?
That is why that if only basis not , you restricted yourselves, writing execute as user
Now you only the USER in a procedure context, and never login.
And that to you produces suser_name () = sa;
So it because  your basis sa.
Here it also produces.
And make now

select * from sys.fn_my_permissions (null, ' server ')

Also be convinced that no server rights and in  are present

10

Re: Error at creation in a full-sphere

All right, a companion, do not take offense.
Do not do procedure with with execute as ' dbo '.
Write inside procedures

execute as login = ' sa'

And here it already will be present sa.
EXECUTE AS CLAUSE <> EXECUTE AS STATEMENT

11

Re: Error at creation in a full-sphere

o-o;
Thanks.
Light spilled.
The test transits tomorrow)

12

Re: Error at creation in a full-sphere

gerogekochkin;
There such business still... If the executor has no procedures (login)  on sa;
It receives about it an error.

13

Re: Error at creation in a full-sphere

And the rights that as a result should be given the machine with SQl Server', truly?

14

Re: Error at creation in a full-sphere

If the server walks under Network Service, yes, to an account of a computer, which with dollar on a tail.
Then the executor-system administrator will check way presence on a full-sphere under a service account.
If launches  it is right it is necessary to produce an uchetke-proxy, which for xp_cmdshell.
Here once fought with it:
network file Check on existence
I, by the way, just had problems if role login-member sysadmin launched.
Can, well it generally , not documentary procedure xp_fileexists?
The main thing that  instead of that any xp_fileexists found a way

15

Re: Error at creation in a full-sphere

Second, at you under whom service is launched, under NT AUTHORITY\LOCAL SERVICE or under NT AUTHORITY\NETWORK SERVICE?
The first in a network does not walk generally.
My answer for the second, for NT AUTHORITY \ NETWORK SERVICE .
Here it on a network walks from computer name

16

Re: Error at creation in a full-sphere

Yes, a variant. And can  sa to credential?

17

Re: Error at creation in a full-sphere

gerogekochkin wrote:

yes, a variant. And can  sa to credential?

In sense?
In sp_xp_cmdshell_proxy_account    an account

18

Re: Error at creation in a full-sphere

o-o wrote:

second, at you under whom service is launched, under NT AUTHORITY\LOCAL SERVICE or under NT AUTHORITY\NETWORK SERVICE?
The first in a network does not walk generally.
My answer for the second, for NT AUTHORITY \ NETWORK SERVICE .
Here it on a network walks from computer name

All is true, under NT AUTHORITY\NETWORK SERVICE

19

Re: Error at creation in a full-sphere

o-o;
But an error all the same. I changed procedure, as advised:

ALTER PROC [dbo]. [sp_backup]
AS
BEGIN
execute as login ='sa'
***
EXECUTE master.dbo. DatabaseBackup @databases = @dbs, @Directory = @backupLocation, @BackupType = ' FULL ', @Verify = ' Y ', @CleanupTime = 72, @CheckSum = ' Y ', @LogToTable = ' Y ';
END

20

Re: Error at creation in a full-sphere

gerogekochkin wrote:

o-o;
But an error all the same. I changed procedure, as advised:

ALTER PROC [dbo]. [sp_backup]
AS
BEGIN
execute as login ='sa'
***
EXECUTE master.dbo. DatabaseBackup @databases = @dbs, @Directory = @backupLocation, @BackupType = ' FULL ', @Verify = ' Y ', @CleanupTime = 72, @CheckSum = ' Y ', @LogToTable = ' Y ';
END

The sufferer;
1.  always executes the server and always on behalf of the . Though, at  mysterious master.dbo. DatabaseBackup...
2. AXES  on "execute as login ='sa '". And  AXES -  doubly.

21

Re: Error at creation in a full-sphere

aleks222 wrote:

it is passed...
The sufferer;
1.  always executes the server and always on behalf of the . Though, at  mysterious master.dbo. DatabaseBackup...
2. AXES  on "execute as login ='sa '". And  AXES -  doubly.

Here if you, , read all post;
That also would learn that at it not an error  , and an error not documented xp_fileexists .
She does not see a way when it is executed not under the administrator.
And mysterious DatabaseBackup it  Ola Hallengren,
About what it was written above, it climbs  xp_fileexists and falls with an error.
And it was already offered to it to spit on this xp_fileexists and  to itself it is quiet;
Just because  just all the same, the system administrator or not, is always used  servers

22

Re: Error at creation in a full-sphere

When something does not plow;
And at center of this idle something not documented;
I.e. it also is not known, that wants;
And the claim not to show;
That on me so it is a high time to refuse this .
Well if so it would be desirable  to use that;
Simply comment out a piece with usage xp_fileexists.

23

Re: Error at creation in a full-sphere

o-o wrote:

when something does not plow;
And at center of this idle something not documented;
I.e. it also is not known, that wants;
And the claim not to show;
That on me so it is a high time to refuse this .
Well if so it would be desirable  to use that;
Simply comment out a piece with usage xp_fileexists.

Commented out the unit, works!
Thanks once again!