<p>What's the best way to implement a SQL script that will grant select, references, insert, update, and delete permissions to a database role on all the user tables in a database?</p>

<p>Ideally, this script could be run multiple times, as new tables were added to the database. SQL Server Management Studio generates scripts for individual database objects, but I'm looking for more of a "fire-and-forget" script.</p>

[{'answer_id': 44841, 'author': 'Dr Zimmerman', 'author_id': 4605, 'author_profile': 'https://Stackoverflow.com/users/4605', 'pm_score': 2, 'selected': False, 'text': "<p>I'm sure there is an easier way, but you could loop through the sysobjects table in the database and grant permissions to any user table objects that exist.  You could then run that multiple times whenever new tables are added.</p>\n"}, {'answer_id': 45907, 'author': 'ninesided', 'author_id': 1030, 'author_profile': 'https://Stackoverflow.com/users/1030', 'pm_score': 2, 'selected': True, 'text': "<p>Dr Zimmerman is on the right track here. I'd be looking to write a stored procedure that has a cursor looping through user objects using execute immediate to affect the grant. Something like this:</p>\n\n<pre><code> IF EXISTS (\n    SELECT 1 FROM sysobjects\n    WHERE name = 'sp_grantastic'\n    AND type = 'P'\n)\nDROP PROCEDURE sp_grantastic\nGO\nCREATE PROCEDURE sp_grantastic\nAS\nDECLARE\n @object_name VARCHAR(30)\n,@time       VARCHAR(8)\n,@rights     VARCHAR(20)\n,@role       VARCHAR(20)\n\nDECLARE c_objects CURSOR FOR\n    SELECT  name\n    FROM    sysobjects\n    WHERE   type IN ('P', 'U', 'V')\n    FOR READ ONLY\n\nBEGIN\n\n    SELECT  @rights = 'ALL'\n           ,@role = 'PUBLIC'\n\n    OPEN c_objects\n    WHILE (1=1)\n    BEGIN\n        FETCH c_objects INTO @object_name\n        IF @@SQLSTATUS &lt;&gt; 0 BREAK\n\n        SELECT @time = CONVERT(VARCHAR, GetDate(), 108)\n        PRINT '[%1!] hitting up object %2!', @time, @object_name\n        EXECUTE('GRANT '+ @rights +' ON '+ @object_name+' TO '+@role)\n\n    END\n\n    PRINT '[%1!] fin!', @time\n\n    CLOSE c_objects\n    DEALLOCATE CURSOR c_objects\nEND\nGO\nGRANT ALL ON sp_grantastic TO PUBLIC\nGO\n</code></pre>\n\n<p>Then you can fire and forget:</p>\n\n<pre><code>EXEC sp_grantastic\n</code></pre>\n"}, {'answer_id': 45920, 'author': 'Pascal Paradis', 'author_id': 1291, 'author_profile': 'https://Stackoverflow.com/users/1291', 'pm_score': 0, 'selected': False, 'text': "<p>We use something similar where I work. Looping through every Tables, Views, Stored Procedures of the system. </p>\n\n<pre><code>CREATE PROCEDURE dbo.SP_GrantFullAccess \n    @username varchar(300)\nAS\n\nDECLARE @on varchar(300) \nDECLARE @count int\nSET @count = 0\n\nPRINT 'Granting access to user ' + @username + ' on the following objects:'\n\nDECLARE c CURSOR FOR \nSELECT name FROM sysobjects WHERE type IN('U', 'V', 'SP', 'P') ORDER BY name\nOPEN c \nFETCH NEXT FROM c INTO @on \nWHILE @@FETCH_STATUS = 0 \nBEGIN \n SET @count = @count + 1\n EXEC('GRANT ALL ON [' + @on + '] TO [' + @username + ']') \n --PRINT 'GRANT ALL ON [' + @on + '] TO ' + @username\n PRINT @on\n FETCH NEXT FROM c INTO @on \nEND \nCLOSE c \nDEALLOCATE c\n\nPRINT 'Granted access to ' + cast(@count as varchar(4)) + ' object(s).'\nGO\n</code></pre>\n"}, {'answer_id': 52897, 'author': 'Martynnw', 'author_id': 5466, 'author_profile': 'https://Stackoverflow.com/users/5466', 'pm_score': 1, 'selected': False, 'text': "<p>There's an undocumented MS procedure called sp_MSforeachtable that you could use which is definitely in 2000 and 2005. </p>\n\n<p>To grant select permissions the usage would be: </p>\n\n<pre><code>EXECUTE sp_MSforeachtable @command1=' Grant Select on ? to RoleName'\n</code></pre>\n\n<p>To grant the other permissions either have a new statement for each one or just add them to the command like this: </p>\n\n<pre><code>EXECUTE sp_MSforeachtable @command1=' Grant Select on ? to RoleName; Grant Delete on ? to RoleName;'\n</code></pre>\n\n<p>With a bit of playing around it might be possible to turn the role name into a parameter as well.</p>\n"}, {'answer_id': 1663058, 'author': 'CSecord', 'author_id': 201157, 'author_profile': 'https://Stackoverflow.com/users/201157', 'pm_score': 0, 'selected': False, 'text': '<pre><code>use [YourDb]\nGO\nexec sp_MSforeachtable @command1=\n    "GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON ? TO Admins, Mgmt",\n    @whereand = " and o.name like \'tbl_%\'"\nGO\n\nuse [YourDb]\nGO\nexec sp_MSforeachtable @command1=\n    "GRANT REFERENCES, SELECT ON ? TO Employee, public",\n    @whereand = " and o.name like \'tbl_%\'"\nGO\n</code></pre>\n'}]
['https://Stackoverflow.com/questions/44780', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/3475/']