SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Why does this code not execute in current db?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

DBADave
Constraint Violating Yak Guru

USA
366 Posts

Posted - 03/19/2008 :  12:54:29  Show Profile  Reply with Quote
I am creating a stored procedure to generate the sp_addrolemember syntax, since SQL 2005 does not script role members. The stored procedure is named sp_ so it can be executed from any database. However, any reference to a view beginning with sys. only gets executed in the master database and not the current database. Why?

Try this code for a test.

Use Master

GO

Create Procedure sp_test as
Begin

Select g.name, u.name
From sys.database_principals u,
sys.database_principals g,
sys.database_role_members m
Where g.principal_id = m.role_principal_id
And u.principal_id = m.member_principal_id
And u.name <> 'dbo'
Order by g.name, u.name

End

-- Run sp_test from a database containing fixed database role members. The select only runs against master and not the current DB.

jordanam
Yak Posting Veteran

62 Posts

Posted - 03/19/2008 :  13:18:30  Show Profile  Reply with Quote
Just a thought, but is it possibly because the sp is not actually replicated into other dbs, it can merely be CALLED from anywhere else? It is literally being called to run as is, and running only against the master db.

You could wrap it up in dynamic sql and create the execution that way, and have it only run on db_name().


Edited by - jordanam on 03/19/2008 13:19:10
Go to Top of Page

DBADave
Constraint Violating Yak Guru

USA
366 Posts

Posted - 03/19/2008 :  13:24:15  Show Profile  Reply with Quote
Good timing. I just determined that at least some, if not all, objects owned by sys execute only in the current database when referenced by a user stored procedure. However, if I make the code dynamic such as SET @SQL = "Select..." Exec (@SQL) ..., then the sys objects execute in the currrent database and not the database where my stored procedure was created.
Go to Top of Page

mdubey
Posting Yak Master

USA
133 Posts

Posted - 05/27/2008 :  10:31:33  Show Profile  Reply with Quote
It is not recommened that you would have to use SP_<Procedurename>

Any stored proc startingwith SP_ will assume as a system sp. Try to create using any name.

Manoj
MCP, MCTS
Go to Top of Page

DBADave
Constraint Violating Yak Guru

USA
366 Posts

Posted - 05/27/2008 :  10:46:35  Show Profile  Reply with Quote
Hi Manoj,

In this particular case I want SQL Server to think this is a system stored procedure so it can be invoked from any database. The recommendation to avoid using sp_ is intended primarily for normal development purposes, because SQL will (1) not check the procedure cache and (2) check the master database first for any proc beginning with sp_. 99% of the time you should avoid sp_, but there are cases as a DBA where it is beneficial.

Thanks, Dave
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.53 seconds. Powered By: Snitz Forums 2000