Recently just upgraded to SQL Server 2008 R2 Express. When I attempt to create a database after logging in using Windows Authentication with my id myuser I receive this error:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
CREATE DATABASE permission denied in database 'master'. RESTORE HEADERONLY is terminating abnormally Error 262
If I try to add the sysadmin role to myuser, this is the error I receive:
Add member failed for ServerRole 'sysadmin'. (Microsoft.SqlServer.Smo)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
User does not have permission to perform this action Error 15247
If I try to add this role to my user with T-SQL, using this command,
EXEC sp_addsrvrolemember 'ziffenergy\myuser', 'sysadmin';
GO
Here is the error I receive:
Msg 15247, Level 16, State 1, Procedure sp_addsrvrolemember, Line 29
User does not have permission to perform this action.
Does anyone have any suggestions? It seems that I can't do anything with database on the local machine. Please note that I am the administrator on the Windows 7 workstation I am using, and if I try to create or modify databases and/or users on our network IT Test database server using SQL Server Management Studio, I can do that with no problem.
Coming late to the party, but I found this fantastic step-by-step guide on getting control of your SQLExpress instance if you don't have your sa password. I used this process to not only reset my sa password, but I also added my domain account to all the available server roles. I can now create databases, alter logins, do bulk operations, backups/restores, etc using my normal login.
To summarize, you use SQL Server Configuration Manager to put your instance into single-user mode. This elevates you to sysadmin when you connect, allowing you the ability to set everything up.
Edit: I've copied the steps below - kudos to the original author of the link above.