none
users unable to Access their database unless they are given sysadmin permission RRS feed

  • Question

  • Hi, I have setup new SQL Server 2014 Ent Ed. I restored a database for the company developers and give the developers login db_owner mapping on the database. I ran T-SQL grant view any database for the developers group and Grant create database for that group. the users can normally access the database from the SSMS but they are unable to access the database from the VB6 application unless they are given sysadmin server role. previously, on the old setup on the same server (which was the also SQL server 2014 Ent.) they were able to access the database through the VB6 application using the ODBC DSN System SQL Driver. The users now have only public server role.  Any suggestions please?

    I also tried all in here 

    https://social.technet.microsoft.com/Forums/sqlserver/en-US/309236fa-d86d-4294-a156-729e2c28b63f/users-are-unable-to-connect-to-sql-server-2005-unless-they-are-in-the-sysadmin-group?forum=sqlsecurity

    but no luck !!


    Tuesday, June 9, 2015 1:23 PM

Answers

  • SOLVED

    Although the public server profile has connect permission and the user active securables was showing connect SQL permission the connect permission was not working somehow !

    Acton to resolve was very simple: (T-SQL)

    use master

    Go

    Grant connect to Public

    Go

    The VB6 users in public profile can connect to the SQL server.

    Hope someone can make use of this information.

    Thanks for every one. 

    • Marked as answer by Munaf Salim Thursday, June 11, 2015 12:30 PM
    Thursday, June 11, 2015 12:30 PM

All replies

  • So exactly what happens when the users try to connect and fails?

    What message do you see in the SQL Server log for the login failure?

    And how does the connect string in the application look like?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, June 9, 2015 9:34 PM
  • I second Erland, please post the error message and that way we'll be able to help what is that causing the login failure. Also, the thread that you have mentioned, says that there was a "Deny Access" property added to the login group/login itself for that server in particular.   Here are two links that I would suggest you to go through http://blogs.msdn.com/b/sqlsakthi/archive/2011/02/06/how-to-troubleshoot-connectivity-failure-error-with-sql-server.aspx and https://msdn.microsoft.com/en-us/library/aa951231.aspx

    Thank you
    Hemantgiri S. Goswami | SQL Server Consultant
    SQL Server Citation| Twitter | Facebook
    Author of book - SQL Server 2008 High Availability

    Wednesday, June 10, 2015 4:37 AM
    Moderator
  • Thank you for your reply

    I got this error mesage


    the connection string is:

    Public Const conn_str = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=admindb;Data Source=Sqlvm01\oilsqlserver"
    Public cn As adodb.Connection
    Public MonthStatus As Boolean

    ____________________________________

    I was suspecting an endpoint issue so I ran grant connect on endpoint for public using master database

    Still the problem not moving.

    Do i have to do something with the ODBC DSN? I already created System DSN with Windows authentication as I am using AD.

    Please advise.

    Thank you



    • Edited by Munaf Salim Thursday, June 11, 2015 11:48 AM
    Wednesday, June 10, 2015 8:06 AM
  • It seems that the app cann't find the SQL Server. Does it exist? Can you connect using some other tool (UDL, SQLCMD, etc) from that machine? Did you configure SQL aliases on that machine from where you connect?

    Tibor Karaszi, SQL Server MVP | web | blog

    Wednesday, June 10, 2015 10:42 AM
  • Hi

    As this is a new SQL Server you may need to turn on the Browser Service so that the Server can be discovered by the other Servers/clients on the Network.

    Leave it on for a few days and then turn the Browser Service off.


    Please click "Mark As Answer" if my post helped. Tony C.

    Wednesday, June 10, 2015 10:49 AM
  • You may be interested in the detailed troubleshooting information that I put on the Wiki How to Troubleshoot Connecting to the SQL Server Database Engine http://social.technet.microsoft.com/wiki/contents/articles/2102.how-to-troubleshoot-connecting-to-the-sql-server-database-engine.aspx

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Wednesday, June 10, 2015 3:42 PM
  • That error message means that you can't find your way to the server at all. Either because it truly does not exist, there is a firewall in the way, the Browser service is not running or something similar. However, it's confusing, because you say it works if you make users sysadmin. Since the problem is that you don't reach SQL Server, the permissions inside SQL Server do not come in play.

    Leave DSNs out of this; you are using OLE DB, not ODBC.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, June 10, 2015 9:17 PM
  • SOLVED

    Although the public server profile has connect permission and the user active securables was showing connect SQL permission the connect permission was not working somehow !

    Acton to resolve was very simple: (T-SQL)

    use master

    Go

    Grant connect to Public

    Go

    The VB6 users in public profile can connect to the SQL server.

    Hope someone can make use of this information.

    Thanks for every one. 

    • Marked as answer by Munaf Salim Thursday, June 11, 2015 12:30 PM
    Thursday, June 11, 2015 12:30 PM