1

We are delivering SQL Server 2012 SP1 Express edition as part of database with our application. We setup SQL Server express edition as silent installer that user can choose to install or not.

However, I am seeing problem with windows authentication when Domain\User1 setup SQL Server and Domain\User2 on same machine tries to connect to the same instance created by User1. Though, User2 can log in to the instance created by User1, User2 can't create database or access database created by User1. Following is the error message shown when User1 tries to access database.

Error on Accessing Database

I know that we can add domain users when installing SQL Server manually. However, because we should setup SQL Server as silent installer with command-line parameter, We can't add other domain users. Is there anyway that we can allow multiple domain users to connect to database while still using Windows Authentication?

Improve this question
5
  • 3
    Instead of setting a user via the command line, could you instead set a group? Then you can associate as many users as you'd like to the group via AD.
    – Avarkx
    Sep 19, 2016 at 15:45
  • We ship product as part of Installshild setup package. i.e. as Desktop installer. The setup package will be installed by client themselves and I am not sure if SQL Server accepts group name in command line parameter when installing.
    – Yauvaraj Rimal
    Sep 19, 2016 at 15:50
  • It does, but I'm unclear on how you're currently managing AD users via the silent installer. If part of your InstallSheild package setup takes the domain user as a parameter for passing down to the commandline, changing the label to say "group" rather than "user" would be all there is to it.
    – Avarkx
    Sep 19, 2016 at 15:59
  • Different clients will have their own AD and event we don't know when we create setup package. However, I'll try replacing SQLSYSADMINACCOUNTS and SQLSVCACCOUNT account by Group name and update the question if it works. But, since AD is not in our control, this may not be always a good idea.
    – Yauvaraj Rimal
    Sep 19, 2016 at 16:07
  • 1
    In my opinion, that's a good idea anyway, if you can default to those values but let the user select different accounts if they desire. For instance, I would be mildly annoyed if I was installing something that didn't let me change the service account to my company's dedicated production service account.
    – Avarkx
    Sep 19, 2016 at 16:10

2 Answers 2

Reset to default
1

For troubleshooting purposes you could fine-tune your deployment after installing SQL Express by attaching this one-liner. Feel free to get more granular with security as needed--perhaps a more exclusive user group besides the local USERS group (and DB_OWNER--hopefully is overkill and can be something much less):

sqlcmd -E -S %COMPUTERNAME%\SQLEXPRESS -Q "CREATE LOGIN [BUILTIN\USERS] FROM WINDOWS; USE <your  database>;CREATE USER [BUILTIN\USERS];EXEC SP_ADDROLEMEMBER DB_OWNER,[BUILTIN\USERS]"

p.s. be sure and change to the actual database name of the local SQL Express database. And if SQLEXPRESS is not the actual instance name--change that too. There are alternate syntax variants available as you progress up the SQL 20XX versions.

Improve this answer
0

Thanks everyone for help. I've finally solved the issue. In Command-line I am passing following parameter: /SQLSYSADMINACCOUNTS="BUILTIN\USERS" .

Previously, I was passing /SQLSYSADMINACCOUNTS="NT AUTHORITY\SYSTEM" which would add only current user as sysadmin. BUILTIN\USERS will give any users who can log into that machine a sysadmin privilege.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.