Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Stored Procedure InputParameter and SQLString Expand / Collapse
Author
Message
Posted Wednesday, August 15, 2012 2:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 14, 2013 7:25 AM
Points: 3, Visits: 16
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
Post #1345165
Posted Wednesday, August 15, 2012 5:54 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 9:43 AM
Points: 316, Visits: 1,022
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/
Post #1345224
Posted Wednesday, August 15, 2012 8:07 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 9:43 AM
Points: 316, Visits: 1,022
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
Post #1345281
Posted Wednesday, August 15, 2012 8:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 14, 2013 7:25 AM
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?
Post #1345319
Posted Wednesday, August 15, 2012 9:09 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 9:43 AM
Points: 316, Visits: 1,022
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.
Post #1345328
Posted Wednesday, August 15, 2012 9:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 14, 2013 7:25 AM
Points: 3, Visits: 16
Ok, I will do so.

Thanks again :)
Post #1345333
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse