Cannot open a sql file from Management studio.

Posted on 2012-08-31
Last Modified: 2012-09-04

A user on one of the client machines is trying to open a file using SQL Management studio but he gets access denied error message.
He can connect to any of the servers with his credentials and execute queries or do any DDL
or DML operations, though.
Any ideas?

Question by:isonali
    LVL 18

    Expert Comment

    Opening  a file has nothing to do with the user rights in SQLServer. Did you check if the user has read /write permissions for the file  (or the folder) ?

    Author Comment

    Basically when the user clicks on file->open in SQL management studio , it says access denied,
    its not specific to any file or folder.
    LVL 18

    Accepted Solution

    This is strange. It might be the default folder which is going to be opened in the file dialog box. Still not SQL issue - try to add the user as local administrator to check if this will help
    LVL 5

    Expert Comment

    Try below:

    Right click on the file ->properties->choose security tab-> edit->click on add->give the username->click ok button->select full control in the below permissions for administrator tab-> click ok.

    Now try to open the file through SSMS FILE option or drag and drop the file directly on query optimizer window.
    LVL 82

    Expert Comment

    by:Dave Baldwin
    What kind of file?  You should be able to open an SQL file but I thought that an MDF has to be 'attached' to the server before you can look at it.
    LVL 75

    Expert Comment

    by:Anthony Perkins
    Yes, that explains it.  I suspect the user is trying to open a data file and not a SQL Script.  Since the database is already attached, there is no need to "open" it and the user just needs some basic lessons using SSMS.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now