Ward Beattie, one of our developers, has published a nice script that allows you to get sysadmin privilege to your local SQL Server Express instance. Obviously, you need to have administrative rights on your machine or access to an account that does.

The script can be very useful in the following situation:

When Visual Studio 2010 RC is installed by one user and being used by another user, the second user is unable to create databases, tables and other objects in SQL Server 2008 Express instance that is installed by Visual Studio 2010 RC. This is because only the user who installed SQL Server 2008 Express is granted sysadmin role in the SQL Server 2008 Express. So even if the second user has administrative privileges to her machine, she will not be able to manage the local SQL Server 2008 Express instance.

If this is your problem, just run Ward's script and become the master of your SQL Server 2008 Express!

Some Background

As surprising as it may seem at first, it all makes sense, when we think about it a little bit deeper. The user who installs SQL Server 2008 Express is granted sysadmin role during the installation. There is no way SQL Server 2008 Express could guess who else may be developing database applications and we certainly wouldn't like to give administrative access to the SQL Server instance to everyone J.

In the past (SQL Server 2005 Express) we were also granting sysadmin role to local Administrators group. But this doesn't help on Windows Vista and above, because User Account Control (UAC) is hiding the Administrators group from the security token assigned to the Visual Studio process. In order to be able to create database objects every Windows Vista or above user would have to run Visual Studio in elevated mode ("Run as Administrator"), which is not recommended (and inconvenient).

So the second user in our example has basically two options. She can ask the first user - the one who installed SQL Server 2008 Express - to grant sysadmin rights to her. Note that unless the first user is familiar with managing SQL Server security via T-SQL scripts, the task is not straightforward and may require installing SQL Server Management Studio Express on the target machine.

If the second user is a local administrator (or can get help from one), she has one more option available. She could follow steps for SQL Server administrator password recovery described by Raul Garcia here. She could then automate those steps in a form of a command script that could be reused next time she finds herself without access to her local SQL Server 2008 Express.

Or even better, she could just use Ward's script that does the automation of administrator account recovery steps for her

- Krzysztof Kozielczyk