Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-07-17
4
Medium Priority
?
448 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 13

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 1500 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 13

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

688 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