主页 / dba / 问题 / 125886
Accepted
VansFannel
Asked: 2016-01-12 05:46:11 +0800 CST

检查用户是否存在于 SQL Server 数据库中

  • 772

我正在使用 SQL Server 2012。我想在将用户添加到数据库之前检查它是否存在。

这是我测试过的：

USE [MyDatabase]
GO

IF NOT EXISTS (SELECT name 
                FROM [sys].[server_principals]
                WHERE name = N'IIS APPPOOL\MyWebApi AppPool')
Begin
    CREATE USER [IIS APPPOOL\MyWebApi AppPool] 
    FOR LOGIN [IIS APPPOOL\MyWebApi AppPool] WITH DEFAULT_SCHEMA=[dbo]
end
ALTER ROLE [db_owner] ADD MEMBER [IIS APPPOOL\MyWebApi AppPool]
GO

但是，SELECT name FROM [sys].[server_principals]如果该用户存在于MyDatabase.

如何检查用户是否存在MyDatabase？

sql-server sql-server-2012

4 个回答

  1. Best Answer

    使用sys.database_principals而不是sys.server_principals.

    所以最终的查询看起来像这样（考虑用户过滤器）：

    USE [MyDatabase]
GO

IF NOT EXISTS (SELECT [name]
                FROM [sys].[database_principals]
                WHERE [type] = N'S' AND [name] = N'IIS APPPOOL\MyWebApi AppPool')
Begin
    CREATE USER [IIS APPPOOL\MyWebApi AppPool] 
    FOR LOGIN [IIS APPPOOL\MyWebApi AppPool] WITH DEFAULT_SCHEMA=[dbo]
end
ALTER ROLE [db_owner] ADD MEMBER [IIS APPPOOL\MyWebApi AppPool]
GO
    • 42

  2. 我将 SUSER_ID() 和 USER_ID() 用于此类事情：

    -- Check SQL Server Login
IF SUSER_ID('SomeLogin') IS NULL
    CREATE LOGIN SomeLogin WITH PASSWORD = 'SomePassword';

-- Check database user
IF USER_ID('SomeUser') IS NULL
    CREATE USER SomeUser FOR LOGIN SomeLogin;
    • 26

  3. 进一步细化，因为这将使读取更佳

    USE [MyDatabase]
GO

IF DATABASE_PRINCIPAL_ID('IIS APPPOOL\MyWebApi AppPool') IS NULL
BEGIN
    CREATE USER [IIS APPPOOL\MyWebApi AppPool] 
    FOR LOGIN [IIS APPPOOL\MyWebApi AppPool] WITH DEFAULT_SCHEMA=[dbo]
END
ALTER ROLE [db_owner] ADD MEMBER [IIS APPPOOL\MyWebApi AppPool]
GO
    • 18

  4. 如果您使用的是电阻式服务器，您可以一次检查多个服务器并返回真/假： 

    SELECT @@servername,    
    CASE 
        WHEN EXISTS(SELECT name FROM sys.database_principals WHERE name = 'LoginName') THEN 1 
        ELSE 0 
    END AS YesNo
    • 2

相关问题