By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,156 Members | 1,026 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,156 IT Pros & Developers. It's quick & easy.

user/schema problem in SQL Server 2005!

P: n/a
do the following steps:

1:Use Manage Studio login the server with Integrated security.

2:Create a dabase named testdb;

3:Create a SQL Server login named amber ,and set it to be dbowner of
testdb;

4:Create a SQL Server login named guxiaobo ,set it's default databse to
be testbd,and in the testdb databse map login guxiaobo to user
guxiaobo;

5:Close Manage Studio and reopen it ,this time use login amber log to
the server.

6:In database testbd create a databse role role1 owned by dbo;

7:In database testdb create a schema schema1 owned by dbo;

8:Set user guxiaobo to be member of role1,and set guxiaobo's default
schema to be schema1;

9:In the schema properties-schema1 dialog choose permissions,in the
users or roles listview I add role1 to it.in the explicit permissions
for role1 listbox,I choose select /update/insert/delete/view definition
grant checkboxes.and apply the selects.

10:In the schema1 schema I create a table:create table a(a int ,b
varchar(10));

11:In the dabase role properties-role1 dialog choose securables panel
,int securable listview I add the schema1.a table( choose all the
columns of table a for all permissions),and in explicit permissions for
schema1.a I choose all for grant ,then apply the selects.

12:Now I use login guxiaobo to log into dabase and issue "select * from
a",but got a error msg saying guxiaobo has not enough permission to
select form table a.

Does anyone has found anything I missed?

Oct 12 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Is there anybody who knows what I have miss?

Oct 13 '06 #2

P: n/a
On 11 Oct 2006 17:47:23 -0700, Amber wrote:
do the following steps:
(snip)
>10:In the schema1 schema I create a table:create table a(a int ,b
varchar(10));
To create this table in the schema1 schema, you'll have to specify the
schema. If you don't, it will be created in the dbo schema (since you're
connected as amber, who is database owner.

(snip)
>12:Now I use login guxiaobo to log into dabase and issue "select * from
a",but got a error msg saying guxiaobo has not enough permission to
select form table a.
After correcting the code in step 10 to ensure that the table is made in
the correct schema, I didn't get an error here.

--
Hugo Kornelis, SQL Server MVP
Oct 13 '06 #3

P: n/a
Thanks very kind of you to help me, I made schema1 to be amber's
default schema ,so the create table command works well ,and tables a
locates in schema1 schema.

Oct 16 '06 #4

P: n/a
In step 9 and 11,I also choose the CONTROL permission for deny .Later I
found if I uncheck the CONTROL's deny permission checkbox(let three
options empty),then user guxiaobo can select the tables.

Here comes the question,how the control permission applys to table
object?

Oct 16 '06 #5

P: n/a
Hugo Kornelis,are you here?

Oct 20 '06 #6

P: n/a
On 15 Oct 2006 23:34:03 -0700, Amber wrote:
>In step 9 and 11,I also choose the CONTROL permission for deny .Later I
found if I uncheck the CONTROL's deny permission checkbox(let three
options empty),then user guxiaobo can select the tables.

Here comes the question,how the control permission applys to table
object?
Hi Amber,

Yes, this is expected behaviour.

To understand the security architecture, you must be aware that denying
pernmission is not the same as not granting permission. Not explicitly
granting a permission to a user is enough to make sure that he or she
won't be allowed that action. Explicitly denying a permission is a more
fierce step - you are basically saying that this user really, really,
really should not have this access. Ever. And no amount of granting can
reverse that.

In other words: a DENY will always override a GRANT.

The Books Online page about DENY Object Permissions (see
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/0b8d3ddc-38c0-4241-b7bb-ee654a5081aa.htm)
has a nice table that shows how various object-level permissions inherit
from schema level permissions. You'll see that object-level SELECT
inherits from schema-level SELECT. And the similar table in the page
about DENY Schema Permissions
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/300a67c4-d226-4653-9e9f-7ae4d53fcf33.htm)
shows that schema-level SELECT inherits from schema-level SELECT. So
these two steps combined explain the the SELECT was disalllowed because
the explicit object-level ALLOW SELECT was overriden by the (doubly
inherited) DENY SELECT.
On 19 Oct 2006 21:36:04 -0700, Amber wrote:
>Hugo Kornelis,are you here?
As you see, I am now <g>.

The answer took a while because I also have a family and a job that
actually pays my bills. Giving free support comes only after that - and
if time is limited, I answer questions in various locations in a
round-robin fashion.

--
Hugo Kornelis, SQL Server MVP
Oct 22 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.