Re: Grant Execute right to the group of users
From: Dan Guzman (danguzman_at_nospam-earthlink.net)
Date: 05/18/04
- Next message: RobertP: "RE: Linked Server"
- Previous message: Andre: "missing indexes"
- In reply to: Q: "Grant Execute right to the group of users"
- Next in thread: Q: "Re: Grant Execute right to the group of users"
- Reply: Q: "Re: Grant Execute right to the group of users"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 17 May 2004 19:41:50 -0500
> How could I grant the execute right to all stored procedures in a database
for a group of users ?
Below is a script that will grant execute permissions to the specified role
on all user procedures in the current database.
SET NOCOUNT ON
DECLARE @GrantStatement nvarchar(4000)
DECLARE GrantStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
N'GRANT EXECUTE ON ' +
QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME) +
N' TO Users'
FROM INFORMATION_SCHEMA.ROUTINES
WHERE
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsMSShipped') = 0 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsProcedure') = 1
OPEN GrantStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM GrantStatements
INTO @GrantStatement
IF @@FETCH_STATUS = -1 BREAK
IF @@FETCH_STATUS = 0
BEGIN
RAISERROR (@GrantStatement, 0, 1) WITH NOWAIT
EXECUTE sp_ExecuteSQL @GrantStatement
END
END
CLOSE GrantStatements
DEALLOCATE GrantStatements
-- Hope this helps. Dan Guzman SQL Server MVP "Q" <anonymous@discussions.microsoft.com> wrote in message news:0AF1C2FA-F089-4967-A89A-0793BA4F3D5E@microsoft.com... > Hi all, > How could I grant the execute right to all stored procedures in a database for a group of users ? > > I create a database role called: "Users", and added the specific logins to that group. I grant them db_datareader and db_datawriter, so that they could have access to all the data. At this point, I don't know how to give them execute permission to all stored procedures in the database. > > Thank You in advance, > Q
- Next message: RobertP: "RE: Linked Server"
- Previous message: Andre: "missing indexes"
- In reply to: Q: "Grant Execute right to the group of users"
- Next in thread: Q: "Re: Grant Execute right to the group of users"
- Reply: Q: "Re: Grant Execute right to the group of users"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
