• I'm afraid that's because the way I sent the article to the editors.

    Here's the code in text:

    DECLARE @DBName SYSNAME

    DECLARE @SQLCmd NVARCHAR(2000)

     

    SET @DBNAME=''

     

    WHILE @DBNAME IS NOT NULL

          BEGIN

    SELECT @DBNAME=MIN(name) FROM MASTER..SYSDATABASES WHERE sid > 1 AND name>@DBNAME

                SET @SQLCmd='SELECT ''SELECT name AS [File],

                      filename as [File Name],

                      CAST(size/128.0 as DECIMAL(10,2)) AS [Size in MB],

                      CAST(FILEPROPERTY(name, ''''SpaceUsed'''')/128.0 as DECIMAL(10,2)) as [Space Used],

                      CAST(size/128.0-(FILEPROPERTY(name, ''''SpaceUsed'''')/128.0) AS DECIMAL(10,2)) AS [Available Space]

                      FROM SYSFILES'''

                --PRINT @SQLCmd

                IF @DBName IS NULL BREAK

                EXEC MASTER..XP_EXECRESULTSET @SQLCmd, @DBName

          END

    and

    DECLARE @SQL NVARCHAR(4000)

    DECLARE @USER SYSNAME

    DECLARE @DATABASE SYSNAME

    DECLARE @PERMISSION SYSNAME

     

    SET @USER='BOB' –- Replace with name of user

    SET @DATABASE='MyDB' –- Replace with database name

    SET @PERMISSION='EXECUTE' -- Replace with rights. You can use a comma separated list here. For example 'SELECT,UPDATE' if you want to modify permissions on tables

     

    SET @SQL='SELECT ''GRANT '+@PERMISSION+' ON ''+NAME+'' TO '+@USER+' ''FROM '+@DATABASE+'..SYSOBJECTS WHERE TYPE=''P''  AND NAME LIKE ''USP%'''

     

    EXEC SP_EXECUTESQL @SQL

     

    EXEC MASTER..XP_EXECRESULTSET @SQL, @DATABASE

     

    -- J.T.

    "I may not always know what I'm talking about, and you may not either."