M
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?
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?