4

I am working on a SSRS report to display the logins permissions in a set of databases on a specific server.

the server, the logins and the databases are all parameters. NULL shows them all (logins and DBS) server - only 1 - must be specified.

The Procedure accepts MULTIPLE parameters, which I pass to the procedure as a COMMA DELIMITED STRING. and here is where I think I am making a mistake.

the report must list the permissions only for the list of logins supplied, and list of DBs supplied.

Here is a picture of the report, collecting the parameters to be passed to the stored procedure:

enter image description here

the stored procedure is run from inside the report, as you can see below, and further below a picture with the output of my stored procedure.

exec bsp_GetServerDBRoles 
@SERVER=N'my server\instance',
@LOGINS=NULL,
@DBS='master'

enter image description here

Now, below, you can see the code of my stored procedure:

PRINT 'THE SERVER IS ' + @@SERVERNAME
--select db_name()
PRINT 'THE DATABASE IS ' + db_NAME() 
PRINT ''
GO

USE [Monitoring]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
BEGIN TRY
    PRINT  @@SERVERNAME
    PRINT  DB_NAME()
    PRINT 'PROCEDURE bsp_GetServerDBRoles'
    DROP PROCEDURE bsp_GetServerDBRoles
    print 'dropped PROCEDURE bsp_GetServerDBRoles'
END TRY
BEGIN CATCH
END CATCH
GO

CREATE PROCEDURE bsp_GetServerDBRoles
     @SERVER NVARCHAR(128),
     @LOGINS NVARCHAR(MAX)=NULL,
     @DBS NVARCHAR(MAX)=NULL
    WITH EXECUTE AS OWNER, ENCRYPTION
AS
/*
=======================================================================
Script  :   bsp_GetServerDBRoles
Author  :   Marcelo Miorelli
Date    :   5-May-2015

Desc    :   this procedure shows the database names on a specific server

            it needs @SERVER to be a linked server with enabled data access in the current server.
Usage   :  

            exec bsp_GetServerDBRoles @SERVER=N'my server/instance',@LOGINS=N'my login',@DBS=N'AppFabric'
            go

             exec bsp_GetServerDBRoles 'my server'

            SELECT * FROM OPENQUERY([my server\instsqlweb1],'SET FMTONLY OFF;SET NOCOUNT ON; exec sp_GetServerDBRoles ''UKStock,US15AUTPProduct''')
            --================================================================
            -- using a temp table to store the results
            --================================================================


            BEGIN TRY 

               DROP TABLE #Radhe

            END TRY
            BEGIN CATCH
            END CATCH



            CREATE TABLE #Radhe(
                [DB_Name] [nvarchar](128) NULL
            )

            --==================================================================================
            --this way it does not work -- An INSERT EXEC statement cannot be nested.
            -- see the links below

            --INSERT INTO #Radhe
            --exec sp_GetServerLogins
            --==================================================================================

            -- and if you get the error below:
            -- Server 'SQLSALON1\STOCKALLOCATION' is not configured for DATA ACCESS

            --select @@servername

            -- get this done first:
                --EXEC sp_serveroption @server = @@servername
                --                  ,@optname =  'data access'     
                --                  ,@optvalue =  'TRUE'

                declare @sql nvarchar(max)
                select @sql = 'SELECT 
                                        [DB_Name]
                                 FROM OPENQUERY(' + quotename(@@servername) + ',' + '''' + 'SET FMTONLY OFF; exec monitoring.dbo.bsp_GetServerDBRoles ' + '''' + '''' + 'SQLREPLON1\REP' + ''''  + '''' + '''' + ')'

                --print ( @sql)
                INSERT INTO #Radhe
                exec ( @sql)

                select
                    [DB_Name]
                from #Radhe

Links   :

=======================================================================

Vritasura talking to Indra:

SB 6.12.14: Since everything is dependent on the supreme will of the Personality of Godhead, 
            one should be equipoised in fame and defamation, 
            victory and defeat, life and death. 
            In their effects, 
            represented as happiness and distress, 
            one should maintain oneself in equilibrium, without anxiety.

SB 6.12.15: One who knows that the three qualities — goodness, passion and ignorance — 
            are not qualities of the soul but qualities of material nature, 
            and who knows that the pure soul is simply an observer of the actions and reactions of these qualities, 
            should be understood to be a liberated person. 
            He is not bound by these qualities.
=======================================================================
History
Date             Action        User                Desc
-----------------------------------------------------------------------
05-May-2015     created       Marcelo Miorelli


=======================================================================
*/
SET NOCOUNT ON
SET XACT_ABORT ON
SET DEADLOCK_PRIORITY NORMAL;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @I INT
       ,@Z INT
       ,@LOGIN nvarchar(128)

    DECLARE @sql NVARCHAR(MAX)

    DECLARE  @log NVARCHAR(MAX)
            ,@vCrlf CHAR(2);

    SELECT  @log = ''
           ,@vCrlf = CHAR(13)+CHAR(10);

SELECT @SERVER = COALESCE(UPPER(LTRIM(RTRIM(@SERVER))),@@SERVERNAME)

IF NOT EXISTS(
                SELECT * 
                  FROM SYS.SERVERS
                  WHERE NAME = @SERVER
                    AND PROVIDER = 'SQLNCLI'
                    AND is_data_access_enabled = 1
) BEGIN

           SELECT @SQL = 'The server called %s cound not be found, or does not have data access enabled.' + @vCrlf + @vCrlf +
                         'bsp_GetServerDBRoles usage:' + @vCrlf +
                         'bsp_GetServerDBRoles @Server sysname' + @vCrlf +
                         'Example:' + @vCrlf +
                         'bsp_GetServerDBRoles ''sqlreplon1\rep''' + @vCrlf

           RAISERROR(@SQL ,16,1,@SERVER)
           RETURN (-1)


