SQL Server Performance Forum – Threads Archive

execute permission

I have a database with about 900 stored procedures. I need to give execute permission to all those procedures for a new login. Is there a way to do that besides going to one procedure at a time? Rinu

if this task is one time only then you can use GRANT to give permission to specific user. else create a database roles, assign sp permissions to it..then add users in the role. — You can find list of SP using: –
select name from sysobjects where xtype=’P’ and name not like ‘dt_%’ order by name
BOL has detailed info about:-
GRANT
Creates an entry in the security system that allows a user in the current database to work with data in the current database or execute specific Transact-SQL statements.
Roles
Roles are a powerful tool that allow you to collect users into a single unit against which you can apply permissions. Permissions granted to, denied to, or revoked from a role also apply to any members of the role. You can establish a role that represents a job performed by a class of workers in your organization and grant the appropriate permissions to that role. As workers rotate into the job, you simply add them as a member of the role; as they rotate out of the job, remove them from the role. You do not have to repeatedly grant, deny, and revoke permissions to or from each person as they accept or leave the job. The permissions are applied automatically when the users become members of the role. Deepak Kumar –This posting is provided â€œAS ISâ€ with NO rights for the sake of knowledge sharing.

Another way ight be to use
select ‘GRANT EXECUTE ON ‘ + name +’ TO Whomeveryouwant’ from sysobjects where xtype=’P’ and name not like ‘dt_%’ order by name Copy the results back into QA and run this. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Hi Frank
In the query which you have listed above it does not work in my case where userid is 21User.
i tried the following but it throws me error.If the userid starts with a charachter it executes properly but when i have a user with prefix as a int it throws error.
Thank you GRANT EXECUTE ON p_UpdatePersonalEmail TO 21User
error
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ’21’.

EasyAnswer here – add square brackets around the login name. Better answer: change the login name – you really shouldn’t start any ‘name’ in a database with a number, it’s begging trouble.

Thanks Adrian
That worked,I understand there should be no user name starting with int.
They have been using it since ,i need to change the user name and its dependencies,
Thanks again

]]>