grant to all objects

Is there an easy way to assign permissions to all objects for a user/group?
For instance, in the following BOL example, SELECT permissions are granted t
o
the public role:
GRANT SELECT
ON authors
TO public
GO
In the above example, the table object 'authors' is explicitly listed; is
there a way to use the same statement to grant the same select permission to
the public role for all objects? Or would I have to explicitly indicate each
object? In the latter case, I could use a cursor to dynamically create the
grant statements for each of my objects, however, I was hoping to find a
simpler way to have this done.
Thanks for all your responses in advance.Firstly, I think the BOL example is dumb - I wouldn't go assigning ANY
permissions to the public role in ANY database.
That said, you'd have to assign individual object permissions
individually, preferably to a role that you create but to a specific
user would work too (just much uglier and more admin overhead when users
come & go and when restoring DB backups to other servers). You can do
this in a small cursor loop which is very easy to do. Here's one I
whipped up in about 5 minutes when I read your post that will assign all
possible permissions for all user tables, views, procs & UDFs in the
current database to a specified user or role (untested):
declare @.cmd nvarchar(1000)
declare @.objname sysname
declare @.owner sysname
declare @.objtype char(2)
declare objs cursor for
select [name], user_name(uid) as owner, type from dbo.sysobjects
where type in ('U', 'P', 'V', 'FN')
order by type, [name]
for read only
open objs
fetch next from objs into @.objname, @.owner, @.objtype
while (@.@.FETCH_STATUS != -1)
begin
if (@.@.FETCH_STATUS != -2)
begin
select @.cmd = 'grant ' +
case (@.objtype)
when ('U') then ('select, insert, update, delete,
references')
when ('V') then ('select, insert, update, delete,
references')
when ('P') then ('execute')
when ('FN') then ('execute')
end + ' on [' + @.owner + '].[' + @.objname + '] to <my
user/role>'
exec (@.cmd)
end
fetch next from objs into @.objname, @.owner, @.objtype
end
close objs
deallocate objs
How easy is that?
HTH
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Rob wrote:

>Is there an easy way to assign permissions to all objects for a user/group?
>For instance, in the following BOL example, SELECT permissions are granted
to
>the public role:
>GRANT SELECT
>ON authors
>TO public
>GO
>In the above example, the table object 'authors' is explicitly listed; is
>there a way to use the same statement to grant the same select permission t
o
>the public role for all objects? Or would I have to explicitly indicate eac
h
>object? In the latter case, I could use a cursor to dynamically create the
>grant statements for each of my objects, however, I was hoping to find a
>simpler way to have this done.
>Thanks for all your responses in advance.
>sql