SSMS 2008: Object Explorer Details

I'm using SQL Server 2008 SSMS against a SQL 2008 database and have a
question about permissions.

As a test, I gave a sql authenticated id Select permissions to two of
the tables. Since that id has access to the tables, shouldn't I be able
to see them when I open SSMS 2008 connected as the test user, expand the
database and then expand Tables?

I can open a New Query window and query the tables and get data returned
without error.

I don't need to be able to see all the objects -- just what I have
access to. Is that possible?

thank you,

Toni

*** Sent via Developersdex http://www.developersdex.com ***

Toni (teibner@allinadotcom) writes:
> I'm using SQL Server 2008 SSMS against a SQL 2008 database and have a
> question about permissions.
>
> As a test, I gave a sql authenticated id Select permissions to two of
> the tables. Since that id has access to the tables, shouldn't I be able
> to see them when I open SSMS 2008 connected as the test user, expand the
> database and then expand Tables?
>
> I can open a New Query window and query the tables and get data returned
> without error.
>
> I don't need to be able to see all the objects -- just what I have
> access to. Is that possible?

That is indeed possible, although it is not default. To see the defintion
of an object, you need the permission VIEW DEFINITION on it. But if you
have SELECT permission, VIEW DEFINITION is implied. However, you can
explicitly deny a user VIEW DEFINITION, in which case the user cannot
see the table in Object Explorer.

The script below illustrates:



CREATE DATABASE tomtefrid
CREATE LOGIN kalleanka WITH PASSWORD='Bengt Feldreich'
go
USE tomtefrid
go
CREATE TABLE nisse (a int NOT NULL)
CREATE TABLE staffan (a int NOT NULL)
CREATE TABLE josef (a int NOT NULL)
CREATE USER kalleanka
go
GRANT SELECT ON nisse TO kalleanka
GRANT SELECT ON staffan TO kalleanka
go
EXECUTE AS LOGIN = 'kalleanka'
go
-- Lists both tables
SELECT object_name(object_id) FROM sys.tables
go
REVERT
go
DENY VIEW DEFINITION ON nisse TO kalleanka
go
EXECUTE AS LOGIN = 'kalleanka'
go
-- Lists only staffan
SELECT object_name(object_id) FROM sys.tables
-- But user can still SELECT.
SELECT a FROM nisse
go
REVERT
go
USE tempdb
go
DROP DATABASE tomtefrid
DROP LOGIN kalleanka


--
Erland Sommarskog, SQL Server MVP, esquel.RemoveThis@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx