CODEWITHSUNDEEP
sqlsql-serversql-grant
Pரதீப்
Pரதீப்

Reputation: 93734

How to Grant REFERENCES permission to all tables

I have to grant REFERENCES permission to a login say sql_login.

I can give grant REFERENCES permission to individual table like 

GRANT REFERENCES ON Mytable TO sql_login

Is there any way to grant REFERENCES permission to my login to all my tables or am wrong in any way

Upvotes: 7

Views: 21902

Answers (2)

Ben Thul
Ben Thul

Reputation: 32717

As it turns out, an explicit securable isn't needed in a GRANT statement. That is, you can say:

GRANT REFERENCES TO [sql_login];

Note, that it's not just tables to which the REFERENCES permission applies. From the documentation, it also applies to:

  • AGGREGATE
  • ASSEMBLY
  • ASYMMETRIC KEY
  • CERTIFICATE
  • CONTRACT
  • DATABASE
  • FULLTEXT CATALOG
  • FULLTEXT STOPLIST
  • FUNCTION
  • MESSAGE TYPE
  • PROCEDURE
  • QUEUE
  • RULE
  • SCHEMA
  • SEARCH PROPERTY LIST
  • SEQUENCE OBJECT SYMMETRIC KEY
  • SYNONYM
  • TABLE
  • TYPE
  • VIEW and
  • XML SCHEMA COLLECTION

I say that only so that you appreciate that you may be granting permissions on objects that you don't intend to with this approach. But the upside is that you won't have to manage these permissions ever again. That is, if/when you add a new table to your database, your user will automatically get the REFERENCES permission for it.

Upvotes: 14

Pரதீப்
Pரதீப்

Reputation: 93734

I myself found a way to do this 

DECLARE @sql VARCHAR(max) = ''

SET @sql =(SELECT distinct Concat('GRANT REFERENCES ON ', TABLE_NAME, ' TO sql_login; ')
           FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS
           WHERE  CONSTRAINT_TYPE IN ( 'PRIMARY KEY', 'UNIQUE' )
                  AND table_name IS NOT NULL
           FOR xml path(''))

--print @sql
EXEC (@sql)

sys.tables version

DECLARE @sql VARCHAR(max) = ''

SET @sql =(SELECT Concat('GRANT REFERENCES ON ', name, ' TO sql_login; ')
           FROM   sys.tables
           WHERE  Objectproperty(OBJECT_ID, 'TableHasPrimaryKey') = 1
                  AND type = 'u'
           FOR xml path(''))

--print @sql
EXEC (@sql)

Not sure this is the Ideal way to achieve this

Upvotes: 4

Related Questions