Sql-server – In Microsoft SQL Server’s linked server configuration, what does the “RPC” setting do

Please stop using the user instance=true setting. Not only has this feature been deprecated, but what is actually happening here is that Management Studio is firing up a different user instance of SQL Server than the one your application is using, so it is not surprising that you can't see the same thing in both cases.

(Usually, the symptom of this is that someone runs an update from their app, then checks the table in Management Studio, thinking there is a bug but not realizing that the update affected a different copy of the database.)

Create the database on your running SQL Server instance (or attach it there). Then connect to that server the same way, without the User Instance setting, from both SSMS and your app. Lots more information here. Don't create the database every time the application starts - that just sounds horrendously wrong.

First thing, never set Max Number of Concurrent Connections on the server properties page to 1. This option can even prevent administrators logging in, but in any case the Dedicated Administrator Connection can still be used.

1. Restart services using SQL Server Configuration Manager, try to login and if not successful, use the Dedicated Administrator Connection.
2. Right click on the Instance name, and change the value for Max Number of Concurrent Connections to 0 (which means maximum) and restart services.

Sometimes, after setting Max Number of Concurrent Connections to 1, on login you may face the following error message:

Cannot open user default database. Login failed.
Login failed for user ‘UserName’

To fix this: In the login window, click on the Options button and in the default database item, select a database like tempdb and then press login.

If the database is in Single_User mode

Using T-SQL:

USE [master];
GO
ALTER DATABASE [YourDatabaseNameHere] SET MULTI_USER WITH NO_WAIT;
GO

Using SSMS:

• Right click on required database --> Properties
• On the left of Database properties window, click on Options
• On the righthand side, scroll down and at bottom you will find a status section
• Change the Restrict Access value to multi_user

If the database is in Single_User mode, and you want to change it to Multi_User, but can't access it as someone else already have the connection, then:

1. If you have ALTER ANY CONNECTION rights (part of sysadmin and processadmin fixed server roles), run SP_WHO or SP_WHO2, find the process using the target database and execute:

   KILL processid (Ex: KILL 87)
   

   then execute above T-SQL to bring your database back to multi_user.

2. Or, you can detach and then attach your database and execute the T-SQL above to bring it to multi_user. This is not recommended on production servers.