SQL Server 2005中“创建用户"所需的权限? [英] Permissions required for 'CREATE USER' in SQL Server 2005?

问题描述

我正在尝试从我的应用程序以及自定义应用程序用户行中创建SQL Server登录名和数据库用户.我希望这些用户能够创建其他用户-即该应用程序将控制哪些人可以/不能创建用户，但是我需要所有用户都具有创建SQL Server登录名和数据库用户的权限.

我已经拥有服务器登录权限，即现有的用户/登录名可以通过将登录名添加到"securityadmin"服务器角色中来创建新的登录名，该角色将授予"ALTER ANY LOGIN"特权.

我试图对数据库用户执行相同的操作-将它们添加到'db_accessadmin'数据库角色中-据说该数据库授予ALTER ANY USER特权，这是CREATE USER所必需的.

但是，任何时候我尝试使用具有上述特权的用户来创建新的数据库用户时，都会出现权限异常.

我尝试手动向特定用户授予ALTER ANY USER权限(GRANT ALTER ANY USER to demouser)，但这也不起作用.

解决方案

从技术上讲，是的.无论是对还是错...无可奉告.

无论如何，数据库安全性分为两个功能:

• db_accessadmin来管理用户(或您提到的"ALTER ANY USER"权限)
• db_securityadmin 允许您管理角色成员身份和对象权限(或更改任何角色权限)

sp_addrolemember 提到过. /p>

通过运行sp_addrolemember，您实际上是在更改角色而不是用户，因此只要具有完全的db_owner权限，"ALTER ANY ROLE"就足够了.

I am trying to create a SQL server login and database user from within my application, along with a custom application user row. I want these users to be able to create other users - i.e. the application will control who can/can't create users but I need all users to have permissions for creating SQL server logins and database users.

I have got the server login permissions working - i.e. an existing user/login can create a new login - by adding the logins to the 'securityadmin' server role - which grants the 'ALTER ANY LOGIN' privilege.

I tried to do the same with the database users - adding them to the 'db_accessadmin' database role - which supposedly grants the ALTER ANY USER privilege, which is required for CREATE USER.

However any time I try to create a new database user using a user with the above privileges I get a permissions exception.

I have tried manually granting the ALTER ANY USER permission to a particular user (GRANT ALTER ANY USER TO demouser) but this doesn't work either.

解决方案

Technically, yes. Whether it's right or wrong... no comment.

Anyway, database security is split into 2 functions:

• db_accessadmin to manage users (or "ALTER ANY USER" permission as you mentioned)
• db_securityadmin allows you to manage roles memberships and object permissions (or "ALTER ANY ROLE permission)

This is mentioned for sp_addrolemember.

You are actually changing the role, not the user, by running sp_addrolemember so "ALTER ANY ROLE" is enough without having full db_owner rights.

这篇关于SQL Server 2005中“创建用户"所需的权限?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！