Best way to create a sql server role

I have an application that connects to SQL using SQL security. I would like
to give this user the permission to select data from all user tables and
execute all stored procedures.
I have accomplished this by creating a role and manually assigning the
"execute" permission to every stored proc to this role. Then I added the use
r
to that role. I have also assigned the user the db_datawriter and
db_datareader permissions.
My question is this - what happens when a new stored proc is added to the
system? Do I have to manually give that role execute rights to the new proc?
Is there a better way to accomplish this. To sumarize the requirements, the
user should be able to execute all stored procs and select data from any
table. Thanks!
- JohnnyHi
I hope you have already denied EXECUTE permission to public role.
You can generate dyamic script to grant an execute permissions and when a
new SP is added just reran the script and it will added to the role .
"JohnnyMagz" <JohnnyMagz@.discussions.microsoft.com> wrote in message
news:0346DA03-0106-47A7-937B-CFA32A5E345C@.microsoft.com...
>I have an application that connects to SQL using SQL security. I would like
> to give this user the permission to select data from all user tables and
> execute all stored procedures.
> I have accomplished this by creating a role and manually assigning the
> "execute" permission to every stored proc to this role. Then I added the
> user
> to that role. I have also assigned the user the db_datawriter and
> db_datareader permissions.
> My question is this - what happens when a new stored proc is added to the
> system? Do I have to manually give that role execute rights to the new
> proc?
> Is there a better way to accomplish this. To sumarize the requirements,
> the
> user should be able to execute all stored procs and select data from any
> table. Thanks!
> - Johnny|||Below is a script like the one Uri mentioned. Just change 'SpExecuteRule'
to your role(s).
--grant
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 SpExecuteRole'
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
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
"JohnnyMagz" <JohnnyMagz@.discussions.microsoft.com> wrote in message
news:0346DA03-0106-47A7-937B-CFA32A5E345C@.microsoft.com...
>I have an application that connects to SQL using SQL security. I would like
> to give this user the permission to select data from all user tables and
> execute all stored procedures.
> I have accomplished this by creating a role and manually assigning the
> "execute" permission to every stored proc to this role. Then I added the
> user
> to that role. I have also assigned the user the db_datawriter and
> db_datareader permissions.
> My question is this - what happens when a new stored proc is added to the
> system? Do I have to manually give that role execute rights to the new
> proc?
> Is there a better way to accomplish this. To sumarize the requirements,
> the
> user should be able to execute all stored procs and select data from any
> table. Thanks!
> - Johnny