SQL Server Performance Forum – Threads Archive
Execute as.
Is there any feature in SQL2K that I can use.Example..
execute as sysadmin sp_changeobjectowner objectname
I heard this will be available in Yukon, but I would like use any work arounds for now.
Thanks
I am assuming you don’t want to exit the current session and open a new session as Sysadmin and execute the procedure, right?<br /><br />One workaround I can think of is run xp_cmdshell and execute osql.exe that takes as input a .sql file with the commands you are interested in. To this osql.exe you will pass Admin credentials…<br /><br />Caveats with this approach:<br />– You need the Sysadmin to give the non-sysadmin user permissions to execute xp_cmdshell<br />– You are "clear-texting" sysadmin’s passsowrd as it can be seen by anyone that has<br /> access to this script<br />– Looks very cumbersome to me [<img src=’/community/emoticons/emotion-2.gif’ alt=’
‘ />]<br /><br /><br /><br />Thanks,<br />– Sri
Another workaround is to create a job which would execute under the credentials of the sql agent or sql agent proxy account (if enabled). As long as the sql agent or sql agent proxy account has the right level of permissions. Also, regarding sp_changeobjectowner from BOL:
…the db_owner fixed database role, or a member of both the db_ddladmin and db_securityadmin fixed database roles can execute sp_changeobjectowner. So if it’s just sp_changeobjectowner that you need to run, you do not have to be sysadmin. -rl
I agree with Rfl for running the process using sQLAgent privileges and ensure the account used has required privileges. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
]]>