GRANTing permissions across different databases (schemas)

Question

I'm securing the DB by only allowing interaction with the DB through a series of Sprocs; pretty common fare.

I've dug up and modified a script which loops through and assigns the user EXECUTE permission for all non-system SProcs. It works a treat except that I'd ideally like to add it to the Master DB so that I can easily use it for any subsequent projects. Yes, I could save simple as a .sql file but I'd prefer it this way.

The problem is that I don't know how to dynamically refer to objects in another DB. For example, I can easily query on MyDB.dbo.INFORMATION_SCHEMA.ROUTINES, but if the DB name is dynamic (e.g. @MyDBName), how can I query the objects in this DB?

Edit: Thanks to the posters below, I now have a working solution:

USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[spGrantExec] 
@User sysname,
@DB varchar(50),
@Target varchar(50)
AS 
/*---------------------------- SQL 2005 + -------------------------------*/

SET NOCOUNT ON

-- 1 - Variable declarations
DECLARE @SQL varchar(8000)

-- 2 - Create temporary table
Set @SQL =
'USE @DB

DECLARE @MAXOID int
DECLARE @OwnerName varchar(128)
DECLARE @ObjectName varchar(128)
DECLARE @CMD1 varchar(8000)

CREATE TABLE #StoredProcedures
(OID int IDENTITY (1,1),
StoredProcOwner varchar(128) NOT NULL,
StoredProcName varchar(128) NOT NULL)

-- 3 - Populate temporary table

INSERT INTO #StoredProcedures (StoredProcOwner, StoredProcName)
SELECT ROUTINE_SCHEMA, ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_NAME LIKE ''' + @Target + '%''
 AND ROUTINE_TYPE = ''PROCEDURE''

-- 4 - Capture the @MAXOID value
SELECT @MAXOID = MAX(OID) FROM #StoredProcedures

-- 5 - WHILE loop
WHILE @MAXOID > 0
 BEGIN 

    -- 6 - Initialize the variables
    SELECT @OwnerName = StoredProcOwner,
    @ObjectName = StoredProcName
    FROM #StoredProcedures
    WHERE OID = @MAXOID

    -- 7 - Build the string

    SELECT @CMD1 = ''GRANT EXEC ON '' + ''['' + @OwnerName + '']'' + ''.'' + ''['' + @ObjectName + '']'' + '' TO @user''

    -- 8 - Execute the string
    Print @CMD1
    EXEC(@CMD1)

    -- 9 - Decrement @MAXOID
    SET @MAXOID = @MAXOID - 1
 END

-- 10 - Drop the temporary table
DROP TABLE #StoredProcedures'

Set @SQL = REPLACE(REPLACE(REPLACE(@SQL, '@DB', @DB), '@User', @User), '@Target', @Target)
--Select @SQL
--Print @SQL
Exec (@SQL)
SET NOCOUNT OFF

Answer 1

You can use the double exec technique.

In your case, instead of just:

EXEC(@CMD1)

You would have:

SET @CMD1 =
    'USE OtherDatabase;
    EXEC (''' + REPLACE(@CMD1, '''', '''''') + ''')'
EXEC(@CMD1)

Answer 2

I found another technique, which I think is cleaner:

SELECT @sql = 'CREATE VIEW ...'
SELECT @sp_executesql = quotename(@dbname) + '..sp_executesql'
EXEC @sp_executesql @sql

This relies on setting the database context by calling sp_executesql in the other database (just like one could call an SP in any database).

In your case it would be equivalent to:

SELECT @sp_executesql = quotename(@dbname) + '..sp_executesql'
EXEC @sp_executesql @CMD1