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!
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
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
Or even better, she could just use Ward's script that does
the automation of administrator account recovery steps for her
- Krzysztof Kozielczyk