Grant Select, Insert, Update, & Delete to all Tables and Views

Share

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.
Share

Grant Exec to all stored procedures

Share

In Microsoft SQL Server, if you want to grant exec authority to all stored procedures 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].[GrantExecTo](@Executioner varchar(200)) as
begin

 -- From: http://csharpner.blogspot.com/2009/11/grant-exec-to-all-stored-procedures.html

 declare @ObjectName varchar(200)

 DECLARE ObjectCursor CURSOR FOR
 SELECT   name
 FROM   [dbo].sysobjects
 WHERE   xtype = 'P'

 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 exec 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 exec to a user called “db_user”:

exec GrantExecTo ‘db_user’

This stored procedure searches the system tables for all stored procedures, 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.

Share

Assassin’s Creed 2 Walkthrough Video

Share

 

Assassin’s Creed 2 Walkthrough.

This game looks freaking awesome!

Be sure to watch it in HD and go full screen.

This game is available on Xbox 360, PlayStation 3, and Windows PCs.  It incorporates a new technology called UPlay, which will eventually connect up all of Ubisoft’s games in one, giant, connected gaming universe.  That service is still in beta though.

 

 

To use UPlay, you’ll have to sign up for it first, then you can activate it in-game.  Sounds like UPlay is somewhat similar to the XBox Live service.

Share