Solved

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

Posted on 2014-07-17
4
443 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
  • 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

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.
If you don't know how to downgrade, my instructions below should be helpful.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

763 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