How to Script Automatically, the securables assigned to a SQL account

Question

I want to generate script to assign an user account to some securables, e.g. Table:Select.

How to do this?

Answer 1

I have found a script that supposed to do this.

http://alexsdba.spaces.live.com/Blog/cns!F86565E81CD9BC16!137.entry

Answer 2

As with anything SQL server when using SQL Server Management Studio if you aren't sure how to do a specific thing (i.e. looked in Books online but can't quite figure it out), using the 'Script Action to New Query Window' from the Script drop-down in the dialogs is very useful.

By using the GUI tools then inspecting the resulting script you can quickly see how to do more complex things that you just can't keep in memory until you've done them loads of times.

Answer 3

You can modify the where clause of the object selection as you see fit. Should be minimal tweeking if you are looking for a 2005 or 2008 script.

/Caution!/ This script can be a little dangerous.

Declare @TableName varchar(100),
   @Sql nvarchar(500),
   @Result int,
   @UserName nvarchar(258)
   set @UserName= QuoteName('<your_user>')
Print @UserName

  DECLARE
     Your_Cursor cursor
     LOCAL
     FORWARD_ONLY
     OPTIMISTIC
  FOR  
/* if you only want one object to apply permissions to*/
-- select Name from Sysobjects where name = 'Your_TableName'
/*tables*/-- select name from sysobjects where xtype = 'U' order by name
/*views*/-- select name from sysobjects where xtype = 'V' order by name
/*StoredPs*/-- select name from sysobjects where xtype = 'P' order by name
/*UDFs*/-- select name from sysobjects where xtype = 'FN' order by name
/**********************************************************************/


OPEN Your_Cursor
FETCH NEXT from Your_Cursor into @TableName
while (@@fetch_status = 0)
   begin
/*Tables*/
--      set @Sql = N'Grant Select On '+ @TableName+ N' To ' + @UserName
--      set exec @Result = sp_executeSql @Sql
--      if @Result = 0
--        begin
--          Print 'Granted Select On '+ @TableName + ' by ' + @UserName
--        end
--      set @Sql = N'Grant Insert On '+ @TableName+ N' To ' + @UserName
--      set exec @Result = sp_executeSql @Sql
--      if @Result = 0
--        begin
--          Print 'Granted Insert On '+ @TableName + ' by ' + @UserName
--        end
--      set @Sql = N'Grant Update On '+ @TableName+ N' To '+ @UserName
--      set exec @Result = sp_executeSql @Sql
--      if @Result = 0
--        begin
--          Print 'Granted Update On '+ @TableName + ' by ' + @UserName
--        end
--      set @Sql = N'Grant Delete On '+ @TableName+ N' To '+ @UserName
--      set exec @Result = sp_executeSql @Sql
--      if @Result = 0
--        begin
--          Print 'Granted Delete On '+ @TableName + ' by ' + @UserName
--        end
/*Stored Procs and UDFs*/
--      set @Sql = N'Grant Execute On '+ @TableName+ N' To '+ @UserName
--      set exec @Result = sp_executeSql @Sql
--      if @Result = 0
--        begin
--          Print 'Granted Execute On '+ @TableName + ' by ' + @UserName
--        end
     FETCH NEXT from your_Cursor into @TableName
   end
CLOSE Your_Cursor
DEALLOCATE Your_Cursor