检查数据库中是否存在角色？

发布于 2024-08-19 23:48:12 字数 123 浏览 6 评论 0原文

我想将用户添加到多个数据库中的同一角色。然而，该角色可能存在于每个数据库中，也可能不存在。如何检查每个数据库中是否存在该角色，如果存在，请将用户添加到该角色？

例如如果角色存在开始在角色中添加用户结尾

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

帅哥哥的热头脑 2024-08-26 23:48:12

尝试：

IF DATABASE_PRINCIPAL_ID('role') IS NULL
BEGIN
  -- add user here
  CREATE ROLE role AUTHORIZATION MyUser;
END

try:

IF DATABASE_PRINCIPAL_ID('role') IS NULL
BEGIN
  -- add user here
  CREATE ROLE role AUTHORIZATION MyUser;
END

回复收藏 0 原文

時窥 2024-08-26 23:48:12

IF EXISTS 
(
  SELECT 1
    FROM sys.database_principals
    WHERE type_desc = 'DATABASE_ROLE'
    AND name = 'name'
)
BEGIN
  -- add user;
END

IF EXISTS 
(
  SELECT 1
    FROM sys.database_principals
    WHERE type_desc = 'DATABASE_ROLE'
    AND name = 'name'
)
BEGIN
  -- add user;
END

回复收藏 0 原文

此生挚爱伱 2024-08-26 23:48:12

我还不能发表评论，所以我必须将其添加为答案。

我喜欢尽可能具体，这就是为什么我更喜欢包括主要类型。这就是为什么我对亚伦的答案投了赞成票。

如果存在与要添加的角色同名的另一个主体类型，则使用 DATABASE_PRINCIPAL_ID 可能会产生意外结果。这是因为 DATABASE_PRINCIPAL_ID 返回当前数据库中主体的 ID 号，而不是专门针对具有某种数据库角色类型的主体。

假设您有一个与数据库角色同名的用户。 Yada 脚本的状态将指示成功，但它不会添加该角色，因为已经有一个具有该名称的主体。但是，Aaron 的脚本会返回以下错误：

当前数据库中已存在用户、组或角色“名称”。

我宁愿尽早发现这个问题（例如，当脚本运行时），而不是稍后（例如，当我的应用程序正在使用时）。

这是我通常使用的：

IF NOT EXISTS(SELECT NULL FROM sys.database_principals WHERE [name] = 'role_name' AND [type]='R')
BEGIN
    -- add user;
END

如果我真的想处理这种情况并且不显示错误，我可以使用如下内容：

DECLARE @RoleName sysname,
        @PrincipalType NVARCHAR(60);

SET @RoleName = 'role_name';
SET @PrincipalType = (SELECT type_desc FROM sys.database_principals WHERE [name] = @RoleName);

IF @PrincipalType IS NULL
BEGIN
    -- Add user;
END
ELSE IF @PrincipalType <> 'DATABASE_ROLE'
BEGIN
    --Deal with the issue as desired. Here we're printing out a warning. Important: The status will still indicate that the Query executed successfully when using PRINT to show warnings.
    PRINT 'WARNING: The ' + @RoleName + ' database role was not created. A principal already exists in the database with a type of ' + @PrincipalType + '.';
END

I can't comment yet, so I have to add this as an answer.

I like to be as specific as possible, which is why I prefer including the principal type. This is why I up voted Aaron's answer.

Using DATABASE_PRINCIPAL_ID could give unexpected results if there is another principal type with the same name as the role that you want to add. This is because DATABASE_PRINCIPAL_ID returns the ID number of a principal in the current database, not specifically for a principal with a type of database role.

Let's say that you have a user with the same name as the database role. The status of Yada's script would indicate success, but it would not add the role because there is already a principal with that name. However, Aaron's script would return the following error:

User, group, or role 'name' already exists in the current database.

I'd rather catch this issue early on (e.g. when the script is run) than later on (e.g. when my app is being used).

Here's what I'd normally use:

IF NOT EXISTS(SELECT NULL FROM sys.database_principals WHERE [name] = 'role_name' AND [type]='R')
BEGIN
    -- add user;
END

If I really want to handle this scenario and not show an error, I could use something like this:

DECLARE @RoleName sysname,
        @PrincipalType NVARCHAR(60);

SET @RoleName = 'role_name';
SET @PrincipalType = (SELECT type_desc FROM sys.database_principals WHERE [name] = @RoleName);

IF @PrincipalType IS NULL
BEGIN
    -- Add user;
END
ELSE IF @PrincipalType <> 'DATABASE_ROLE'
BEGIN
    --Deal with the issue as desired. Here we're printing out a warning. Important: The status will still indicate that the Query executed successfully when using PRINT to show warnings.
    PRINT 'WARNING: The ' + @RoleName + ' database role was not created. A principal already exists in the database with a type of ' + @PrincipalType + '.';
END

回复收藏 0 原文

~没有更多了~