none
Sql Server 2008 R2 express - Instance available to all users?

    Question

  • I am attempting to build a sql server 2008 R2 installer for XP (yes i know but the place i work at still uses XP!)

    Here is the problem:

    The Sql Server Express 2008 R2 installer works fine for the Admin user of the PC who installed it. The software that uses the Sql Server runs brilliantly.

    osql -L returns the correctly named instance.

    However when another user logs into that pc, the Sql Server is available - but the created Instance "MyCoolSqlInstance" is not.

    osql -L returns an empty list.

    How do I make the instance available to all users of the PC? (there will be Admin and non Admin users). 

    cheers

    Buzz

    • Changed type Bryce Burrows Monday, January 13, 2014 6:09 AM its a question
    Monday, January 13, 2014 4:04 AM

Answers

All replies

  • Hello,

    Do the non admin users have access to the SQL Server instance? To maje the instance available to all users of the PC,

    add "NT Authority\Authenticated Users" to the SQL security list.

    http://support.microsoft.com/kb/243330/en-gb

    Name: Authenticated Users
    Description: A group that includes all users whose identities were authenticated when they logged on. Membership is controlled by the operating system.

    But the best way of providing access to everyone would be via SQL Authentication. Enable SQL authentication in SQL and use SQL Authentication mode to connect to SQL.

    Regards,

    Don [MSFT]


    Regards, Don Rohan [MSFT]

    Monday, January 13, 2014 7:41 AM
  • Hi Don, thanks for that

    So i fired up Sql Server Managemment Stuudio and added "NT Authority\Authenticated Users" 

    to the "Logins" under Security, rebooted and logged in as a (Non admin)user and XXXXXXXXXXX it worked.

    I was 

    sqlcmd -L stills returns an empty list.

    sqlcmd -S .\Database -U sa - P password

    lets me log in to the server

    and i can connect via the management tool.

    But i have progress, thank you sir.


    buzz




    • Edited by Bryce Burrows Tuesday, January 14, 2014 5:27 AM fixed my mistake
    Tuesday, January 14, 2014 4:29 AM
  • Hi,

    If I understand correctly, you want to give everyone access permission to your SQL instance. It is no recommended to do this design, since it is low security and your important data may be modified by other users.

    If you are only for testing purpose, you can add the Authenticated user to the sysadmin server role. Members of the sysadmin fixed server role can perform any activity in the server.

    For understanding Server-Level Roles, see:

    http://msdn.microsoft.com/en-us/library/ms188659.aspx

    Additional information:

    User Instances for Non-Administrators

    http://technet.microsoft.com/en-us/library/ms143684(v=sql.105).aspx

    Thanks.


    Tracy Cai
    TechNet Community Support


    Tuesday, January 14, 2014 8:35 AM