I ran across this error in an ASP.NET project that was using ADO.NET to access the data in a .csv file (more info here). After much gnashing of teeth, I finally ran across this blog post, that explained the problem and provided a link to this MSDN article.
Basically, you need to make sure that the user has permissions on both the file in which the .csv file resides, and also the file in which the Jet Engine will create temporary files. It is this last piece that I didn’t know, and lead to days of heartache.
So I thought I’d post, in the hopes of minimizing the heartache for others…
You can use SQL Server Database Roles to control access to various parts of your application. Let’s say I have created a .NET application. I have a menu called Administration and I only want certain users to be able to see the menu. One way to do this is to use Active Directory Groups. Another way is to use SQL Server Database roles.
For this example I create a role called MyAppAdmin. Only members of MyAppAdmin should be able to see the Administration menu. After creating the role and assigning appropriate permissions to the role, I want to check, within my .NET application, to see if the logged on user is a member of the MyAppAdmin role. Here’s how to do this:
Create a stored procedure:
CREATE procedure [dbo].[uspTestDatabaseRole]
— Returns 1 if user is a member of the specified role or if user is dbo
declare @Return integer
if USER_NAME() = ‘dbo’
select @Return = 1
select @Return = IS_MEMBER(@RoleName)
In this procedure the variable @RoleName refers to the Database Role you are checking. You can also use it to check membership in a Windows group. You may notice if the person calling the stored procedure is dbo, I return 1 no matter what. You may not want the procedure to work this way so you should modify for your own purposes.
Now all you need to do is call this procedure from your .NET code and it will return 1 if the person is a member of the database role you are checking or if the person is dbo. Otherwise, it will return 0.