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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy