grant Permissions to all store procs at a time

Question

grant Permissions to all store procs at a time

striker-baba

SSCrazy

Points: 2170
More actions
May 19, 2010 at 9:11 pm

#140461

Hi all,
i have a problem with assigning permissions to store procs.
I want to give access to all SP's in a db to a user,
so that the user can get access to them and see from his login id.
I tried this......
In sql 2005.... i went to DB-->security-->user's and to that particular user and on rightclick and properties and ---> securables and added storeprocs and gave all types of permissions to some sp's
and Next, when we log in to the db with that particular userlogin and I am still not able to see that storeprocs in my db.
Please let me know what kind of permissions do I need to assign or what procedure do I need to follow in order to see SP's from that user login.
thanks in adv.
I added

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

That depends, do you want to give them access to modify the procs or just execute the procs?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Leo.Miller SSChampion Points: 13051 More actions · Answer 2

striker-baba (5/19/2010)
so that the user can get access to them and see from his login id.

If all you want is for them to see the meta-data, but not make changes you need to grant VIEW DEFINITION permissions

GRANT VIEW DEFINITION on [ObjectName] TO [UserName]

If they need to be able to exicute it , then:

GRANT EXECUTE on....

Leo

Striving to provide a better service.

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

imtiazl Newbie Points: 9 More actions · Answer 3

The scripts below may help you to grant your users execution rights for SPs. You will need to grant the relevant users the db_executor role.

print 'SCRIPT TO ASSIGN EXECUTE RIGHTS FOR STORED PROCEDURES'

DECLARE @SPName varchar(100)

DECLARE @cmd NVARCHAR(500)

/* CREATE A NEW ROLE */

print ''

print '1/2 ADD ROLE:'

if not exists (select 'RoleName' = name, 'RoleId' = uid, 'IsAppRole' = isapprole

from sysusers where (issqlrole = 1 or isapprole = 1) and name = 'db_executor')

begin

CREATE ROLE db_executor

print ' Role Added - db_executor'

end

else

print ' Role Exists - db_executor'

/* GRANT EXECUTE FOR EACH SP TO THE ROLE */

print ''

print '2/2 GRANT EXECUTE RIGHTS TO:'

--GRANT Execute to db_executor

DECLARE SP_csr CURSOR FOR

select name from sys.procedures where is_ms_shipped=0

/* and left(name,2) in ('gl','gs','ps','ts') */

-- uncomment the line above and amend the own filtering if you only wish to grant execute rights on certain SP masks.

order by name

OPEN SP_csr

FETCH NEXT FROM SP_csr into @SPName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @cmd = 'GRANT EXECUTE ON dbo.' + @SPName + ' TO db_executor'

EXEC (@cmd)

print ' Grant db_executor - dbo.' + @SPName

FETCH NEXT FROM SP_csr into @SPName

END

CLOSE SP_csr

DEALLOCATE SP_csr

Print ''

print 'Script Completed'

Regards

JSR_SDCA SSC-Addicted Points: 445 More actions · Answer 4

You can also do...

GRANT VIEW DEFINITION ON SCHEMA::[SchemaName] TO [User/Role];

-- or

GRANT EXECUTE ON SCHEMA::[SchemaName] TO [User/Role];