How to share SQL LocalDb with other users on same machine?

Question

I am looking for ways to access LocalDb database between users using the share command of sqllocaldb command utility but I cannot see the database created in one user in another.

Here are my steps:

1. Login to user User1 on Windows 7

2. Create a new database called test using sqlcmd as follows

   sqlcmd -S (localdb)\v11.0 create database test

3. Share the instance v11.0 as follows with User2

   sqllocaldb share domain\User2 v11.0 myinstance

4. Switch to User 2

5. Login to shared instance as follows

   sqlcmd -S (localdb)\.\myinstance

   select name from sys.master_files

   The above query does not display test database.

Why am I not able to see the databases in User 2 that was created in User1 after sharing?

I thought it must work based on blog (approach 2)http://blogs.msdn.com/b/sqlexpress/archive/2011/12/09/using-localdb-with-full-iis-part-2-instance-ownership.aspx#SharedLocalDB by Krzysztof Kozielczyk but it does not.

Any help/suggestion is appreciated.

Thanks Pare

Solution 1

The problem is that LocalDB runs as a process owned by the current user, meaning each user runs their own instance of LocalDB. This is what makes it zero configuration and very easy to work with. Unfortunately, it also means it's difficult to have a shared LocalDB instance. I was running into a similar issue, and just switched to using SQL Express for a db I needed accessible by multiple users in development.

Solution 2

The reason this isn't working is because you're issuing the wrong command. The usage instructions from sqllocaldb.exe are:

share|h ["owner SID or account"] "private name" "shared name"

Based on this, your second command should be the SID or account that created the instance, not with the account you want to share the instance with. Sharing shares an instance with all accounts on the machine.

Assuming that your account created the instance you want to share, then the command you should issue is:

sqllocaldb share v11.0 myinstance

Otherwise, it should be:

sqllocaldb share "<account that created the instance>" v11.0 myinstance

In either case, you also need to ensure that you are running an elevated command window so it can be successfully shared.

Comments

[Admin]

Thanks for your reply. But "share" option is really mis leading. Isn't it?

[Admin]

I have marked it as answer becuase you had the same problem and it is not possible to access SQL LocalDb from another user by "share".

[Alex M]

I needed to restart the instance as well, only after that could access a db through shared name from IIS.

[Hassan Tariq]

How did you restart the instance?

[Martin Costello]

I would imagine sqllocaldb stop myinstance then sqllocaldb start myinstance would do it.

[raddevus]

Works exactly as explained. Great stuff!! Thanks