SQL Server and "User Instance" Error: The user instance login flag is not supported on this version of SQL Server. The connection will be closed

If you attempt using the membership/role providers with the .NET framework 2.0, you may get the following error:

The user instance login flag is not supported on this version of SQL Server. The connection will be closed

There could be a number of reasons:

1) You have not run the aspnet_regsql (in the C:\Windows\Microsoft .NET\Framework..) folder to register the required stored procedures for the membership and/or role providers.

2) You are not using SQL Server Express and your connection string has User Instance=True.

3) You downloaded a starter kit and trying to point it to a SQL Server 2005 instance and haven't modified the default Web.config (or forgot to remove User Instance=True in the connection string).

What's the User Instance flag used for?

It is only supported for the express edition of SQL Server. It allows running applications under a least-privilege user account (LUA).

It allows the creation of a separate SQL Server Express instance (spunned off the parent instance) that runs under the security context of the current user. You can read more here.

Rebuilding the master DB for SQL Server 2005

I was starting the SQL Server 2005 instance. The service failed to start.

I checked the event log and found the following error:

Error 2(The system cannot find the file specified.) occurred while opening file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf' to obtain configuration information at startup. An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary.

I went to that folder and sure enough, the master database does not exist. So how do you build one? It appears that you have to change the installation in order to do that.

Control Panel --> Add/Remove Programs --> Microsoft SQL Server 2005.

Click on Change. You should get a Microsoft SQL Server 2005 Maintenance dialogue.

First: Component Selection --> Select the troubled instance.

Next.

Second: Feature Maintenance --> I selected Database Engine.

Next. That should start a Microsoft SQL Server 2005 Setup Dialogue.

Changing the owner of tables

Taken from: http://weblogs.asp.net/owscott/archive/2004/01/30/65229.aspx

The below helps changing ownership of tables (not database. You can follow the same principle to change other types of objects). Please note running the below would could limit access to these tables.

DECLARE @old sysname, @new sysname, @sql varchar(1000)
SELECT
@old = 'OldOwner'
, @new = 'dbo'
, @sql = '
IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES
WHERE
QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?''
AND TABLE_SCHEMA = ''' + @old + '''
)
EXECUTE sp_changeobjectowner ''?'', ''' + @new + ''''
EXECUTE sp_MSforeachtable @sql