In Microsoft SQL Server, if you want to grant select, insert, update, and/or delete authority to all tables and views in a database to an individual account, you may have a lot of manual labor ahead of you fully prone to error, or you could automate it. Here’s a stored procedure that’ll do it for you. You just pass in the name of the SQL Server user account.
CREATE procedure [dbo].[GrantAllTableAccessTo](@Executioner varchar(200)) as
begin
-- From http://csharpner.blogspot.com/2009/11/grant-select-insert-update-delete-to.html
declare @ObjectName varchar(200)
DECLARE ObjectCursor CURSOR FOR
SELECT name
FROM [dbo].sysobjects
WHERE xtype = 'V' or xtype = 'U'
OPEN ObjectCursor
FETCH NEXT FROM ObjectCursor INTO @ObjectName
declare @Command varchar(500)
WHILE (@@FETCH_STATUS -1) BEGIN
IF (@@FETCH_STATUS -2) BEGIN
set @Command = 'grant select on ' + @ObjectName + ' to [' + @Executioner + ']'
print @Command
exec(@Command)
set @Command = 'grant insert on ' + @ObjectName + ' to [' + @Executioner + ']'
print @Command
exec(@Command)
set @Command = 'grant update on ' + @ObjectName + ' to [' + @Executioner + ']'
print @Command
exec(@Command)
set @Command = 'grant delete on ' + @ObjectName + ' to [' + @Executioner + ']'
print @Command
exec(@Command)
end
FETCH NEXT FROM ObjectCursor INTO @ObjectName
END
CLOSE ObjectCursor
DEALLOCATE ObjectCursor
end;
Here’s how you call it, assuming you want to grant select, insert, update, and delete to a user called “db_user”:
exec GrantAllTableAccessTo ‘db_user’
This stored procedure searches the system tables for all tables and views, then builds multiple “grant” statements for each one and executes each one for you. For your convenience of reviewing the results, it prints each generated command to the output window, so that if there are any errors, you’ll know which generated statement triggered it.
- If you just want views, then remove “or xtype = ‘U’” from the query.
- If you just want tables, then remove “xtype = ‘V’ or “ from the query.
- If you want it granted to all but a small, handful of of tables or views, you could add the exceptions list to your own logic added inside the loop.