May 16, 2002 at 12:14 pm
I have been looking around to find what the correct syntaxs is for using a stored procedure that I have created with sp_msforeachdb.
Is this possible?
Thanks Rich
RICHARD KIRMSS
RICHARD KIRMSS
May 16, 2002 at 12:37 pm
Read this article it should give you an idea
http://www.pinpub.com/sq/SQmag.nsf/Index/5D30F6DBB37272C1852568E2006A94B6?opendocument
quote:
I have been looking around to find what the correct syntaxs is for using a stored procedure that I have created with sp_msforeachdb.
Is this possible?
Thanks Rich
RICHARD KIRMSS
May 16, 2002 at 1:09 pm
Or here, without leaving the site!
http://www.sqlservercentral.com/columnists/bknight/sp_msforeachdb.asp
Andy
May 16, 2002 at 2:55 pm
Ok I looked at both sites and i'm still having trouble. This is what i'm trying to do. My stored procedure has 3 parameters that I pass in when I execute it. so when I try to execute my stored procedure with the three parameters inside the sp_msforeachdb, I get errors saying that it is incorrect syntax near 'Revoke': Here is what i'm doing.
exec sp_msforeachdb "sp_grant_revoke 'revoke', 'developers', 'P'"
If anyone can please help me that would be great.
Thank you Rich
RICHARD KIRMSS
RICHARD KIRMSS
May 16, 2002 at 3:02 pm
Try this
exec sp_msforeachdb 'sp_grant_revoke ''revoke'', ''developers'', ''P'''
It may not like your use of " (double quotes) but when you use ' (single quotes) you have to double singles around text values inside.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 17, 2002 at 7:18 am
ok... I tried that and I still got an error.
Server: Msg 4606, Level 16, State 1, Line 1
Granted or revoked privilege SELECT is not compatible with object.
revoke select, execute ON sp_grant_revoke_perms_roles TO developers
I have tried doing more research on this but coming up with nothing... can someone please help me.
Thanks Rich
RICHARD KIRMSS
RICHARD KIRMSS
May 17, 2002 at 8:43 am
Try just doing execute permissions.
Andy
May 17, 2002 at 9:05 am
Based on your error it looks like sp_grant_revoke tried to revoke select permissions on sp_grant_revoke_perms_roles which SPs do not have a Select permission.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 17, 2002 at 9:34 am
My stored procedure is dynamic, it creates a table with a series of select grant or select revoke depending on what is passed to it. It then goes and executes the table once it is all filled. here is the code if you want to take a look at it.
create PROC sp_grant_revoke_perms_roles
(@Grant VARCHAR (6),
@Group_Usr VARCHAR (25),
@Type VARCHAR (3)
) AS
DECLARE @Type_1 VARCHAR (1),
@Type_V VARCHAR (1),
@Type_U VARCHAR (1),
@Type_P VARCHAR (1),
@Strlen INT,
@tablename varchar(200),
@tablenameB varchar(200)
BEGIN
IF (@Grant != 'grant' and @Grant != 'revoke')
BEGIN
PRINT @Grant
RAISERROR ('Not an option, please retype with GRANT or REVOKE', 2, 1)
RETURN
END
BEGIN
select @Strlen = 1
WHILE (@Strlen <= LEN(@Type))
BEGIN
Select @Type_1 = SUBSTRING(@type, @STRLEN, 1)
IF (@Type_1 = 'U')
Select @Type_U = 'U'
ELSE
IF (@Type_1 = 'V')
Select @Type_V = 'V'
ELSE
IF (@Type_1 = 'P')
Select @Type_P = 'P'
ELSE
BEGIN
RAISERROR ('please retype third parameter with any combonation of P U or V', 3, 1)
RETURN
END
Select @Strlen = 1 + @Strlen
END
END
DECLARE
tables_cursor CURSOR
FOR
SELECT tbl_index = @GRANT + ' ' + 'select, insert, update, delete ON'+ ' ' + name + ' ' +
' TO ' + @Group_Usr
FROM sysobjects
WHERE type = @Type_V
OR type = @Type_U
GROUP BY name
DECLARE
tables_cursorB CURSOR
FOR
SELECT tbl_index = @GRANT + ' ' + 'select, execute ON'+ ' ' + name + ' ' +
' TO ' +@Group_Usr
FROM sysobjects
WHERE type = @Type_P
GROUP BY name
BEGIN
IF ((@Type_U = 'U' OR @Type_V = 'V') AND @Type_P = 'P')
BEGIN
OPEN tables_cursor
FETCH NEXT FROM tables_cursor INTO @tablename
WHILE (@@fetch_status <> -1)
BEGIN
print @tablename
exec (@tablename)
FETCH NEXT FROM tables_cursor INTO @tablename
END
CLOSE tables_cursor
DEALLOCATE tables_cursor
OPEN tables_cursorB
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
END
BEGIN
IF ((@Type_U = 'U' OR @Type_V = 'V') OR (@Type_U = 'U' AND @Type_V = 'V'))
BEGIN
OPEN tables_cursor
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
END
BEGIN
IF (@Type_P = 'P')
BEGIN
OPEN tables_cursorB
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
END
END
I then want to run this sp across all the databases, using sp_msforeachdb.
The last thing I tried was this
exec sp_msforeachdb ' "grant", "developers", "p" '
and i continue to get the same error as above.
I hope this is enough to let you know what i'm trying to do.
Rich
RICHARD KIRMSS
RICHARD KIRMSS
May 17, 2002 at 10:14 am
Change this
SELECT tbl_index = @GRANT + ' ' + 'select, execute ON'+ ' ' + name + ' ' +
to
SELECT tbl_index = @GRANT + ' ' + ' execute ON'+ ' ' + name + ' ' +
Should handle P, I believ though there is a lot of unneccessary work being done here and when I get a chance to fine tooth it I will post a slimmer version.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 17, 2002 at 11:09 am
ok....that seemed to work, which is great
but now it's not going through all the databases. do I need to use the '?' character so the SP will run in all the databases?
RICHARD KIRMSS
RICHARD KIRMSS
May 17, 2002 at 11:20 am
If you built your SP in master then do
exec sp_msforeachdb '?..sp_grant_revoke ''revoke'', ''developers'', ''P'''
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 17, 2002 at 12:33 pm
Thank you very much for helping me out. I didn't realize that the master database contains all information about other databases. I really appriciate the time you took to help me.
Rich
RICHARD KIRMSS
RICHARD KIRMSS
May 17, 2002 at 12:46 pm
Actually it doesn't, not like ytou are thinking. The reason ?..sp works is ? run the SP under the context of the database referenced first. But if the sp doesn't exist in that database it looks for the code in the master database. If found the code is executed against the current database. For the most part this works but sometimes you have to create a DB parameter to make some calls which this did not need to.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply