Re: Database connection issue using SQL schema user account
- From: "Mary Chipman [MSFT]" <mchip@xxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 11 Jan 2008 11:25:10 -0500
User-schema separation has introduced a whole new level of confusion
that didn't exist before SQLS 2005. For example:
--Schemas are intended to be used for grouping objects, much like a
namespace. They can simplify permissions insofar as being able to have
new objects created inside of a schema inherit permissions assigned to
the schema, but no permissions are inherited from a schema by users;
they are only inherited by the database objects inside the schema.
--The dbo user account is not the same thing as the dbo default
schema. The dbo user maps to db_owner/sysadmin. The dbo schema in 2005
serves the purpose of providing a default schema that is backwards
compatible with earlier versions.
--Users who are assigned the dbo schema do not inherit the permissions
of the dbo user account. There is no reason why you need to have
schemas owned by different users who have restricted privileges -- dbo
can work just fine because each schema can have its own set of
permissions that objects inherit that are independent of the dbo user
account.
It looks to me like you may have created users with restricted
permissions by granting them only db_datareader and db_datawriter
roles. That means they don't have permission to do anything else but
read and write data in one particular database. So I think the issue
may be one of permissions rather than schemas, although it's hard to
tell without knowing more about it. I'd recommend taking a look at
SQLS BOL http://msdn2.microsoft.com/en-us/library/ms190387.aspx,
ADO.NET http://msdn2.microsoft.com/en-us/library/bb669061.aspx and SQL
Server MVP Erland Sommarskog's web site
http://www.sommarskog.se/grantperm.html. This will help you get a
handle on designing an appropriate security model that will work for
you.
--Mary
On Wed, 9 Jan 2008 03:57:20 -0800 (PST), mcotter@xxxxxxxxxxxxxxx
wrote:
Thanks for your responds William..
I believe the initial catalog parameter is not required because I set
the default database setting when creating the user login account. I
tried this parameter anyways but still received the same error.
I am still in the development phase of this project so the deployment
setup is not an option. I am still puzzled why you must be a local
administrator or a dbo to attach a database. This requirement
eliminates the powerful use of database schemas. Is this an oversight
by Microsoft or am I missing something
- References:
- Database connection issue using SQL schema user account
- From: mcotter
- Re: Database connection issue using SQL schema user account
- From: William Vaughn
- Re: Database connection issue using SQL schema user account
- From: mcotter
- Database connection issue using SQL schema user account
- Prev by Date: Memory footprint of expression columns
- Next by Date: LIMIT 1,10 versus Sql Server: TOP
- Previous by thread: Re: Database connection issue using SQL schema user account
- Next by thread: Index Questions
- Index(es):
Relevant Pages
|
