Hello.

I need to grant permissions to specific scalar function in specific database.
How Can I deny execution from everyone except 3 specific users / logins?

Thanks.

Hi
GRANT SELECT ON some_udf TO username -- Grant permission on a single udf



"ampo" <u53310@uwe> wrote

1. Is this means that the rest of the users, by default, are denied?
2. Should I grand "Select" or "Execute" permissions?

Thanks.


Uri Dimant wrote:

ampo
No, rest of users should no be a member of db_owner database role, sysadmin
server role

Hmm , If I remember well SELECT ,have not done it for a long time, check it
out in BOL



"ampo" <u53310@uwe> wrote

ampo (u53310@uwe) writes:
Unless they have permission by some other mean, for instance permission on
the schema.

Also, keep in mind that if the function is called from within a stored
procedure with the same owner, it does not matter who is the caller.

Let's see:

CREATE FUNCTION testis () RETURNS int AS
BEGIN
RETURN 2
END
go
GRANT SELECT ON testis TO public
GRANT EXECUTE ON testis TO public
go
DROP FUNCTION testis

Gives:

Msg 4606, Level 16, State 1, Line 1
Granted or revoked privilege SELECT is not compatible with object.

Looks like you should grant EXECUTE.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thanks Erland for testing




"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote