Checking REFERENCES permission is granted or not?

Question

I am trying to execute the following minimalized example on SQL Server (14.0.3465.1):

--drop TABLE [dbo].[testtable]
--drop TABLE [dbo].[referencetable]
create table [dbo].[referencetable](
    [referencetableID] [int] IDENTITY(1,1) NOT NULL
    CONSTRAINT [PK_referencetable] PRIMARY KEY CLUSTERED 
    (
        [referencetableID] ASC
    )
);

CREATE TABLE [dbo].[testtable](
    [testtableID] [int] IDENTITY(1,1) NOT NULL,
    [referencetableID] [int] NOT NULL
 CONSTRAINT [PK_testtable] PRIMARY KEY CLUSTERED 
(
    [testtableID] ASC
));

ALTER TABLE [dbo].[testtable]  WITH CHECK ADD  CONSTRAINT [FK_testtable_referencetable] FOREIGN KEY([referencetableID])
REFERENCES [dbo].[referencetable] ([referencetableID])

I receive the following error at the ALTER lines:

Msg 229, Level 14, State 5, Line 19
The REFERENCES permission was denied on the object 'referencetable', database '<dbnamestripped>', schema 'dbo'.
Msg 1088, Level 16, State 20, Line 19
Cannot find the object "dbo.referencetable" because it does not exist or you do not have permissions.
Msg 1750, Level 16, State 1, Line 19
Could not create constraint or index. See previous errors.

We have multiple server instances and multiple databases. I executed the same script on a database on another server instance and on another database on the same server instance: the script executed without error.

I found that a REFERENCES permission might be missing. I checked granted permissions on all of the three databases (the two where the script executed without error and the one where it fails) and I do not have that permission on any of them!

I do not have privileged account on any of the above servers/databases. I am authenticating using Windows login and sysadmins set up grants based on Windows group membership.

Why is the script executing flawlessly on two other databases and is failing on the third one?

UPDATE: It seems on the other two databases I am member of db_ddladmin role through some group membership chain. The db_ddladmin role implicitly contains the REFERENCES permission.

Answer 1

you can use the HAS_PREMS_BY_NAME() to see what permissions you have access to. For example,

SELECT HAS_PERMS_BY_NAME('dbo.referencetable', 'OBJECT', 'REFERENCES' );

If it returns 1 or TRUE, you have the permissions needed to create the foreign key. Otherwise, you'll need to check with the database owner to grant you the rights. On this particular database, the admin may have granted you create table permissions, but forgot to add a statement like this to give your assigned database role like analysts references permissions.

GRANT REFERENCES TO analysts;