Stored Procedure InputParameter and SQLString

  • Hello sqlservercentral 🙂

    I created a stored procedure to to grant/revoke permissions a bit more comfortable.

    (found most of it in the net to be precise^^ http://www.mssqltips.com/sqlservertip/1593/granting-view-definition-permission-to-a-user-or-role-in-sql-server/)

    This works:

    USE Sandbox

    GO

    /****** Object: StoredProcedure [dbo].[Permissions] Script Date: 08/07/2012 11:47:20 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    IF OBJECT_ID ('Permissions') IS NOT NULL

    DROP PROCEDURE Permissions

    GO

    CREATE PROCEDURE [dbo].[Permissions] (@command nvarchar(100), @user nvarchar(100))

    --

    AS

    BEGIN

    SET NOCOUNT ON;

    CREATE TABLE #tmpTab

    (

    Kju varchar(500) NOT NULL

    )

    INSERT INTO #tmpTab (Kju)

    SELECT @command + ' ON ' + schema_name(schema_id) + '.' + [name] + ' TO ' + @user

    FROM sys.objects

    WHERE sys.objects.type IN ('P', 'U')

    AND is_ms_shipped = 0;

    SELECT @command = Kju FROM #tmpTab

    SELECT * FROM #tmpTab;

    DECLARE @x nvarchar(400)

    DECLARE cursorName CURSOR

    FOR Select Kju FROM #tmpTab

    OPEN cursorName

    FETCH NEXT FROM cursorName INTO @x

    WHILE @@FETCH_STATUS = 0

    BEGIN

    EXEC sp_executesql @x

    PRINT @x

    FETCH NEXT FROM cursorName

    INTO @x

    END

    CLOSE cursorName

    DEALLOCATE cursorName

    END

    Executing the procedure works for example like:

    EXECUTE Permissions 'GRANT VIEW DEFINITION', 'guest'

    Now I would like to include the objectdatatypes as InputParameters

    this is not working as i figured out (but why)?

    declare @par nvarchar (10)

    set @par='''U'''

    SELECT* FROM sys.objects

    WHERE sys.objects.type IN (@par)

    so I tried to put it in an sqlString

    (only listing the rows i changed, hope thats ok):

    CREATE PROCEDURE [dbo].[Permissions] (@command nvarchar(100), @par nvarchar(100), @user nvarchar(100))

    DECLARE @commandString nvarchar(400)

    SET @commandString =

    'INSERT INTO #tmpTab (Kju) ''SELECT ' +@command + SCHEMA_NAME(schema_id).[name] + ' TO ' +@user+

    ' FROM sys.objects

    WHERE sys.objects.type IN ( '+@par+' ) AND is_ms_shipped = 0;'''

    EXEC sp_executesql @commandstring

    EXECUTE Permissions 'GRANT VIEW DEFINITION','''P'',''U''','guest'

    Problems are: @commandString is written in #tmpTab, but only once. To boot, schema_name... is not chosen dynamically, it gives me "schema_name..." instead of [dbo].filename or whatever would be appropriate.

    In the first example @command is written as many times into #tmpTab as databaseobjects are used (P, U), one row for each object. This should also happen here, so the Cursor can fetch row after row.

    I am sure the '''' are not ok, but maybe there is more to do.

    Thanks a lot in advance.

    z.ushi

  • I'm not sure why you've got 3 quotes each side of 'U'. You only need 1 quote - then it works:

    declare @par nvarchar (10)

    set @par='U'

    SELECT* FROM sys.objects

    WHERE sys.objects.type IN (@par)

    If you want to pass parameters like 'U,P' you're getting into string-splitter land, & it's a lot more complicated. See link:

    http://www.sqlservercentral.com/articles/Tally+Table/72993/

  • Having said that ----

    This works - d/k if it does exactly what you want:

    DECLARE @command nvarchar(100)

    DECLARE @par nvarchar(100)

    DECLARE @user nvarchar(100)

    DECLARE @commandString nvarchar(400)

    SET @command = 'GRANT VIEW DEFINITION'

    SET @par = '''P'',''U'''

    SET @user = 'guest'

    CREATE TABLE #tmptab (Kju varchar(max));

    SET @commandString =

    'INSERT INTO #tmpTab SELECT ''' +@command + ' '' + SCHEMA_NAME(schema_id) + ''.'' + [name] + '' TO ' +@user+

    ''' FROM sys.objects

    WHERE [type] IN ( '+@par+' ) AND is_ms_shipped = 0;'

    PRINT @commandString

    EXEC sp_executesql @commandstring

    By the way, I wouldn't call the sp 'Permissions', as SQL is colouring it, so it may cause a problem. Best to call it something distinctive & unique.

    MS also recommend using the schema names too e.g. EXEC dbo.xxxxxxxxxxxx

  • Hello laurie,

    thank you very much for your advice!

    TallyTable is really a nice thing to know and use.

    Just tried your corrected code

    The 'ON' got lost in my 2nd example, i included it like

    SET @commandString =

    'INSERT INTO #tmpTab SELECT ''' +@command + ' ON '' + SCHEMA_NAME(schema_id) + ''.'' + [name] + '' TO ' +@user+

    ''' FROM sys.objects

    WHERE [type] IN ( '+@par+' ) AND is_ms_shipped = 0;'

    now it does exactly what I want!

    The procedure is not called "Permissions" in original (it is called "Rechte")..I just called it that way in the forum to have everything in english to keep the flow for everyone, but still its good you tell me.

    schema-names you mean to add this [dbo] in front of every object?

  • Good to hear it's working!

    schema-names you mean to add this [dbo] in front of every object?

    That's right. MS might make this compulsory in the future, so it's good to do it from now on. Many people only have a [dbo] schema, but more complex databases can have several different - so you need to use it then anyway.

  • Ok, I will do so.

    Thanks again 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply