Posted by Erland Sommarskog on 10/01/07 21:40

abu hisham (yjogee@hotmail.co.uk) writes:
> You can grant permissions dynamically in this way to all db objects:
> /* tables and views*/
> select 'Grant select,insert,update,delete on '+name+ ' to USER'
> from sysobjects
> where xtype in ('U','V')
>
> /*Stored procedures*/
> select 'Grant exec on '+name+ ' to USER'
> from sysobjects
> where xtype in ('P')

In SQL 2005 this can be achieved with a single statement:

GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE
ON SCHEMA::schema_name TO user

Access granted on schema level are inherited by objects in the schema, which
means that it also applies to future objects.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]