SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stored Procedure InputParameter and SQLString


Stored Procedure InputParameter and SQLString

Author
Message
z.ushi
z.ushi
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 16
Hello sqlservercentral Smile

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
laurie-789651
laurie-789651
SSChasing Mays
SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)

Group: General Forum Members
Points: 616 Visits: 1272
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/
laurie-789651
laurie-789651
SSChasing Mays
SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)

Group: General Forum Members
Points: 616 Visits: 1272
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
z.ushi
z.ushi
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 16
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?
laurie-789651
laurie-789651
SSChasing Mays
SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)

Group: General Forum Members
Points: 616 Visits: 1272
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.
z.ushi
z.ushi
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 16
Ok, I will do so.

Thanks again Smile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search