Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 449
  • Last Modified:

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

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
ktaczala
Asked:
ktaczala
  • 2
  • 2
1 Solution
 
Vadim RappCommented:
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
 
ktaczalaAuthor Commented:
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
 
Vadim RappCommented:
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
 
ktaczalaAuthor Commented:
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now