SQL Express相关

User instances are useful for users who are running Windows under a least-privilege user account (LUA) because each user has SQL Server system administrator (sysadmin) privileges over the instance running on her computer without needing to run as a Windows administrator as well. Software executing on a user instance with limited permissions cannot make system-wide changes because the instance of SQL Server Express is running under the non-administrator Windows account of the user, not as a service. Each user instance is isolated from its parent instance and from any other user instances running on the same computer. Databases running on a user instance are opened in single-user mode only, and it is not possible for multiple users to connect to databases running on a user instance. Replication and distributed queries are also disabled for user instances.

For more information, see "User Instances" in SQL Server 2005 Books Online.

Note:

User instances are not needed for users who are already administrators on their own computers, or for scenarios involving multiple database users.

 Enabling User Instances

To generate user instances, a parent instance of SQL Server Express must be running. User instances are enabled by default when SQL Server Express is installed, and they can be explicitly enabled or disabled by a system administrator executing the sp_configure system stored procedure on the parent instance.

Copy Code

-- Enable user instances.
sp_configure 'user instances enabled','1' 

-- Disable user instances.
sp_configure 'user instances enabled','0'

The network protocol for user instances must be local Named Pipes. A user instance cannot be started on a remote instance of SQL Server, and SQL Server logins are not allowed.

 Connecting to a User Instance

The User Instance and AttachDBFilename ConnectionString keywords allow a SqlConnection to connect to a user instance. User instances are also supported by the SqlConnectionStringBuilder UserInstance and AttachDBFilename properties.

Note the following about the sample connection string shown below:

• The Data Source keyword refers to the parent instance of SQL Server Express that is generating the user instance. The default instance is .\sqlexpress.

• Integrated Security is set to true. To connect to a user instance, Windows Authentication is required; SQL Server logins are not supported.

• The User Instance is set to true, which invokes a user instance. (The default is false.)

• The AttachDbFileName connection string keyword is used to attach the primary database file (.mdf), which must include the full path name. AttachDbFileName also corresponds to the "extended properties" and "initial file name" keys within a SqlConnection connection string.

• The |DataDirectory| substitution string enclosed in the pipe symbols refers to the data directory of the application opening the connection and provides a relative path indicating the location of the .mdf and .ldf database and log files. If you want to locate these files elsewhere, you must provide the full path to the files.

Copy Code

Data Source=.\\SQLExpress;Integrated Security=true;
User Instance=true;AttachDBFilename=|DataDirectory|\InstanceDB.mdf;
Initial Catalog=InstanceDB;