Solved

How do I get all users on a PC access to a SQL 2014 Instance / Database

Posted on 2014-07-17
4
446 Views
Last Modified: 2014-07-19
I've developed an app in VB (Visual Studio 2008).  It uses SQL for the database.
I've written code to create an instance in SQL Server and create the database.

Currently I'm trying to have my app check to see if SQL is installed locally, if not then run a silent install of SQL 2014 Express.  Which is working fine, however,  it seems only the user that was logged on when SQL was installed has access to the Database.
For example:
Joe installs my app and it finds that there is no SQL installed.  My app installs SQL, Prompts him to create a database and then the app opens up and all is good.
Now Jane logs into the same PC (Users are local **NO DOMAIN**) and tries to use my app.  And gets the error that her username cannot logon to the SQL.
Here's my unattend code for installing the SQL 2014 Express with tools:
Process.Start("C:\SQL2014WT\setup.exe", "/q /Action=Install /IACCEPTSQLSERVERLICENSETERMS /Hideconsole /Features=SQLEngine,Tools /InstanceName=DT_DBS /SQLSYSADMINACCOUNTS=""NT AUTHORITY\SYSTEM"" /SQLSVCACCOUNT=""NT AUTHORITY\SYSTEM"" /BROWSERSVCSTARTUPTYPE=""Automatic""")
When I open SQL Management Studio as Joe, I can add Jane giving her access,  How can I allow ALL users access without having to do this manually.  And YES builtin/users is there but doesn't help.
0
Comment
Question by:ktaczala
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 40204832
We tried the installation with your exact command parameters (but without creating any databases), and once sql server 2014 express was installed, another non-admin non-domain user of the same machine was able to logon to it without any extra efforts.

What is the exact error message that you receive?
0
 
LVL 12

Author Comment

by:ktaczala
ID: 40205376
Ok, My question wasn't quite correct, Yes other users can indeed connect to the SQL Instance, but not to the database.

So how can I give access to a database I create within my app, to all users?

The Clip1.jpg shows the error from within my app when user2 tries to open the database DT_test1.
The Clip2.jpg show the error from within SQL management studio when user2 tries to expand the Database DT_test1

SQL was installed with user1 logged in, all is good for user1.

Remember I'd like the user not to have to do any configuration manually.
clip1.jpg
clip2.jpg
0
 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 500 total points
ID: 40205410
The first message is not about having no access, it's about "login failed", such as because of wrong password. Or, maybe, empty password (I'm not sure if that's the case, but might be)

> So how can I give access to a database I create within my app, to all users?

According to http://msdn.microsoft.com/en-us/library/bb669065(v=vs.110).aspx , by either enabling guest account, or by mapping users to public role.
0
 
LVL 12

Author Comment

by:ktaczala
ID: 40205805
Here's my solution:
            str = "USE " & dbname & ";" & "ALTER SERVER ROLE [sysadmin] ADD MEMBER [BUILTIN\Users];"
            obj.CommandText = str
            obj.ExecuteNonQuery()
This gives all users on a PC access to all instances & databases.  My app needs users to be able to add, delete, backup & restore databases as well as create instances.  So I know it's not proper to give total access, but this works.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

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.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

617 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