We help IT Professionals succeed at work.

Attach an MDF file using SQL Server Authentication

448 Views
Last Modified: 2012-06-27
We, a group of programmers, are developing an ASP.NET app. The application's connection string attaches an .MDF file that is in the App_Data directory. This works nicely so that the app can be checked out and run without each developer having to manually attach the database to the local SQL server instance.

However, the attached database needs to be very restrictive with permissions. The user account accessing the database should only be granted permissions to stored procedures, not tables, etc. We have setup a SQL Server user  account with the appropriate restrictive permissions. We want to use SQL Server Authentication in the connection string, referencing the aforementioned SQL server user account, when attaching the database, but this doesn't work; we get permissions errors.

I think this is because the SQL user account has so few permissions it cannot even attach the database. I'm also wondering if it is even possible to attach a database using the connection string and SQL server authentication?
Comment
Watch Question

CERTIFIED EXPERT

Commented:
"User must be an administrator or must be running a "User Instance" of Sql Server"

http://devspeed.spaces.live.com/Blog/cns!34211943A0184E9C!200.entry
CERTIFIED EXPERT
Top Expert 2012

Commented:
>>I'm also wondering if it is even possible to attach a database using the connection string and SQL server authentication?<<
You are right, if you have the correct permissions you can do it.  The big question that you have to ask yourself is why you want to attach the database?  Is the database not already attached in SQL Server?  If it is, than all you and all the developers need to do is open it.
CERTIFIED EXPERT

Commented:
acperkins, you normally use AttachDBFilename to be able to keep/have XCopy deployment for the application.

Author

Commented:
acperkins: In the production environment we will definitely have the database directly attached to a SQL server instance. However, in development, with multiple team members, it's much easier for us to dynamically attach the .mdf file using the connection string.

acperkins and TheMegaLoser: The problem with granting the user administrator permissions is that it gives the user too much access to the database. The user should only have access to specific stored procedures that we explicitly grant access to, so the access is very limited. I believe that if we set the user up with admin permissions we would have to explicitly deny the user access to the tables and other objects??

As it is now, we routinely attach the database to a SQL server instance, test that the appropriate user still only has the correct access, and then use a different user with excessive permissions for development convenience. This isn't ideal and goes against the practice of developing with the permissions level that will be adhered to in the production environment.

I very much appreciate your help so far!
CERTIFIED EXPERT
Top Expert 2012

Commented:
>>you normally use AttachDBFilename to be able to keep/have XCopy deployment for the application. <<
I understand, but they are not deploying the database.  They are simply trying to develop with a connection string that attaches the database.

>>However, in development, with multiple team members, it's much easier for us to dynamically attach the .mdf file using the connection string.<<
Really?  Than not to be facetious, but you question sems to indicate otherwise.

>>The problem with granting the user administrator permissions is that it gives the user too much access to the database.<<
Absolutely.  I was not being serious.

I am afraid I don't understand the purpose of attaching and detaching the development database everytime.  That may work for a single developer, for a team it would be out of the question.  Just attempting to make regular backups would be impossible.

Author

Commented:
acperkins: We don't have to attach/detach manually, it is done using the connection string when the application is started. By doing it this way we can commit the MDF using SVN and all devs will receive the latest version when they update their checkouts. Once received the devs don't have to go through the process of reattaching the mdf to a SQL server instance, which can be wearying when the database changes often.

Based on both of your responses, and my own looking around, I don't think it is possible for me to do what I hoped.

I'm going to leave this thread open a little longer, in the hopes someone else may come along with a suggestion.
CERTIFIED EXPERT
Top Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
acperkins: Do you develop applications using source control? Issues 1 and 3 are not relevant, because the context is an SVN checkout, and issue 2 is exactly the problem I am referencing!
CERTIFIED EXPERT
Top Expert 2012

Commented:
If you think that 1 and 3 are not relevant than all I can say you have not been in the shops even remotely similar to the ones I have worked with.  No serious network administrator is going to give developers access to the box that houses SQL Server, let alone the files on that server, even if it is for development only. Period.

I wish you the very best of luck.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.