END 


BEGIN TRY

            --==================================================================================

            -- and if you get the error below:
            -- Server 'SQLSALON1\STOCKALLOCATION' is not configured for DATA ACCESS

            -- get this done first:
                --EXEC sp_serveroption @server = @SERVER 
                --                  ,@optname =  'data access'     
                --                  ,@optvalue =  'TRUE'

                --DECLARE @Radhe TABLE (
                --  db nvarchar(128)          NULL,
                --  login_ nvarchar(128)      NULL,
                --  role_ nvarchar(128)       NULL,
                --  Obj nvarchar(517)         NULL,
                --  Permission nvarchar(128)  NULL,
                --  script nvarchar(588)      NULL
                --) 

                SELECT @DBS    = CASE WHEN LEN(LTRIM(RTRIM(@DBS))) < 1    THEN NULL ELSE LTRIM(RTRIM(@DBS)) END
                SELECT @LOGINS = CASE WHEN LEN(LTRIM(RTRIM(@LOGINS))) < 1 THEN NULL ELSE LTRIM(RTRIM(@LOGINS)) END

                select @sql = 'SELECT 
                                       [name]
                                      ,[Type_Desc]
                                      ,[The_Server_Roles]
                                      ,[List_of_Groups_Login_belong_to]
                                      ,[dbname]
                                      ,[modify_date]
                                      ,[Permissions_user]
                                 FROM OPENQUERY(' + quotename(@SERVER) + ','  + '''' + 'SET FMTONLY OFF;SET NOCOUNT ON; exec sp_GetServerDBRoles ' + 
                                 CASE WHEN @DBS IS NULL THEN 'NULL' 
                                                        ELSE '''' +  '''' + @DBS  + ''''  + ''''
                                  END + ',' +  
                                 CASE WHEN @LOGINS IS NULL THEN 'NULL'
                                                          ELSE  '''' + '''' + @LOGINS + ''''  + ''''
                                  END  + '''' + ')'

                print @sql

                exec ( @sql)


        RETURN (0)

END TRY
BEGIN CATCH

        PRINT '--EXCEPTION WAS CAUGHT--' + CHAR(13) +
              'THE ERROR NUMBER:' + COALESCE(CAST ( ERROR_NUMBER()  AS VARCHAR), 'NO INFO') + CHAR(13) 

        PRINT 'SEVERITY: '        + COALESCE(CAST ( ERROR_SEVERITY()  AS VARCHAR), 'NO INFO') + CHAR(13) +
              'STATE: '           + COALESCE(CAST ( ERROR_STATE() AS VARCHAR), 'NO INFO')  + CHAR(13) 

        PRINT 'PROCEDURE: '       + COALESCE(CAST ( COALESCE(ERROR_PROCEDURE(),'NO INFO')  AS VARCHAR), 'NO INFO') + CHAR(13) +
              'LINE NUMBER: '     + COALESCE(CAST ( ERROR_LINE() AS VARCHAR), 'NO INFO')  + CHAR(13) 

        PRINT 'ERROR MESSAGE: '
        PRINT  CAST ( COALESCE(ERROR_MESSAGE(),'NO INFO')   AS NTEXT)
        RETURN (-1)

END CATCH;


  ------------------------------------
  GO
  ------------------------------------
  print 'created PROCEDURE bsp_GetServerDBRoles'
GO
--  exec sys.sp_MS_marksystemobject 'bsp_GetServerDBRoles'
--GO

The problem is

when I call the stored procedure as it shows below, I get an exception:

exec bsp_GetServerDBRoles 
@SERVER=N'my server\instance'
the list of my logins'

The exception is:

--EXCEPTION WAS CAUGHT--
THE ERROR NUMBER:7357

SEVERITY: 16
STATE: 2

PROCEDURE: NO INFO
LINE NUMBER: 1

ERROR MESSAGE: 
Cannot process the object "SET FMTONLY OFF;SET NOCOUNT ON; exec sp_GetServerDBRoles 'the list of my logins ...

the procedure that the report calls live in a monitoring server called SQLMON1. From there, through LINKED SERVERS I go to the server passed as parameter and there I have a local stored procedure that returns me the data I need.

question:

Would USING A TABLE TYPE instead of the comma delimited string be a good solution for this?

I would have to create this table type in each server that I am to monitor.

appreciate any suggestions, I understand this is not an objective question.

1

The problem here is the way the SSRS parameters returns the value :

for example when you tried executing the bsp_GetServerDBRoles stored procedure(I have skipped the @Login parameter in demonstration as its too long):

 exec bsp_GetServerDBRoles 
 @SERVER=N'my server'
,@DBS=N'UK15AUTMProduct,UK15SUMMProduct,UK15SUMSProduct,UK15WINPProduct'

The expected format of values in @DBS was :

'UK15AUTMProduct','UK15SUMMProduct','UK15SUMSProduct','UK15WINPProduct'

but ssrs parameter @DBS returns the multiple values in the below format and thus the query fails:

UK15AUTMProduct,UK15SUMMProduct,UK15SUMSProduct,UK15WINPProduct

To resolve this declare a new variable in your stored procedure lets suppose @DBSNEW and set its value as :

SET @DBSNEW = REPLACE(@DBS,',',''',''')

This will result in turning the values returned from SSRS @DBS parameter as shown below:

UK15AUTMProduct','UK15SUMMProduct','UK15SUMSProduct','UK15WINPProduct

Now still we have quotes missing in the start and the end for adding them use the @DBSNEW variable as ('''+@DBSNEW+''') in where clause of your dynamic select query. Hope this helps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.