SQL Server Performance

Why doesn't this script execute in the current database?

Discussion in 'SQL Server 2005 General DBA Questions' started by DBADave, Mar 18, 2008.

  1. DBADave New Member

    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 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.
  2. SQLDBcontrol New Member

    I've found the same problem - it's frustrating and I couldn't find an answer either.
    In the end I gave up and went back to doing things like I did in SQL 2000 - just use the old object names - like dbo.sysobjects instead of sys.objects.
    I'd be interested if there's a way around this.
  3. DBADave New Member

    I was able to get it working by using dynamic SQL.Create
    Procedure [dbo].[sp_wb_script_database_role_members]as
    Begin--Set Quoted_Identifier OFFDeclare
    @RoleName nvarchar(128),@MemberName
    nvarchar(128),@SQL
    varchar(500)Set
    @SQL = "Declare DBRole_Csr Cursor For " +"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"Exec
    (@SQL)Open
    DBRole_CsrFetch Next
    From
    DBRole_CsrInto
    @RoleName,@MemberNameWhile
    @@Fetch_Status = 0BeginSet @SQL = "sp_addrolemember @rolename = '" + @RoleName + ", @membername = '" + @MemberName + "'"
    Print @SQL
    Fetch NextFrom DBRole_Csr
    Into @RoleName,@MemberNameEnd
    Close
    DBRole_CsrDeallocate
    DBRole_Csr--Set Quoted_Identifier ONEnd
    P.S. Any idea why when I past code into this forum it places a blank line between my lines of code?
  4. FrankKalis Moderator

    Just out of curiosity, what happens when you use this code in your procedure:
    DECLARE @dbname sysname;
    DECLARE @sql nvarchar(MAX);
    SET @dbname = DB_NAME();
    SET @sql = QUOTENAME(@dbname) + N'..sp_executesql';
    EXEC @sql N'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';

Share This Page