SQL Server - what permission need to open for view a table schema in a linked server
After creating a linked server on SSMS 2008, how should I see linked server table schema? Should I change any permission on remote SQL Server? Also, what commands can check those permissions for all accounts? Thanks. EDIT: running on SQL Server 2000.
Stan, if all you want to be able to see is the table definitions themselves, you can grant [VIEW DEFINITION]. This can be done at: - Instance Level - Database Level - Schema Level - Object Level This will allow you to see the columns in a table/view or the inner workings of a stored proc/function. If you want to be able to access the data in a table/view, you then need to look at granting SELECT privileges for the object(s). For Stored Procedures it would be the EXECUTE privelege. See the BOL reference: [GRANT] As far as viewing all permissions for all users, something like this would do the trick: (This is code that I use, but I originally found [here] select dp.NAME AS principal_name, dp.type_desc AS principal_type_desc, o.NAME AS object_name, p.permission_name, p.state_desc AS permission_state_desc from sys.database_permissions p inner join sys.database_principals dp on p.grantee_principal_id = dp.principal_id left join sys.all_objects o on p.major_id = o.OBJECT_ID *EDIT: SQL 2000 script added* Below is a script that should help a little with SQL 2000, although only on a database level. I am not too hot on SQL 2000. SELECT DISTINCT su.name principal_name, principal_type_desc = CASE WHEN issqlrole = 1 THEN 'DATABASE_ROLE' WHEN isapprole = 1 THEN 'APPLICATION_ROLE' WHEN issqluser = 1 THEN 'SQL_USER' WHEN isntuser=1 THEN 'WINDOWS_USER' WHEN isntgroup=1 THEN 'WINDOWS_GROUP' END, so.NAME object_name, permission_name = CASE spr.action WHEN 26 THEN 'REFERENCES' WHEN 178 THEN 'CREATE FUNCTION' WHEN 193 THEN 'SELECT ' WHEN 195 THEN 'INSERT ' WHEN 196 THEN 'DELETE ' WHEN 197 THEN 'UPDATE ' WHEN 198 THEN 'CREATE TABLE ' WHEN 203 THEN 'CREATE DATABASE ' WHEN 207 THEN 'CREATE VIEW ' WHEN 222 THEN 'CREATE PROCEDURE ' WHEN 224 THEN 'EXECUTE ' WHEN 228 THEN 'BACKUP DATABASE ' WHEN 233 THEN 'CREATE DEFAULT ' WHEN 235 THEN 'BACKUP LOG ' WHEN 236 THEN 'CREATE RULE' END, permission_name = CASE spr.protecttype WHEN 204 THEN 'GRANT_W_GRANT' WHEN 205 THEN 'GRANT' WHEN 206 THEN 'DENY' END FROM syspermissions sp INNER JOIN sysprotects spr ON
sp.id INNER JOIN sysusers su ON sp.grantee = su.uid LEFT JOIN sysobjects so ON
This depends upon what user is entered for the linked server connection. You can pass through your login details (each user that uses the linked server uses their own login information), set a static user (all users impersonate one user) or use no login information. The permissions on the linked server are then dependant on the user that is connecting to it. After setting up the linked server, you can then navigate through it to see what you can access. It is easier to check permissions by accessing the linked server directly and checking permissions there. If you have the right permissions, you can navigate the database(s) on the linked server in the same way as a local database in SSMS.