Execute premmition

Hello there
I've add new user to my sql server.
AS the default he got the public role.
I add db_datawriter and db_datareader to the user.
Now the user can enter data and read data but he can't execute store
procedures.
Is there a role for executing stored procedures?
if not is that mean that i have to create my owm role and pass stored proc
one by one and allow it for the role?
any help would be usefulRoy,
There is not a predefined role for stored procedures, so yes you will need
to create one.
Of course, you could write a little bit of code to generate a GRANT for each
stored procedure in the database to your general role. (Just need to
remember when you add stored procedures to do it again.)
Russell Fields
"Roy Goldhammer" <roygoldh@.hotmail.com> wrote in message
news:uzZ9XykyDHA.3436@.tk2msftngp13.phx.gbl...

quote:

---

> Hello there
> I've add new user to my sql server.
> AS the default he got the public role.
> I add db_datawriter and db_datareader to the user.
> Now the user can enter data and read data but he can't execute store
> procedures.
> Is there a role for executing stored procedures?
> if not is that mean that i have to create my owm role and pass stored proc
> one by one and allow it for the role?
> any help would be useful
>

---

|||Thankes russell
But i don't like to work hard for this
there is probebly system store procedure that add the execution premmition
for some role.
There is also option to know which procedure is has already grant execute so
the procedure i want to build will allow me to update the role
Can you help me on it?
any help would be useful
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:#wbvU0kyDHA.3224@.tk2msftngp13.phx.gbl...

quote:

---

> Roy,
> There is not a predefined role for stored procedures, so yes you will need
> to create one.
> Of course, you could write a little bit of code to generate a GRANT for

---

each

quote:

---

> stored procedure in the database to your general role. (Just need to
> remember when you add stored procedures to do it again.)
> Russell Fields
> "Roy Goldhammer" <roygoldh@.hotmail.com> wrote in message
> news:uzZ9XykyDHA.3436@.tk2msftngp13.phx.gbl...
proc[QUOTE]
>

---

|||Here's a script like the one Russell alluded to. This will grant the
specified role execute permissions on all user stored procedures in the
current database:
DECLARE @.MyRole sysname
SET @.MyRole = 'MyRole'
DECLARE @.GrantStatement nvarchar(4000)
DECLARE GrantStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
'GRANT EXECUTE ON ' +
QUOTENAME(USER_NAME(uid)) +
'.' +
QUOTENAME(name) +
' TO ' +
@.MyRole
FROM sysobjects
WHERE
OBJECTPROPERTY(id, 'IsProcedure') = 1 AND
OBJECTPROPERTY(id, 'IsMSShipped') = 0
OPEN GrantStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM GrantStatements INTO @.GrantStatement
IF @.@.FETCH_STATUS = -1 BREAK
EXEC(@.GrantStatement)
END
CLOSE GrantStatements
DEALLOCATE GrantStatements
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Roy Goldhammer" <roygoldh@.hotmail.com> wrote in message
news:%23G2YPZlyDHA.1736@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Thankes russell
> But i don't like to work hard for this
> there is probebly system store procedure that add the execution premmition
> for some role.
> There is also option to know which procedure is has already grant execute

---

so

quote:

---

> the procedure i want to build will allow me to update the role
> Can you help me on it?
> any help would be useful
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:#wbvU0kyDHA.3224@.tk2msftngp13.phx.gbl...
need[QUOTE]
> each
> proc
>

---