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.
| Author |
Topic |
|
DBADave
Constraint Violating Yak Guru
366 Posts |
 Posted - 2008-03-19 : 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 MasterGOCreate Procedure sp_test as BeginSelect g.name, u.name From sys.database_principals u, sys.database_principals g, sys.database_role_members mWhere g.principal_id = m.role_principal_id And u.principal_id = m.member_principal_id And u.name <> 'dbo'Order by g.name, u.nameEnd-- 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 - 2008-03-19 : 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(). |
 |
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2008-03-19 : 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
133 Posts |
Posted - 2008-05-27 : 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.ManojMCP, MCTS |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2008-05-27 : 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 |
|
|
|
|
|
|
|
|
Why does this code not execute in current db?