How to Give permission to large number of Tables/SP/Views in a Database . (Microsoft SQL 2000)

Permission script for Tables.

SELECT 'grant select, insert, update on ' + TABLE_NAME + ' TO [Machine name\user]' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'

 Permission Script for Store procedures.

SELECT 'grant EXECUTE on ' + ROUTINE_NAME  + ' TO [Machine name\user]' FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='PROCEDURE' and Lower(SUBSTRING(ROUTINE_NAME,1,2))<>'dt' AND PATINDEX('%sys%',ROUTINE_NAME)=0

Permission Script for Views

 SELECT 'grant select on ' + TABLE_NAME + ' TO [Machine name\user]' FROM INFORMATION_SCHEMA.views

 Run the above scripts to get the details of the Tables/SP/Views like this.

Example for Tables script.

grant select, insert, update on Country TO [Machine name\user]
grant select, insert, update on Customer TO [Machine name\user]
grant select, insert, update on CustomerBrands TO [Machine name\user]
grant select, insert, update on CustomerUserMapping TO [Machine name\user]
grant select, insert, update on dtproperties TO [Machine name\user]

 Follow the same method for the SP and Views.

eravi