<p>In Sql Server 2000/2005, I have a few NT user groups that need to be granted access to hundreds of stored procedures.</p>

<p>Is there a nice easy way to do that?</p>

[{'answer_id': 80340, 'author': 'Leon Bambrick', 'author_id': 49, 'author_profile': 'https://Stackoverflow.com/users/49', 'pm_score': 2, 'selected': False, 'text': '<ul>\n<li>Create a role in sql server.</li>\n<li>Write a\nscript that grants that role\npermission to use those sprocs.</li>\n<li>Add those NT user groups to that role.</li>\n</ul>\n'}, {'answer_id': 83079, 'author': 'Paul G', 'author_id': 162, 'author_profile': 'https://Stackoverflow.com/users/162', 'pm_score': 2, 'selected': True, 'text': "<p>Here's a script that I use for granting permissions to lots of procedures:</p>\n\n<pre><code>DECLARE @DB  sysname ; set @DB = DB_NAME()\nDECLARE @U  sysname ; set @U = QUOTENAME('UserID')\n\nDECLARE @ID           integer,\n        @LAST_ID     integer,\n        @NAME        varchar(1000),\n        @SQL         varchar(4000)\n\nSET @LAST_ID = 0\n\nWHILE @LAST_ID IS NOT NULL\nBEGIN\n    SELECT @ID = MIN(id)\n    FROM dbo.sysobjects\n    WHERE id &gt; @LAST_ID  AND type = 'P' AND category = 0\n\n    SET @LAST_ID = @ID\n\n    -- We have a record so go get the name\n    IF @ID IS NOT NULL\n    BEGIN\n        SELECT @NAME = name\n        FROM dbo.sysobjects\n        WHERE id = @ID\n\n        -- Build the DCL to do the GRANT\n        SET @SQL = 'GRANT EXECUTE ON ' + @NAME + ' TO ' + @U\n\n        -- Run the SQL Statement you just generated\n        EXEC master.dbo.xp_execresultset @SQL, @DB\n\n    END   \nEND\n</code></pre>\n\n<p>You can modify the select to get to a more specific group of stored procs.</p>\n"}]
['https://Stackoverflow.com/questions/80291', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/3276/']