Backup/Restore Permission Problem

Question

Backup/Restore Permission Problem

Viewing 7 posts - 16 through 21 (of 21 total)

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

Tom Powell-334692 Old Hand Points: 366 More actions · Answer 1

Have you tried writing the restore as a SQL Agent job (with any security mods you will need) and granting him authority to run jobs on that server?

[font="Comic Sans MS"]Tom Powell
http://philergia.wordpress.com/[/font]

PhilipC Hall of Fame Points: 3124 More actions · Answer 2

PhilipC

Hall of Fame

Points: 3124

June 20, 2011 at 7:08 am

#1341165

Thanks for the suggestion guys - good idea 🙂

Warren Peace SSCertifiable Points: 5881 More actions · Answer 3

PhilipC (5/12/2011)
"Only members of the sysadmin fixed server role can specify the Auto_Fix option."

Try this on the DB *AFTER* they restore it. It's not "Auto_Fix" so it might work.....

DECLARE @username varchar(25)

DECLARE fixusers CURSOR

FOR

SELECT UserName = name FROM sysusers

WHERE issqluser = 1 and (sid is not null and sid <> 0x0)

and suser_sname(sid) is null

ORDER BY name

OPEN fixusers

FETCH NEXT FROM fixusers

INTO @username

WHILE @@FETCH_STATUS = 0

BEGIN

EXEC sp_change_users_login 'update_one', @username, @username

PRINT 'EXEC sp_change_users_login ''' + 'update_one' + ''', ' + @username + ',' + @username

FETCH NEXT FROM fixusers

INTO @username

END

CLOSE fixusers

DEALLOCATE fixusers

Brandie Tarvin SSC Guru Points: 173053 More actions · Answer 4

Warren,

At the very least, the devs would need security admin perms, if not db_owner / sysadmin to use this proc.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Warren Peace SSCertifiable Points: 5881 More actions · Answer 5

True.

Then I recomend the OP package this script and do as Grasshopper sez.....

PhilipC Hall of Fame Points: 3124 More actions · Answer 6

Thanks guys, I finished up creating it as a SQL Agent job and giving the person permission to execute the job which has worked fine.

Brandie Tarvin SSC Guru Points: 173053 More actions · Answer 7

Glad it worked out.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.