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