EAReq
asked on
Attach an MDF file using SQL Server Authentication
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?
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?
>>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.
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.
acperkins, you normally use AttachDBFilename to be able to keep/have XCopy deployment for the application.
ASKER
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!
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!
>>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.
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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
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.
I wish you the very best of luck.
http://devspeed.spaces.live.com/Blog/cns!34211943A0184E9C!200.entry