SQL Server 2008 R2 Express权限-无法创建数据库或修改用户 [英] SQL Server 2008 R2 Express permissions -- cannot create database or modify users

问题描述

最近刚刚升级到SQL Server 2008 R2 Express.当我使用ID为myuser的Windows身份验证登录后尝试创建数据库时，收到此错误:

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 'ziffenergy\myuser', '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.

这篇关于SQL Server 2008 R2 Express权限-无法创建数据库或修改用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！