Solved

SQL Permissions

Posted on 2011-03-04
10
355 Views
Last Modified: 2012-05-11
We are using a ODBC connection to allow users to update and change some tables in our SQL Server 2008.  However, it seems as though our permissions has changed on the server as they are not able to do this any more.  Even with a simple select statement.  We are using windows authentication and when I check with the ODBC  connection it test out fine.  

What permissions on the database should I be changing so that they can read, write, update, insert, alter sort of stuff.  I've taken over this SQL server from someone else.  We have a group with all of the users listed in it so I just need to change permissions for that group.  

Also do you need to restart a service or something when you change permissions on the database for it to take affect?

This is the most difficult thing they would do:
INSERT INTO AR.GIS.DEMO (ID, GEOID, GEOTYPE, TITLE, UserName)
SELECT ID, GEOID, GEOTYPE, TITLE, UserName
FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0', 'dBase IV;HDR=NO;IMEX=2; DATABASE=\\MA000XVSQL07\GIS\', 'select * from [DEMO.dbf]') AS DEMO
0
Comment
Question by:flfmmqp
  • 3
  • 2
  • 2
  • +1
10 Comments
 
LVL 15

Expert Comment

by:derekkromm
ID: 35039102
No restart necessary

Here's a list of SQL Server permissions/roles/etc

0
 
LVL 15

Expert Comment

by:derekkromm
ID: 35039105
0
 

Author Comment

by:flfmmqp
ID: 35039223
After further review I think it is the ODBC connection but I don't understand.  The old one AR_SQLDB is the exact same as the new one that I just created with a different name AR.  I don't want to have to go change a bunch of code and redistribute it just to rename the ODBC connection.  I even deleted the old ODBC connection and recreated it and it still does not work.  It used to work.  
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 39

Expert Comment

by:lcohan
ID: 35039241
How is the database accessed by users via ODBC?
Each NT user has it's own ODBC and they are using their own NT credentials to access it via ODBC?
0
 
LVL 15

Accepted Solution

by:
derekkromm earned 250 total points
ID: 35039242
About ODBC: http://msdn.microsoft.com/en-us/library/ms714591(v=vs.85).aspx

If you're having permissions issues, its likely not ODBC that is causing them, but rather the underlying permissions on the database server itself.
0
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 250 total points
ID: 35039323
In my opinion the old one you deleted was configured with a (different) speciffic login/password that had sufficient rights in your server.database and it is obvoiously that's why it worked.
0
 

Author Comment

by:flfmmqp
ID: 35815548
I tried to accept a solution and was blocked.  Please feel free to do whatever you want but do not block me from asking a question.  I understand I left it open but I never felt I got a real answer.  That is the point right?

I have one freaking open question and I am paying for this service to be able to ask questions when I need answers quickly.  Not to have to go in and object to a question just to ask my question.  Horrible customer service and you are wasting my time.  Otherwise if you want to do this then making it a non-paying service and then I can live with these rules.  

0
 
LVL 100

Expert Comment

by:mlmcc
ID: 36275220
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

786 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