Database connection issue using SQL schema user account

[mcotter]

I have an database connection issue that I cannot resolve. Here is
the setup.
I created a database in SQL 2005 Express. The database is owned by a
dbo SQL user account, dboAdmin. I also created two non-dbo SQL user
accounts, schAdmin1 and schAdmin2, each owning a schema inside the
newly created database. Both non-dbo user accounts were granted the
db_datareader and db_datawriter roles. When connecting to the
database in Visual Studio 2005 using a non-dbo user account, I get the
infamous error,

CREATE DATABASE permission denied in database 'master'.
An attempt to attach an auto-named database for file C:
\UnleashYourPower\Database\UnleashYourPower.mdf failed. A database
with the same name exists, or specified file cannot be opened, or it
is located on UNC share.

Below is the connection string

strConn1 = "Data Source=.\SQLEXPRESS; AttachDbFilename=C:\UYP\Database
\UYP.mdf; User ID= schAdmin1; Password = <password>; Connect
Timeout=30; User Instance=False"

The underlying problem is that the non-dbo user accounts do not the
granted privilege to attach the database. If I manually attach the
database using SQL Server Management Studio Express or if I first
attach the database using the dbo user account inside a connection
string, the above connection string works. I could grant the two non-
dbo user accounts the dbo role, but this eliminates the need to create
non-dbo schemas. I will be distributing this database and
application, so I need to attach the database using Visual Basic code.
In summary: How would you connect to a SQL 2005 Express database in
Visual Studio 2005 using a non-dbo SQL user account if the database is
not attached manually or not attached by the dbo?

 

[William Vaughn]

When you use AttachDbFilename, you must provide the Initial Catalog keyword
as well.
Frankly, I would build a deployment setup that attached the database and
then the need to use AttachDBFilename would be abrogated.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________

 

[mcotter]

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

 

[Mary Chipman [MSFT]]

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