If you have SQL Server 2008 Express installed in your computer, but you can’t manage it even though you are an administrator of the computer. For example, you try to create a database through SQL Server Management Studio and receive this error message:
Create failed for Database database name.
CREATE DATABASE permission denied in database ‘master’. (Microsoft SQL Server, Error: 262)
Or you try to delete any existing database and receive the error message:
Cannot drop the database ‘database name’, because it does not exist or you do not have permission.
The above error messages clearly stated that you don’t have permission on the SQL Server instance. This is because the person that performed SQL Server 2008 Express installation didn’t add your user account as SQL Server Administrators (sysadmin role). It is different from the previous version, SQL Server 2005 Express, that local Administrators group is automatically granted sysadmin role. On SQL Server 2008 Express, you have to add user account to sysadmin role manually while installing as the figure below.
It could be a problem if you can’t reach the person who performed installation. Fortunately, there is a script that can solve the issue which allows you to add current user to SQL Server Administrator (sysadmin role) within few clicks. The script is Script to add the current user to the SQL Server ‘sysadmin’ role. The requirement to run the script is that you must be a member of the local Windows Administrators group, or have access to the credentials of a user who is. The section below shows you step-by-step guide of how to use the script to add current user as sysadmin role on local instance of SQL Server 2008 Express.
Note: This script will restart the specified SQL Server instance (by default, SQLEXPRESS). All active connections to this instance will be lost. For this reason, it should not be used against production servers.
Take ownership of SQL Server 2008 Express
- Download the script by browse to Script to add the current user to the SQL Server ‘sysadmin’ role. Click on Downloads tab.
- Click addselftosqlsysadmin.cmd to download the file.
- Logged on with user account that you want to add to SQL Server 2008 Express (the account has to be in local Windows Administrators group). Then, run the file. It will ask for instance name of SQL Server. You can leave it as default value (SQLEXPRESS) by press Enter if you are not sure.
- The script will try to add sysadmin role to the current user. If there is no error, you will see message as figure below which indicates that the current user has been added sucessfully.
‘Win7-PC\linglom’ was successfully added to the ‘sysadmin’ role.
Note: The SQL Server instance will be restarted on this step.
- Now the current user has sysadmin role and can performs any task on the SQL Server 2008 Express instance.
- If you want to add other account (not the current account), you can provide parameters to the script. The first parameter is the instance name of SQL Server 2008 Express and the second parameter is an user account (locally). But you must run the script as Administrator. For Windows Vista or Windows 7, you have to open command-prompt as Administrator if you have UAC enabled.
USAGE: addselftosqlsysadmin.cmd instance-name local-user-account
thanks for the help !