default db permissions for account

Hi
I want to allow the windows iusr_computername account exec permissions on
user stored procs and select on views. I have 50 odd stored procs. Is there
a way of assigning permissions so that this account always has those
permissions and the permissions are automatically added when a new view or
sp is added?
I'd also like to easily transfer this to other databases. I'm sure the
answer lies in using roles or scripts or perhaps there's a fundamentally
easy way that I haven't found yet?
Thanks
AndrewHi Andrew,
There is no fundamentally easy way to assign permissions on all the stored
procedures to a user.
You can use the following script to give a user permission on all existing
stored procedures, but you have to re-run it to give permissions to newly
created stored procedures:
DECLARE @.proc_name SYSNAME
SET @.proc_name = ''
WHILE 1=1
BEGIN
SET @.proc_name = (SELECT TOP 1 ROUTINE_NAME FROM
INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME, 'IsMSShipped') = 0 -- Only
user stored procedures
AND ROUTINE_TYPE = 'Procedure'
AND ROUTINE_NAME > @.proc_name
ORDER BY ROUTINE_NAME
)
IF @.proc_name IS NULL BREAK
EXEC ('GRANT EXECUTE ON ' + @.proc_name + ' TO MyUser')
END
You can use something similar to assign permissions on views using
inforamtion_schema.views.
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Andrew Jocelyn" <andrew.jocelyn@.REMOVETHISBITempetus.co.uk> wrote in
message news:OvwDb1ZXDHA.2424@.TK2MSFTNGP12.phx.gbl...
> Hi
> I want to allow the windows iusr_computername account exec permissions on
> user stored procs and select on views. I have 50 odd stored procs. Is
there
> a way of assigning permissions so that this account always has those
> permissions and the permissions are automatically added when a new view or
> sp is added?
> I'd also like to easily transfer this to other databases. I'm sure the
> answer lies in using roles or scripts or perhaps there's a fundamentally
> easy way that I haven't found yet?
> Thanks
> Andrew
>|||Hi thanks for that.
Just one little problem. I'm getting an error "Invalid parameter 2 specified
for object_id.". I'm afraid my attempts to debug have failed. Can you help?
Thanks again
Andrew
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:Oh%23e9saXDHA.1280@.tk2msftngp13.phx.gbl...
> Hi Andrew,
> There is no fundamentally easy way to assign permissions on all the stored
> procedures to a user.
> You can use the following script to give a user permission on all existing
> stored procedures, but you have to re-run it to give permissions to newly
> created stored procedures:
> DECLARE @.proc_name SYSNAME
> SET @.proc_name = ''
> WHILE 1=1
> BEGIN
> SET @.proc_name = (SELECT TOP 1 ROUTINE_NAME FROM
> INFORMATION_SCHEMA.ROUTINES
> WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME, 'IsMSShipped') = 0 -- Only
> user stored procedures
> AND ROUTINE_TYPE = 'Procedure'
> AND ROUTINE_NAME > @.proc_name
> ORDER BY ROUTINE_NAME
> )
> IF @.proc_name IS NULL BREAK
> EXEC ('GRANT EXECUTE ON ' + @.proc_name + ' TO MyUser')
> END
> You can use something similar to assign permissions on views using
> inforamtion_schema.views.
>
> --
> Jacco Schalkwijk MCDBA, MCSD, MCSE
> Database Administrator
> Eurostop Ltd.
>
> "Andrew Jocelyn" <andrew.jocelyn@.REMOVETHISBITempetus.co.uk> wrote in
> message news:OvwDb1ZXDHA.2424@.TK2MSFTNGP12.phx.gbl...
> > Hi
> >
> > I want to allow the windows iusr_computername account exec permissions
on
> > user stored procs and select on views. I have 50 odd stored procs. Is
> there
> > a way of assigning permissions so that this account always has those
> > permissions and the permissions are automatically added when a new view
or
> > sp is added?
> >
> > I'd also like to easily transfer this to other databases. I'm sure the
> > answer lies in using roles or scripts or perhaps there's a fundamentally
> > easy way that I haven't found yet?
> >
> > Thanks
> > Andrew
> >
> >
>