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:
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'
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.
Best Answer
The problem here is the way the SSRS parameters returns the value :
for example when you tried executing the
bsp_GetServerDBRolesstored procedure(I have skipped the @Login parameter in demonstration as its too long):The expected format of values in @DBS was :
but ssrs parameter @DBS returns the multiple values in the below format and thus the query fails:
To resolve this declare a new variable in your stored procedure lets suppose
@DBSNEWand set its value as :This will result in turning the values returned from SSRS @DBS parameter as shown below:
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.