如何在Azure SQL数据库中授予用户sysadmin权限或如何为Azure SQL数据库用户提供同级别的访问权限?
使用 Azure 门户。应该使用服务器级主体登录来授予对各个数据库的访问权限。
可以创建新用户并授予一个或多个数据库的权限:
-- in master
create login [XXXX] with password = 'YYYYY'
create user [XXXX] from login [XXXX];
-- if you want the user to be able to create databases and logins
exec sp_addRoleMember 'dbmanager', 'XXXX';
exec sp_addRoleMember 'loginmanager', 'XXXX'
-- in each individual database, to grant dbo
create user [XXXX] from login [XXXX];
exec sp_addRoleMember 'db_owner', 'XXXX';
如果您刚刚创建了登录名和相应的用户,则必须添加适当的角色成员资格...exec sp_addrolemember 'dbmanager', 'login1User';
exec sp_addrolemember 'loginmanager', 'login1User';
sp_addrolemember
已被弃用。请改用ALTER ROLE [db_owner] ADD MEMBER [XXXX]
。 - Henrik Høyer在 Azure SQL 数据库中,您无法将 sysadmin
授权给登录名[1]。
Azure 数据库中有两个特殊角色[2]:
dbmanager
loginmanager
相反,在 master
数据库中有两个特殊的数据库角色。
USE master
CREATE LOGIN myadmin WITH PASSWORD = '<some password>'
CREATE USER myadmin FOR LOGIN myadmin
ALTER ROLE dbmanager ADD MEMBER myadmin
ALTER ROLE loginmanager ADD MEMBER myadmin
对于服务器上的每个数据库
CREATE USER myadmin FROM LOGIN myadmin
ALTER ROLE [db_owner] ADD MEMBER myadmin
[1] https://learn.microsoft.com/zh-cn/azure/azure-sql/database/logins-create-manage
[2] https://learn.microsoft.com/zh-cn/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-ver15#special-roles-for--and-azure-synapse