can not run stored proceedure as dbo

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

can not run stored proceedure as dbo

Author

Topic

rockie12_us
Starting Member

1 Post

Posted - 2010-05-10 : 09:10:36

Hi all
I have a stored proceedure in my ms sql database. When it runs on the scheduled time, it runs fine. When I log in to run it... it creates and updates tables with my schema.tablename instead of the dbo.tablename.

Question...
How do I login and run the stored proceedure so it runs as dbo instead of me?

Thanks

Dean-O

Kristen
Test

22859 Posts

Posted - 2010-05-10 : 09:15:29

The SProc could be written to explicitly reference the tables prefixed by "dbo." (but then will never be able to execute on "MySchema." tables)

i.e.

SELECT * FROM MyTable

will use the user's schema (if a table exists for that schema, otherwise it will use dbo schema's table)

SELECT * FROM dbo.MyTable

will only reference MyTable in the "dbo" schema.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-10 : 13:12:20

and obviously you need to have UPDATE rights on dbo table for that (which i hope you do have!)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources