August 15, 2012 at 2:35 am
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
August 15, 2012 at 5:54 am
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:
August 15, 2012 at 8:07 am
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
August 15, 2012 at 8:53 am
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?
August 15, 2012 at 9:09 am
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.
August 15, 2012 at 9:16 am
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