| Author |
 Topic  |
|
|
DBADave
Constraint Violating Yak Guru
USA
366 Posts |
 Posted - 03/19/2008 : 12:54:29
|
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
|
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 |
 |
|
|
DBADave
Constraint Violating Yak Guru
USA
366 Posts |
Posted - 03/19/2008 : 13:24:15
|
| 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. |
|
|
|
mdubey
Posting Yak Master
USA
133 Posts |
Posted - 05/27/2008 : 10:31:33
|
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 |
|
|
|
DBADave
Constraint Violating Yak Guru
USA
366 Posts |
Posted - 05/27/2008 : 10:46:35
|
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 |
|
|
| |
Topic |
|
Why does this code not execute in current db?
