security for function execution on sql server 2000/2005

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Vadim - 29 Mar 2008 02:59 GMT

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

Reply to this Message

Dan Guzman - 29 Mar 2008 14:56 GMT

> Does this mean that it is a standard procedure after you install functions
> through query analyzer like
[quoted text clipped - 3 lines]
> 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/archive/2008/02/09/Security-with-Ownership-Chain
s.aspx.

Signature

Hope this helps.

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

> Hi,
>
[quoted text clipped - 15 lines]
>
> Vadim

Reply to this Message