Server Roles...

[Neil Toulouse]

Hi!

Is it possible to programatically grant a user server roles temporarily so they can run a process, and then revert them back afterwards?

Specifically I need to assign 'bulkadmin' and 'sysadmin' to a user, run a stored proc, then revert them back to their original roles.

It would also have to work with both SQL2005 and SQL2008 (and maybe SQL200).

Many thanks
Neil

I like work. It fascinates me. I can sit and look at it for hours...

 

[gmmastros]

I would suggest you do a little research on "Execute AS"

Start here:

http://msdn.microsoft.com/en-us/library/ms188354.aspx

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Neil Toulouse]

Thanks George - looks the ticket!

So if I understand this correctly, I would need to create a user with the BULKADMIN and SYSADMIN roles, run the proc executing as that user, and when the proc finishes it auto reverts back to the exisiting roles (as it were)?



I like work. It fascinates me. I can sit and look at it for hours...

 

[gmmastros]

That's my understanding. I think some serious testing would be in order.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Neil Toulouse]

Cool - I will give it a go! Fortunately, we have the ideal test scenario for this

Thanks George!

I like work. It fascinates me. I can sit and look at it for hours...