Home > SQL > Assign user permission to entire database

Assign user permission to entire database

January 27th, 2009 fbis

Sometimes when you restore a SQL Server backup, the permissions are not carried across! Its a real pain to tick all those boxes so here is the dirty way I use to create a script to assign them.

The code below pulls out the tables and views from the database structure into a single column.

SELECT     '[' + name + ']' AS Expr1
FROM         sysobjects
WHERE     (xtype = 'U' OR xtype='V')

This is rough but it works. Select the resulting column in Enterprise manager and copy into notepad. Do a search and replace, change:

  '[' TO 'GRANT INSERT, UPDATE, SELECT ON ' AND ']' TO ' TO Username'
GRANT INSERT, UPDATE, SELECT ON [tablename] TO Username
Categories: SQL Tags:
Comments are closed.