I want to access a SQL Server table on a 2005 server via an Office Access
project (*.adp). Only read access is wanted.

It seems to be necessary to grant the CONTROL permission (besides SELECT and
VIEW DEFINITION) to display the connected table within Access.

Unfortunately I didn't found much precise information about CONTROL
permission. But it seems no one want to permit CONTROL to read only users.

Can someone give me a clue?

Thanks!

Addition / more precise:
I'm not allowed to explicitely deny the CONTROL permission. If I do so, the
table isn't displayed as a connected table in the Access project.

Softie (Softie (AT) discussions (DOT) microsoft.com) writes:
CONTROL means everything, so, no you don't want to grant that. Nor
should it be necessary.

Why do you think you need CONTROL? Do you get errors if you don't?

Not really sure why you try that. Since you only have CONTROL if you are
granted that permissions, there is no reason to DENY users CONTROL, is
there?


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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/prodinf...ons/books.mspx

Hi Erland!

Meanwhile I suppose I've recognized the meaning of CONTROL:

CONTROL means everything, like you've described.
*But* DENY CONTROL means everything, too - so DENY allowed SELECT, too...

(That's not very intuitive, in my opinion.
And with the GUI you're lead to deny all permissions which you are not
explicitly want to allow.)

I'd misunderstood CONTROL: I supposed that "don't deny" = "allow".


Meanwhile it seems to be more clear (see above: the temptation to deny all
except the wanted permissions, especially with the GUI checkboxes).

The concept of DENY ALL was the basic idea, which lead in my
misinterpretation of CONTROL permission.

Can you confirm my realization about CONTROL permission?

Thanks for help!

Softie (Softie (AT) discussions (DOT) microsoft.com) writes:
That is not what I expect either, but a quick test reveals that is the case.

GUI in case of SQL Server is always dubious. The GUI does not give you
any extra powers. All it does is to generate SQL commands. Many of the
GUIs in SSMS are confusing, because you have to guess what they really
mean.

And you are not really saved from understanding the underlying model. In
SQL Server there are three commands to administer permissions: GRANT, REVOKE
and DENY.

With GRANT you grant permissions, with DENY you explicitly deny permissions,
thereby overriding GRANT. With REVOKE you undo the effect of GRANT or DENY.

The starting position is that you have no permissions, so you only use
DENY to state exceptions. Say that you want users in a database to have
SELECT permissions on all tables, but there is a table with sensitive
data that they should not be able to access. In this case you grant
SELECT permissions on database or schema level, and deny access to the
specific table.

REVOKE does not display in the GUI, because REVOKE is not a state, only
the absence of one.




--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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/prodinf...ons/books.mspx

Hello Erland!

Thanks for your reply!

Unfortunately your infos went into wrong way.
The original/general meaning/sense of DENY (and so on) is well known.

Because there are different ways for users to get permissions/access in MS
SQL Server (i.e. inherition), in my idea it would be nice to deny all
unwanted rights, independant to ways which SQL inventors imagine years ago.


GUI: Yes, you're right - an user of a GUI have to guess/hope that things
would run like expected... (But if the GUI is the "MS SQL Server Management
Studio" I'm expecting much more than from a shareware GUI tool... ;-)) )

And for quick and dirty operations the main config application should do
precise jobs...


The misunderstandig of DENY CONTROL came up because of a similar strange
bahaviour:

Originally I only want to GRANT a SELECT permission.
So I DENY all other permissions.

But I only get data if I don't deny CONTROL *and* VIEW DEFINITION.

So I've to guess that not denying of VIEW DEFINITION is the same as GRANT
VIEW DEFINITION. I've to guess that the permission VIEW DEFINITION is
necessary to get data by a SELECT.

(In general, VIEW DEFINITION shouldn't be necessary! But it is!)

So I guessed the same behaviour for CONTROL permission (which was a wrong
assumption).

Softie (Softie (AT) discussions (DOT) microsoft.com) writes:
It is true that users can be member of many groups, and and this way
may get powers you don't expect them to. But I would suggest that in
such case you should review their group membership, or the permissions
if these group, instead of using DENY. DENY is likely tobuy your more
problems down the road. Maybe later, a user becomes part of a group that
should have a permission that you have denied him, and now you can't
figure why he is not able to access the data.

This is the scoop: When you are granted SELECT permissions on something,
you implicitly get VIEW DEFINTION on the same object. However, you can
REVOKE (rather than DENY) VIEW DEFINTION if this is not needed.)

Yes, many client APIs relies on being able to retrieve metadata, so
revoking or denying VIEW DEFINTION can mean of loss of functionality.




--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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/prodinf...ons/books.mspx

Since you only need to access data read-only from your Access client,
one solution is to revoke all permissions to the base tables and use
stored procedures to access the data. You then grant EXECUTE
permissions on the stored procedures. You can use them as the record
source for your forms and reports, passing parameter values to them as
needed. This also ensures that people can't do an end run around your
Access application and connect to the data using some other client.

--Mary

On Sun, 27 Dec 2009 13:30:01 -0800, Softie
<Softie (AT) discussions (DOT) microsoft.com> wrote: