SQL Server 2008 R2 Express 权限——无法创建数据库或修改用户
最近刚刚升级到 SQL Server 2008 R2 Express.当我在使用 Windows 身份验证和我的 ID myuser 登录后尝试创建数据库时，我收到此错误:
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:
执行 Transact-SQL 语句或批处理时发生异常.(Microsoft.SqlServer.ConnectionInfo)
CREATE DATABASE 权限在数据库master"中被拒绝.RESTORE HEADERONLY 异常终止错误 262
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
如果我尝试将 sysadmin 角色添加到 myuser，这是我收到的错误:
If I try to add the sysadmin role to myuser, this is the error I receive:
为 ServerRole 'sysadmin' 添加成员失败.(Microsoft.SqlServer.Smo)
执行 Transact-SQL 语句或批处理时发生异常.(Microsoft.SqlServer.ConnectionInfo)
用户无权执行此操作错误 15247
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
如果我尝试使用 T-SQL 将此角色添加到我的用户，请使用此命令，
If I try to add this role to my user with T-SQL, using this command,
EXEC sp_addsrvrolemember 'ziffenergymyuser', 'sysadmin';
GO
这是我收到的错误:
消息 15247，级别 16，状态 1，过程 sp_addsrvrolemember，第 29 行
用户无权执行此操作.
Msg 15247, Level 16, State 1, Procedure sp_addsrvrolemember, Line 29
User does not have permission to perform this action.
有人有什么建议吗?看来我不能对本地机器上的数据库做任何事情.请注意，我是我正在使用的 Windows 7 工作站的管理员，如果我尝试使用 SQL Server Management Studio 在我们的网络 IT 测试数据库服务器上创建或修改数据库和/或用户，我可以毫无问题地做到这一点.
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.
推荐答案
您可能是工作站的管理员，但这对 SQL Server 毫无意义.您的登录名必须是 sysadmin 角色的成员才能执行相关操作.默认情况下，本地管理员组不再添加到 SQL 2008 R2 中的 sysadmin 角色.您需要使用其他内容(例如 sa)登录才能授予自己权限.
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.
相关文章