Solved

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

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used.

791 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