Hi,

I have encountered a strange security problem recently, I send sql server
functions to my clients and when they install them, it seems execute
permissions are turned off by default (at least this happened to 2 of my
clients: they encountered lack no execute permissions on those functions).
Are there any differences in the default security settings for triggers and
functions, because I never encountered this with triggers.
Does this mean that it is a standard procedure after you install functions
through query analyzer like
Create function dbo.function_name
AS....

A DBA has to go to Management Studio and set Execution permission for
certain users, otherwise by default they would have deny Execution?

Thank you,

Vadim

> Does this mean that it is a standard procedure after you install functions
> through query analyzer like
> Create function dbo.function_name
> AS....
>
> A DBA has to go to Management Studio and set Execution permission for
> certain users, otherwise by default they would have deny Execution?

The Best Practice is to grant permissions to roles during deployment. For
example:

GRANT EXECUTE ON FUNCTION dbo.function_name TO SomeRole;

This allows end user permissions to be controlled by administering role
membership and your deployment scripts don't change as users are
added/removed.

Note that there are no default permissions on user-defined objects. The
absence of permissions means no access is allowed except by privileged users
(e.g. db_owner role members) and some fixed database role members
(db_datareader). Users must have permissions (directly or through role
membership) on objects accessed directly.

Trigger permissions are not applicable because triggers are not accessed
directly. Uses do not need permissions on indirectly referenced objects as
long as the owners are the same. See Ownership Chaining in the Books Online
for more information. I also blogged some of my thoughts on ownership
chaining at
http://weblogs.sqlteam.com/dang/arch...ip-Chains.aspx.


--
Hope this s.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Vadim" <vadim@dontsend.com> wrote in message
news:uJuXj8TkIHA.4744@TK2MSFTNGP06.phx.gbl...
> Hi,
>
> I have encountered a strange security problem recently, I send sql server
> functions to my clients and when they install them, it seems execute
> permissions are turned off by default (at least this happened to 2 of my
> clients: they encountered lack no execute permissions on those functions).
> Are there any differences in the default security settings for triggers
> and functions, because I never encountered this with triggers.
> Does this mean that it is a standard procedure after you install functions
> through query analyzer like
> Create function dbo.function_name
> AS....
>
> A DBA has to go to Management Studio and set Execution permission for
> certain users, otherwise by default they would have deny Execution?
>
> Thank you,
>
> Vadim
>