User Instances for Non-Administrators

User Instance is a feature that enables non-administrators to run a local version of SQL Server in their own account.  With user instances, non-administrators have database owner privileges over the instance running in their own account. 

Important

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

The user instance, also known as a child or client instance, is an instance of SQL Server that is generated by the parent instance (the primary instance running as a service, such as sqlExpress) on behalf of a user. The user instance runs as a user process under the security context of that user. The user instance is isolated from the parent instance and any other user instances running on the machine. The user instance feature is also referred to as “Run As Normal User” (RANU).

Note

For consistency purposes, SQL Server Express Books Online refers to this feature as user instance. Also, this feature was introduced with SQL Server 2005 Express Edition.

Why User Instance?

Some of the reasons that outline the benefits of using user instance model are listed below:

• The primary goal of the User Instance model is to provide isolation between non-administrator users of SQL Server, thereby allowing users to attach arbitrary databases without the risk of compromising another user. This isolation is achieved by generating a separate instance of SQL Server for each user.

• Besides isolation, the goal of SQL Server Express is also ease of use for non-administrator users. Many Windows users have accounts with administrative privileges. Unfortunately, running as an Administrator makes it easy for malicious software to take over a user's computer. However, malicious software run by non-administrator users cannot make system-wide changes and therefore can do only limited damage.

Overview of User Instance

The user instance model can be summarized by the following points:

• A parent instance of SQL Server must be running to generate user instances.

• Whenever you install SQL Server Express, Setup creates a Template Data folder that stores clean system databases to be consumed by the user instance process, if invoked.

• To invoke the user instance, use the user instance connection string keyword in SQLClient, which can be true or false, and defaults to false. When set to false, no user instances support is in effect and original behavior is preserved. If user instance is set to true, the user instance model will be activated during ADO. NET connection.open call.

  Note

  The System.Data.SqlClient namespace defines a set of classes that enables access to the SQL Server database and that are collectively called the .NET Data Provider for SQL Server, or SqlClient. Managed applications interact with SqlClient through the ADO .NET API to query and manipulate data and schema information stored in SQL Server databases. For more information, see the Visual Studio 2005 documentation.

• The first time a user instance is generated for any user, system databases are copied over from the Template Data folder to a path under the user’s local application data repository directory for exclusive use by the user instance. This path is typically drive:\Documents and Settings\Username\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS.

• The user connects to this new user instance as a SysAdmin and uses the database even if that user is not an administrator on the parent instance.

  Security Note
  Logins provisioned as members of the sysadmin fixed server role during setup are provisioned as administrators in the template database. They are members sysadmin fixed server role on the user instance unless removed.

• When the user instance is generated, SQLClient redirects all calls to the user instance.

Note

The user databases derive their READONLY properties based on the file system attributes and permissions of the primary file for the database. ALTER DATABASE SET READ_ONLY/READ_WRITE settings do not apply.

Connection String for Using User instance

conn string =
"Data Source=.\\SQLExpress" +
"integrated security=true;" + 
"attachdbfilename=|DataDirectory|\mydb.mdf;" +
"user instance=true"
string connstr = GetConnectionString(); // get from config
using(SqlConnection conn = new SqlConnection(connstr)) {
  // this will connect to the user instance, not to the 
  // default SSE instance
  conn.Open();
  // use the connection to the user instance
}

Connecting to a User Instance

The network protocol support for user instances is only through local named pipes, which implies that a user cannot connect to a user instance from a remote computer.

For information about connecting to a user instance by using SQL Server Management Studio, see "SQL Server Management Studio and SQL Server Express" in SQL Server Express Books Online

Note

For more information about named pipes or network protocols, see SQL Server Configuration Manager Help. To access this Help, on the Start menu, click Run, and then type SQLServerManager.msc. This will open the SQL Server Configuration Manager. To access Help, click the Help menu. To see network protocols for SQL Server, click SQL Server Network Configuration.

Administration of User Instances

If you are an administrator and want to manage user instances, the following information will be useful.

View/stored procedure/Transact-SQL	Description
Select * from sys.dm_os_child_instances (Transact-SQL)	Lets you execute a virtual view that lists all the user instances that are generated on the parent instance.
SHUTDOWN	Stops SQL Server. Use this command to stop the user instance. For more information about how to use SHUTDOWN, see SHUTDOWN (Transact-SQL) in SQL Server Books Online.
sp_configure 'user instances enabled'	Controls enabling or disabling of user instance feature. To enable generation of user instances, use: sp_configure 'user instances enabled','1' RECONFIGURE; GO To disable generation of user instances, use: sp_configure 'user instances enabled','0'.
sp_configure ‘user instance timeout’	Use: sp_configure 'show advanced options', 1; RECONFIGURE; GO sp_configure 'user instance timeout', 5; GO Where 5 is the minimum value and 65535 is the maximum value. You need sp_configure ‘show advanced options’ to view and set the timeout. For more information about show advanced options, see Setting Server Configuration Options in SQL Server Books Online. You can set the user instance timeout in both parent instance and user instance. When a user instance starts, it always gets the time out value from the parent instance. However, once the user instance starts, it can use the sp_configure to change the timeout value that is valid only for this specific instance.

Limitations When Using User Instances

When you use user instances, some of the SQL Server Express features might not function as expected. The following list describes limitations that can arise:

• One user can only have one user instance.

• Replication will be disabled.

• User Instance does not support SQL Server Authentication. Only Windows Authentication is supported.

• The network protocol support for user instances is local named pipes only.

• The user instance shares the registry entries of the parent instance.

• There is no support for user instance with native code. This feature is only supported with ADO .NET.

• Full text Search is not supported on a SQL Server Express user instance.

• WMI Provider for Server Events will not be supported on the dynamically spawned user instances. This should still work on the parent SQL Server Express instance. For more information on WMI provider, see WMI Provider for Server Events in SQL Server Books Online.