none
server is not configured for data access error

    Question

  • Hi all, Im relativly new to SQL Server so this may  be a bit of a dumb question.

    I have a user who originally was getting the error "could not find server in sys.servers...." when they were trying to delete a record.

    So I ran select * from sys.servers and sure enough it was not listed

    then

    sp_addserver 'SERVER'

    they are no longer receiving the could not find server error however they are receiving the server is not configured for data access error. 

    What is confusing to me is that this user can log into the database and query for data, just not delete any data.

    I am completely stumped as to what is causing this. Not to mention The servers name isn't even 'server' its 'iO' and the ODBC connection is configured properly.

    any help would be greatly appreciated

    Thanks!

    EDIT: the client is running SQL Server 2005
    Wednesday, November 19, 2008 4:36 PM

Answers

All replies

  • The user should have proper permission to delete records
    Refer Permission paragraph in the following link
    http://msdn.microsoft.com/en-us/library/ms178530.aspx
    Wednesday, November 19, 2008 5:01 PM
  • checked the permissions,  my user and roles are setup so they can delete records


    Wednesday, November 19, 2008 5:48 PM
  • My guess is that you're querying the wrong database.

     

    You can pay attention to the database or start the querys with USE myDatabase

     

    A.D.T.

     

    Wednesday, November 19, 2008 5:51 PM
  • I thought that might be the case as well, but they are querying the right database.
    Wednesday, November 19, 2008 5:55 PM
  • Post the delete statement. It may not be formed correctly or it's trying to delete a sys.object and not a record in which case you would use DROP.

     

    A.D.T.

     

    Wednesday, November 19, 2008 6:04 PM
  • The delete statement was setup by the developer and I have no idea what commands she was using.  However we have never come accross this problem before on any version of sql server.  I did notice that the db_securityadmin role had been dropped from several of users.  I restored it but to no avail,  me thinks foul play is afoot and someone tried to optimize this database without knowing what the *** they were doing. 
    Wednesday, November 19, 2008 7:41 PM
  • I'll second that. Happens a lot.

     

    A.D.T.

     

    Wednesday, November 19, 2008 7:42 PM
  • Well Im going to unmount and remount the database, then recreate the users I dont really think I have any more options at this point. 
    Wednesday, November 19, 2008 7:44 PM
  • well a detach and remount did nothing, nor did recreating the users and roles. 
    Wednesday, November 19, 2008 8:04 PM
  • Did you check the permissions on the table itself?

     

    Edit: You can disable delete in the table properties in management studio.

     

    A.D.T.

     

    Wednesday, November 19, 2008 8:06 PM
  • yup they all had read write delete privileges for the correct users.
    Wednesday, November 19, 2008 8:15 PM
  • Ok...so the delete command from the code doesn't work, can you delete a record from the table manually?

     

    A.D.T.

     

    Wednesday, November 19, 2008 8:45 PM
  • as SA, yes
    Wednesday, November 19, 2008 9:09 PM
  • What account is connection using for the delete command from the code?

     

    You might not know and that account might have limited access.

     

    A.D.T.

     

    Wednesday, November 19, 2008 9:13 PM
  • You can set the DATA ACCESS option the following way:

    exec sp_serveroption @server = 'SRVRNAME\INSTANCENAME' 
    
       ,@optname = 'DATA ACCESS' 
    
       ,@optvalue = 'TRUE' 
    
    

    With this setting OPENQUERY will work on the same server (not only on Linked Server).

    Examples:

    The Power & Flexibility of SQL Server Dynamic SQL

    http://www.sqlusa.com/bestpractices/dynamicsql/


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Friday, May 21, 2010 12:30 AM
  • Thanks.  That worked, but it took 2 passes.  Do I need to add a 'RECONFIGURE statement -- in case I have to do that again.

    I don't understand why I had to do that.  This is the second time I've re-installed SS 2012, and each time there's something else that doesn't work.

    Tuesday, June 11, 2013 4:59 PM