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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

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

Constraint Violating Yak Guru

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


Create Procedure sp_test as

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 <> 'dbo'
Order by,


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

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

Constraint Violating Yak Guru

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

Posting Yak Master

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.

Go to Top of Page

Constraint Violating Yak Guru

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  
 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.03 seconds. Powered By: Snitz Forums 2000