Error Creating Function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Error Creating Function

Author

Topic

Stamey
Starting Member

14 Posts

Posted - 2011-02-09 : 07:33:40

I have a Table-Valued function I am creating, or attempting to create. There seems to be a problem with a DB option/permission setting causing me not to be able to create it, because when I try to create it in the AdventureWorks DB it creates just fine, but I get the following error when trying to create it in my prod DB.
Error:
Msg 4606, Level 16, State 1, Line 1
Granted or revoked privilege EXECUTE is not compatible with object.
The statement has been terminated.

I copied a function from a Technet page, http://technet.microsoft.com/en-us/library/ms191165.aspx, and it creates fine in the AdventureWorks DB but not in my prod DB.

Any ideas as to what option/permission setting I need to look at?

Thanks,
Chris

Sachin.Nand

2937 Posts

Posted - 2011-02-09 : 08:44:40

Seems you dont have enough permission to create a function in the Prod DB.Try this query

SELECT * FROM fn_my_permissions('YourProdDb','Database')

and check whether you have enough privilages to create a function.

PBUH

Stamey
Starting Member

14 Posts

Posted - 2011-02-09 : 10:54:24

I would have thought that also, expect I am SysAdmin on this box, my local WS. I am trying to keep an open mind though.

Permissions returned by your query:
permission_name
CREATE TABLE
CREATE VIEW
CREATE PROCEDURE
CREATE FUNCTION
CREATE RULE
CREATE DEFAULT
BACKUP DATABASE
BACKUP LOG
CREATE DATABASE
CREATE TYPE
CREATE ASSEMBLY
CREATE XML SCHEMA COLLECTION
CREATE SCHEMA
CREATE SYNONYM
CREATE AGGREGATE
CREATE ROLE
CREATE MESSAGE TYPE
CREATE SERVICE
CREATE CONTRACT
CREATE REMOTE SERVICE BINDING
CREATE ROUTE
CREATE QUEUE
CREATE SYMMETRIC KEY
CREATE ASYMMETRIC KEY
CREATE FULLTEXT CATALOG
CREATE CERTIFICATE
CREATE DATABASE DDL EVENT NOTIFICATION
CONNECT
CONNECT REPLICATION
CHECKPOINT
SUBSCRIBE QUERY NOTIFICATIONS
AUTHENTICATE
SHOWPLAN
ALTER ANY USER
ALTER ANY ROLE
ALTER ANY APPLICATION ROLE
ALTER ANY SCHEMA
ALTER ANY ASSEMBLY
ALTER ANY DATASPACE
ALTER ANY MESSAGE TYPE
ALTER ANY CONTRACT
ALTER ANY SERVICE
ALTER ANY REMOTE SERVICE BINDING
ALTER ANY ROUTE
ALTER ANY FULLTEXT CATALOG
ALTER ANY SYMMETRIC KEY
ALTER ANY ASYMMETRIC KEY
ALTER ANY CERTIFICATE
SELECT
INSERT
UPDATE
DELETE
REFERENCES
EXECUTE
ALTER ANY DATABASE DDL TRIGGER
ALTER ANY DATABASE EVENT NOTIFICATION
ALTER ANY DATABASE AUDIT
VIEW DATABASE STATE
VIEW DEFINITION
TAKE OWNERSHIP
ALTER
CONTROL

I got the same results when running against my AdventureWorks DB also.

Thanks,
Chris

Sachin.Nand

2937 Posts

Posted - 2011-02-09 : 11:58:49

Try this

Grant Select on YourFunction to public

PBUH

Stamey
Starting Member

14 Posts

Posted - 2011-02-10 : 08:08:48

That doesn't help because I'm not even to the point of trying to use the function. I can't get the function created in the first place because of this error. Here is a striped down version of the function, which produces the error:
Create Function dbo.Split(@String As VarChar(Max), @Delimiter VarChar(2) = '', @ByteCount Int = 0)
Returns @SplitTable Table
(
SplitID Int Identity(1,1),
SplitText VarChar(10)
)
AS
Begin
Return
End

Error:
Msg 4606, Level 16, State 1, Line 1
Granted or revoked privilege EXECUTE is not compatible with object.
The statement has been terminated.

Here is the MS example from the page mentioned before, with most of the guts stripped out, and it still produces the same error in my prod DB, but not in my AdventureWorks DB.
CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)
RETURNS @retContactInformation TABLE
(
-- Columns returned by the function
ContactID int PRIMARY KEY NOT NULL,
FirstName nvarchar(50) NULL,
LastName nvarchar(50) NULL,
JobTitle nvarchar(50) NULL,
ContactType nvarchar(50) NULL
)
AS
BEGIN
RETURN;
END;
GO

Other functions create just fine. It's the TVFs that will not create.

Thanks,
Chris

Stamey
Starting Member

14 Posts

Posted - 2011-02-10 : 14:56:33

Found it! It was a trigger the DBAs had put in the DB to automatically grant execute on items that were created by non-Sysadmins in the DB, but have not taken into account TVFs.
Someone on SSC suggested I check this.

Thanks,
Chris

Sachin.Nand

2937 Posts

Posted - 2011-02-11 : 06:00:13

You cannot have a execute permission defined on a TVF.Because TVF behaves like a table and table cannot have execute permissions.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources