April 1, 2002 at 10:03 am
I developed this procedure that when executed will grant or revoke privlages to users or user groups. everything works properly however i do get this one message.... Msg 16917, Level 16, State 1
Cursor is not open
is there a way around this or not if anyone could please help me that would be great. Here is my code...
CREATE PROC dba_revoke_is_development
(@Grant VARCHAR (6),
@Group_Usr VARCHAR (25),
@Type VARCHAR (25)) AS
DECLARE @New_Type VARCHAR (1)
DECLARE @something VARCHAR (10)
DECLARE
tables_cursor CURSOR
FOR
SELECT tbl_index = @GRANT + '' + 'select, insert, update, delete ON'+ ' ' + name + ' ' +
' TO ' + @Group_Usr
FROM sysobjects
WHERE type = @New_Type
GROUP BY name
DECLARE
tables_cursorB CURSOR
FOR
SELECT tbl_index = @GRANT + '' + 'select, execute ON'+ ' ' + name + ' ' +
' TO ' +@Group_Usr
FROM sysobjects
WHERE type = @New_Type
GROUP BY name
BEGIN
IF (@Type = 'Tables')
select @New_Type = 'U'
Else
IF (@Type = 'Views')
select @New_Type = 'V'
ELSE
IF (@Type = 'Procedures')
select @New_Type = 'P'
END
BEGIN
IF (@New_Type = 'U' OR @New_Type = 'V')
OPEN tables_cursor
DECLARE @tablename varchar(200)
FETCH NEXT FROM tables_cursor INTO @tablename
WHILE (@@fetch_status <> -1)
BEGIN
print @tablename
exec (@tablename)
FETCH NEXT FROM tables_cursor INTO @tablename
END
DEALLOCATE tables_cursor
END
BEGIN
IF (@New_Type = 'P')
OPEN tables_cursorB
DECLARE @tablenameB varchar(200)
FETCH NEXT FROM tables_cursorB INTO @tablenameB
WHILE (@@fetch_status <> -1)
BEGIN
print @tablenameB
exec (@tablenameB)
FETCH NEXT FROM tables_cursorB INTO @tablenameB
END
DEALLOCATE tables_cursorB
END
RICHARD KIRMSS
RICHARD KIRMSS
April 4, 2002 at 1:19 am
richard,
you initialized @new_type too late. you should move the whole begin/end
BEGIN
IF (@Type = 'Tables')
select @New_Type = 'U'
Else
IF (@Type = 'Views')
select @New_Type = 'V'
ELSE
IF (@Type = 'Procedures')
select @New_Type = 'P'
END
to the top of your sproc.
--
-oj
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply