SQL Server 2008 R2 Express 权限 - 无法创建数据库或修改用户
最近刚刚升级到 SQL Server 2008 R2 Express。当我使用 Windows 身份验证以我的 id myuser 登录后尝试创建数据库时，我收到此错误：
执行 Transact-SQL 语句或批处理时发生异常。 (Microsoft.SqlServer.ConnectionInfo)
数据库“master”中的 CREATE DATABASE 权限被拒绝。 RESTORE HEADERONLY 异常终止错误 262
如果我尝试将 sysadmin 角色添加到 myuser，这是我收到的错误：
为 ServerRole“sysadmin”添加成员失败。 （微软.SqlServer.Smo）
执行 Transact-SQL 语句或批处理时发生异常。 (Microsoft.SqlServer.ConnectionInfo)
用户无权执行此操作错误 15247
如果我尝试使用 T-SQL 使用此命令将此角色添加到我的用户，
EXEC sp_addsrvrolemember 'ziffenergy\myuser', 'sysadmin';
GO
这是我收到的错误：
消息 15247，级别 16，状态 1，过程 sp_addsrvrolemember，第 29 行
用户无权执行此操作。
有人有什么建议吗？看来我无法对本地计算机上的数据库执行任何操作。请注意，我是正在使用的 Windows 7 工作站的管理员，如果我尝试使用 SQL Server Management Studio 在我们的网络 IT 测试数据库服务器上创建或修改数据库和/或用户，我可以毫无问题地做到这一点。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
来晚了，但我发现这个 非常棒如果您没有 sa 密码，则可以了解如何控制 SQLExpress 实例的分步指南。我使用此过程不仅重置了我的 sa 密码，而且还将我的域帐户添加到了所有可用的服务器角色中。我现在可以使用正常登录创建数据库、更改登录、执行批量操作、备份/恢复等。
总而言之，您可以使用 SQL Server 配置管理器将实例置于单用户模式。当您连接时，这会将您提升为系统管理员，使您能够设置一切。
编辑：我已经复制了以下步骤 - 感谢上面链接的原作者。
例子：
来自：-dc:\Program Files\Microsoft SQL........................（直到字符串结尾）
到：-m;-dc:\Program Files\Microsoft SQL........(直到字符串结尾)
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.
example:
from: -dc:\Program Files\Microsoft SQL.............(til end of string)
to: -m;-dc:\Program Files\Microsoft SQL.............(til end of string)
您可能是工作站的管理员，但这对 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.
在 SSMS 2012 中，您必须使用：
要启用单用户模式，在 SQL 实例属性中，不要转到“高级”标签，已经有一个“启动参数”标签。
In SSMS 2012, you'll have to use:
To enable single-user mode, in SQL instance properties, DO NOT go to "Advance" tag, there is already a "Startup Parameters" tag.
我按照killthrush的答案中的步骤进行操作，令我惊讶的是它不起作用。以 sa 身份登录，我可以看到我的 Windows 域用户，并使他们成为系统管理员，但是当我尝试使用 Windows 身份验证登录时，我在登录名下看不到我的登录信息，无法创建数据库等。然后它击中了我。 该登录可能与另一个同名的域帐户（具有某种不正确的内部/隐藏 ID）相关联。我不久前离开了这个组织，几个月后又回来了。他们没有重新激活我的旧帐户（他们可能已删除），而是创建了一个具有相同域\用户名和新内部 ID 的新帐户。使用 sa 我删除了旧的登录名，使用相同的名称重新添加它并添加了 sysadmin。我使用 Windows Auth 重新登录，一切看起来都正常。我现在可以查看我的登录信息（和其他登录信息），并且可以使用 Windows 身份验证登录信息执行作为系统管理员所需执行的任何操作。
I followed the steps in killthrush's answer and to my surprise it did not work. Logging in as sa I could see my Windows domain user and had made them a sysadmin, but when I tried logging in with Windows auth I couldn't see my login under logins, couldn't create databases, etc. Then it hit me. That login was probably tied to another domain account with the same name (with some sort of internal/hidden ID that wasn't right). I had left this organization a while back and then came back months later. Instead of re-activating my old account (which they might have deleted) they created a new account with the same domain\username and a new internal ID. Using sa I deleted my old login, re-added it with the same name and added sysadmin. I logged back in with Windows Auth and everything looks as it should. I can now see my logins (and others) and can do whatever I need to do as a sysadmin using my Windows auth login.
我的 Windows 计算机上有 2 个帐户，其中一个帐户遇到了此问题。我不想使用
sa帐户，我想使用 Windows 登录。对我来说，我需要简单地登录到用于安装 SQL Server 的另一个帐户，然后从那里添加新帐户的权限（SSMS > 安全 > 登录 > 添加登录）
获取完整域名的简单方法是在打开的 cmd echo 中添加每个域名。
为该用户添加登录名，并为其授予 master 数据库和您想要的其他数据库的所有权限。当我说“所有权限”时，请确保不要检查任何“拒绝”权限，因为这会起到相反的作用。
I have 2 accounts on my windows machine and I was experiencing this problem with one of them. I did not want to use the
saaccount, I wanted to use Windows login. It was not immediately obvious to me that I needed to simply sign into the other account that I used to install SQL Server, and add the permissions for the new account from there(SSMS > Security > Logins > Add a login there)Easy way to get the full domain name you need to add there open cmd echo each one.
Add a login for that user and give it all the permissons for master db and other databases you want. When I say "all permissions" make sure NOT to check of any of the "deny" permissions since that will do the opposite.
我遇到了同样的问题，直到我发现这个。
我打开一个新查询并运行以下命令
，然后右键单击“数据库”->
恢复数据库->选择设备->单击...->添加您的数据集 ->点击确定I got the same problem till I found this.
I open a new query and run the following
then right-click on
Databases->Restore Database-> selectDevice-> click...->ADDyour dataset -> ClickOk