Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  Development Tools  ASP.NET  AttacheDB Connection String VS .NET 2005

Author  Topic  mcotter Starting Member 3 Posts Posted - 2008-01-09 : 17:04:18 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?