Hello,
I am using the following to create the text for granting permissions to
stored procedures:
USE Train
SELECT 'GRANT EXECUTE ON '+ name + ' TO Web_Publish'
FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
'%web_%')
Is it possible to actually grant permissions by using an SQL statement like
the following?
GRANT EXECUTE ON
SELECT name
FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
'%web_%')
TO Web_Publish
--
Thanks in advance,
StevenHi
DECLARE @.proc_name SYSNAME
DECLARE @.sql VARCHAR(4000)
SET @.proc_name = ''
WHILE 1=1
BEGIN
SET @.proc_name = (SELECT TOP 1 ROUTINE_NAME FROM
INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'IsMSShipped') = 0
-- Only user stored procedures
AND ROUTINE_TYPE = 'Procedure'
AND ROUTINE_NAME > @.proc_name
ORDER BY ROUTINE_NAME
)
IF @.proc_name IS NULL BREAK
SET @.sql = 'GRANT EXECUTE ON ' + QUOTENAME(@.proc_name) + ' TO MyUser'
EXEC (@.sql)
END
"Steven K0" <stroy@.api.com> wrote in message
news:%23KrhfbRNGHA.3944@.tk2msftngp13.phx.gbl...
> Hello,
> I am using the following to create the text for granting permissions to
> stored procedures:
> USE Train
> SELECT 'GRANT EXECUTE ON '+ name + ' TO Web_Publish'
> FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
> '%web_%')
> Is it possible to actually grant permissions by using an SQL statement
> like the following?
> GRANT EXECUTE ON
> SELECT name
> FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
> '%web_%')
> TO Web_Publish
> --
> Thanks in advance,
> Steven
>
>sql
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment