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.
You may be an administrator on the workstation, but that means nothing to SQL Server. Your login has to be a member of the sysadmin role in order to perform the actions in question. By default, the local administrators group is no longer added to the sysadmin role in SQL 2008 R2. You'll need to login with something else (sa for example) in order to grant yourself the permissions.