Grant execute permission for a user on all stored procedures in database

Question

I generated script from old database  created a new database and imported all data from old database  So far so good  however  no user has execute rights for stored procedures  I know I can use   GRANT EXECUTE ON  storedProcName  TO  userName     If it was just a few procedures  however  I have about 100 so what s the easiest way for me to grant execute access for a specific user to all of them   Thanks in advance

User · Accepted Answer

Create a role add this role to users  and then you can grant execute to all the routines in one shot to this role   CREATE ROLE  lt abc gt  GRANT EXECUTE TO  lt abc gt    EDIT This works in SQL Server 2005  I m not sure about backward compatibility of this feature  I m sure anything later than 2005 should be fine

User · Answer

Without over-complicating the problem  to grant the EXECUTE on chosen database   USE  DB  GRANT EXEC TO  User Name

User · Answer

use below code   change proper database name and user name and then take that output and execute in SSMS  FOR SQL 2005 ABOVE   USE  lt database name gt   select  GRANT EXECUTE ON    name    TO  userName       from sys objects   where type   P   and is ms shipped   0

User · Answer

USE  DATABASE   DECLARE  USERNAME VARCHAR 500   DECLARE  STRSQL NVARCHAR MAX   SET  USERNAME   USERNAME    SET  STRSQL     select  STRSQL  CHAR 13   GRANT EXECUTE ON     s name       obj name    TO   USERNAME     from     sys all objects as obj inner join     sys schemas s ON obj schema id   s schema id where obj type in   P   V   FK   AND s NAME NOT IN   SYS   INFORMATION SCHEMA     EXEC SP EXECUTESQL  STRSQL

User · Answer

This is a solution that means that as you add new stored procedures to the schema  users can execute them without having to call grant execute on the new stored procedure   IF  EXISTS  SELECT   FROM sys database principals WHERE name   N asp net   DROP USER asp net GO  IF  EXISTS  SELECT   FROM sys database principals  WHERE name   N db execproc  AND type    R   DROP ROLE  db execproc  GO  --Create a database role     CREATE ROLE  db execproc  AUTHORIZATION  dbo  GO  --   with EXECUTE permission at the schema level    GRANT EXECUTE ON SCHEMA  dbo TO db execproc  GO  --http   www patrickkeisler com 2012 10 grant-execute-permission-on-all-stored html --Any stored procedures that are created in the dbo schema can be  --executed by users who are members of the db execproc database role  --   add a user e g  for the NETWORK SERVICE login that asp net uses CREATE USER asp net  FOR LOGIN  NT AUTHORITY NETWORK SERVICE   WITH DEFAULT SCHEMA  dbo  GO  --   and add them to the roles you need EXEC sp addrolemember N db execproc    asp net   EXEC sp addrolemember N db datareader    asp net   EXEC sp addrolemember N db datawriter    asp net   GO   Reference  Grant Execute Permission on All Stored Procedures

[sql] Grant execute permission for a user on all stored procedures in database?

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2000