Required permissions LinqPad Object Explorer

Op0

When I point LinqPad at my dev server, where I am dbo, my tables show with primary keys and references.

When I point LinqPad at my prod server, where I only have db_reader and db_writer, my tables say "Warning: No primary key, updates will fail" and show no references.

What permissions does LinqPad Object Explorer need to enumerate the tables completely? With SQL Server Management Studio, I can see the complete table definitions in SSMS Object Explorer pointed at either server.

This is for SQL Server 2008 R2 and LinqPad 4.43.06 Premium Edition.

Tormod

This is the case only for tables where the primary key is not its own field, but rather the composition of foreign keys, correct?

JoeAlbahari

I've just tried creating a user on SQL Server 2012 with only db_reader and db_writer permissions to the northwind database. All the tables come through with primary keys, including EmployeeTerritories whose primary key is a composition of EmployeeID and TerritoryID.

Can you confirm the same thing works (or doesn't) for yourself?

LINQPad uses the following query to retrieve primary key information for SQL Server databases:

select tc.TABLE_SCHEMA, tc.TABLE_NAME, kcu.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
	inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
	on tc.TABLE_NAME = kcu.TABLE_NAME 
	and tc.TABLE_SCHEMA = kcu.TABLE_SCHEMA
	and tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
where tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
order by tc.TABLE_NAME, kcu.ORDINAL_POSITION option (force order)