EXECUTE AS 'TRMR'

Question

EXECUTE AS 'TRMR'

JMI

SSChampion

Points: 11831
More actions
July 20, 2009 at 11:41 am

#212338

I can't run a backup database script that saves a .BAK file on a network drive (H:\ drive).
So I tried this:
Execute as user = 'NA\TRMR'
BACKUP DATABASE [ReportServer]
TO DISK = N'N:\SQL Server - Backups\ReportServer\JUL20_1332_2009.bak'
I know 'NA\TRMR' account has enough permissions.
But get and error:
Msg 916, Level 14, State 1, Line 2
The server principal "NA\TRMR" is not able to access the database "ReportServer" under the current security context.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

woz-1012282 Grasshopper Points: 12 More actions · Answer 1

riga1966 (7/20/2009)

Execute as user = 'NA\TRMR'
BACKUP DATABASE [ReportServer]
TO DISK = N'C:\SQL Server - Backups\ReportServer\JUL20_1332_2009.bak'

I know 'NA\TRMR' account has enough permissions.

Just a shot in the dark here...

Maybe double check that the user is NA\TRMR and not simply TRMR

Get my meaning?

Perhaps the login is NA\TRMR and the user is TRMR?

so try Execute as user = 'TRMR'

Oh... and as another thought... do you have permission to impersonate the user? I think you have to be dbo to use "execute as user"

Sorry if this doesnt help...

Cheers

Woz

Lowell SSC Guru Points: 323518 More actions · Answer 2

Execute as user = 'NA\TRMR'
BACKUP DATABASE [ReportServer]
TO DISK = N'C:\SQL Server - Backups\ReportServer\JUL20_1332_2009.bak'

correct me i'm wrong, but you saved this script as a stored procedure, and the stored procedure was created by sa or a sysadmin login, right? once that is done, then you can grant EXECUTE permissions to the proc to someone with lower security permissions, and they could basically back up the database the way you want.

if i login as the user "bob" and try to run any script with EXECUTE AS, it will fail, right? because only a script created by a sysadmin can use execute user? to big of a security hole if i could just run any script as someone's credentials.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

JMI SSChampion Points: 11831 More actions · Answer 3

Just some clarification.

'NA\TRMR' account has all the roles: sysadmin,securityadmin,etc.

Scenario 1

----------

I am sysadmin. When I run backup script with Windows Authentication:

BACKUP DATABASE [ReportServer]
TO DISK = N'C:\SQL Server - Backups\ReportServer\JUL20_1332_2009.bak'

--------

It works

Scenario 2

----------

I run the same script but this time try to store the backup file on a network drive - "H:\"

BACKUP DATABASE [ReportServer]
TO DISK = N'H:\SQL Server - Backups\ReportServer\JUL20_1332_2009.bak'

Get error:

Cannot open backup device 'H:\Backups\ReportServer\JUL21_2009.bak'. Operating system error 3(The system cannot find the path specified.).

I checked. The path exists.

Scenario 3

----------

I logon to SQL Server box through Remote Desktop using 'NA\TRMR' account

and try to run the same script.

Get error again:

Cannot open backup device 'H:\Backups\ReportServer\JUL21_2009.bak'. Operating system error 3(The system cannot find the path specified.).

It can't see any network drives.

I think the problem is that BACKUP command works in a special security scope.

It doesn't matter who is running the command. It looks like it's running BACKUP under SQL Service account which is "Local Account". My guess is that if we change that account to "NA\TRMR" the script will work. But I can't test it. it's a critical change and I would have to coordinate it with my Team Lead.

JMI SSChampion Points: 11831 More actions · Answer 4

I did a test on my local SQL Server instance.

I changed MSSQLSERVER account to my domain account that has "WRITE" access to

the network "H:\BK" folder.

So I run this script:

BACKUP DATABASE [ReportServer]
TO DISK = N'H:\BK\JUL21_2009.bak'

and get error again!

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'H:\BK\JUL21_2009.bak'. Operating system error 3(The system cannot find the path specified.).

Now I'm really confused.

JMI SSChampion Points: 11831 More actions · Answer 5

I changed "H:\" drive to "\\tocs2001\Ope$\"

and it worked;

BACKUP DATABASE [ReportServer]
TO DISK = N'\\tocs2001\Ope$\BK\JUL21_2009.bak'

So it looks like SQL Server cannot properly see mapped drives.