Function Privileges
-
In my DBs SQL Server shows different privileges for different USER DEFINED FUNCTIONS ....i.e
Some have SELECT
Some have EXECUTE
Some have SELECT, INSERT, UPDATE, DELETE
Some have SELECT, INSERT, UPDATE, DELETE, EXECUTE
I want to know is why they have different privileges....does it depends on SQL Statements used in the FUNCTIONS or some thing else.
-
Do you mean different privleges "granted" for theses functions? Rights are setup by the administrator, not based on code in a function
-
From BOL:
...You must have been granted CREATE FUNCTION permissions to create, alter, or drop user-defined functions. Users other than the owner must be granted EXECUTE permission on a function (if the function is scalar-valued) before they can use it in a Transact-SQL statement. If the function is table-valued, the user must have SELECT permissions on the function before referencing it. If a CREATE TABLE or ALTER TABLE statement references a user-defined function in a CHECK constraint, a DEFAULT clause, or a computed column, the table owner must also own the function. If the function is being schema-bound, you must have REFERENCE permission on tables, views, and functions referenced by the function.
-
If there is no documentation and the developer/admin has rolled off? All you can do is document it as best you can as you go and insure to document when developing yourself!
-
Actually the problem is....when i go to properties of the users of my DB.....there is a tab of Permissions....that shows all the objects of the DB with the rights (SELECT,INSERT,UPDATE,DELETE,EXECUTE,DRI) for all objects that can be granted to the users.......here different USER DEFINED FUNCTIONS have different RIGHTS available..i.e
FOR some functions ONLY SELECT can be granted....
FOR some functions ONLY EXECUTE can be granted....
FOR some functions SELECT, INSRET, UPDATE, DELETE can be granted....
I want to know why is it so......
-
There are different types of UDFs - some are inline scalar functions (return a single value), others are inline table-value functions that can be used similarly to a view. One of the earlier posts spoke about how different permissions are required (and I assume, are available) depending on the type of UDF.
That's my guess anyway
Do you get an error if you write the specific GRANT SQL statement for a permission on a UDF that isn't available in the EM GUI? -
Yes i do get the error e.g
Suppose in the EM GUI, UDF 'MyFunction' has ONLY SELECT Option available for Grant
and if i try to execute "GRANT EXECUTE ON MyFunction TO MYUser"
it gives me following error
"Granted or revoked privilege EXECUTE is not compatible with object"
-
Well I suppose that's your answer - EM is smart enough not to show permissions that cannot be set. I guess it depends on the type of function - was the one with only SELECT available as a grant an inline table-valued function?
-
Yah i also thought that it will be depending on type of function....but i could not find how exactly it differentiates....coz more or less all the functions were same.
-
I have three forms of user-defined functions that I use, and each has different possible permissions when viewed in EM. For this discussion, I'll ignore the DRI permission.
1. A "regular" UDF that returns a scalar type like int, varchar, or datetime. These UDFs can be assigned EXECUTE.
2. An inline table function like the following:
CREATE FUNCTION...
RETURNS TABLE
AS RETURN (SELECT ... )
This function can be assigned the SELECT, INSERT, UPDATE, and DELETE permissions.
3. A UDF that returns a defined table. These types can be assigned the SELECT permission. Here the function:
CREATE FUNCTION dbo.fGetTable
(
@codelist varchar(1000),
@delim varchar(10)
)
RETURNS @tbl TABLE (code varchar(1000))
AS
BEGIN
DECLARE @code varchar(1000), @occur int
SET @occur = 1
SET @code = COMMON.dbo.fGetToken(@codeList, @delim , @occur)
WHILE @code <> ''
BEGIN
INSERT @tbl (code) VALUES (@code)
SET @occur = @occur + 1
SET @code = COMMON.dbo.fGetToken(@codeList, @delim , @occur)
END
RETURN
END
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply