Showing posts with label db_datawriter. Show all posts
Showing posts with label db_datawriter. Show all posts

Sunday, February 26, 2012

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
>

Friday, February 24, 2012

Execute Permission

Hi,
How do I give user execute permission on the complete
db. I gave them db_datareader, db_datawriter and
db_ddladmin and they still get access denied. Is making
them dbo the only way? I don't realy want to give them
permission one sproc at a time
thanks in advance<anonymous@.discussions.microsoft.com> wrote in message
news:4bed01c3e438$70ad8f70$a601280a@.phx.gbl...
quote:

> How do I give user execute permission on the complete
> db. I gave them db_datareader, db_datawriter and
> db_ddladmin and they still get access denied. Is making
> them dbo the only way? I don't realy want to give them
> permission one sproc at a time

Basically you need to issue the
GRANT EXEC ON <sp_name> TO <username>
For each stored procedure, a cursor could be used to enumerate all tables.
Steve|||Hi,
Use the below script from Query analyzer, replace dbname with actual dbname
and username with actual user name
use dbname
go
declare @.x varchar(8000)
set @.x=''
select @.x = @.x + ' grant exec on ' + name+ ' to username'
from sysobjects where type='p'
exec (@.x)
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:4bed01c3e438$70ad8f70$a601280a@.phx.gbl...
quote:

> Hi,
> How do I give user execute permission on the complete
> db. I gave them db_datareader, db_datawriter and
> db_ddladmin and they still get access denied. Is making
> them dbo the only way? I don't realy want to give them
> permission one sproc at a time
> thanks in advance