473,383 Members | 2,005 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,383 software developers and data experts.

user/schema problem in SQL Server 2005!

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
6 2707
Is there anybody who knows what I have miss?

Oct 13 '06 #2
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
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
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
Hugo Kornelis,are you here?

Oct 20 '06 #6
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: LineVoltageHalogen | last post by:
I have many stored procs in my database and I can call them just by their name uspMyProc with success always. However, I just created a user function ufnMyFunction as the same user that I created...
3
by: Chris Lieb | last post by:
I am new to XML Schema and am running into a bit of a snag. I have defined an XML-based scripting language for an updater program that I am working on. I would like to make a schema for this...
2
by: daz_oldham | last post by:
Hi All Bit of an odd one - very basically I created an app using the SqlMembershipProvider in VS.NET 2005, and I did it on a MS SQL 2005 server. However, I then find out that for other...
0
by: Will | last post by:
TITLE: Microsoft SQL Server Management Studio The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138) I have an installer that is...
1
by: billharrison9 | last post by:
I need to be able to drop a table after a user is done with it. I have tried something like, DROP TABLE USER.tblEducation_SAP1 but I get an error. Can someone suggest the way I should be...
1
by: System Audit | last post by:
I am working with an old version of SQL Server (6.5), but the same mechanism may exist in later versions. If there were any changes made to the database schema, would this be recorded somewhere...
8
by: send.me.all.email | last post by:
Hi experts, which approaches would you suggest for: - Reading a database schema (tables, fields, relationships) from SQL Server 2005? - Visualizing the DB schema? For developing a DB tool...
1
by: Erwin Moller | last post by:
Hi Group, (I am just starting with SQL Server 2005.) On SQL Server 7 I used often the nice relation schema, where I used to draw out the whole database, especially the Foreign Keys constraints....
2
by: aj | last post by:
SQL Server 2005 64-bit 9.00.3042 SP2 When I map a database user to a login, I can specify a default schema for that user. After that, any SQL from that user w/o an explicit schema will be...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.