检查SQL Server登录是否已存在

Question

我需要检查SQL Server上是否已经存在特定的登录名，如果不存在，则需要将其添加。

我找到了以下代码来实际将登录名添加到数据库中，但是我想要将其包装在IF语句中（以某种方式）以先检查登录名是否已存在。

CREATE LOGIN [myUsername] WITH PASSWORD=N'myPassword', 
DEFAULT_LANGUAGE=[us_english], 
CHECK_EXPIRATION=OFF, 
CHECK_POLICY=OFF 
GO

我了解需要查询系统数据库，但不确定从何处开始！

- Brett Rigby

---

这是一个重要的问题，但是翻译过来似乎忽略了一个重要的区别：用户与登录。Jon链接到的潜在重复问题似乎是关于用户的。这个问题在标题中提到了“用户”，但在问题代码和被接受的答案中处理的是登录。我相应地编辑了标题和问题。 - LarsH

仅补充一下@LarsH的评论，登录与SQL服务器实例相关联，而用户与特定数据库相关联。数据库用户可以从服务器登录创建，因此他们可以访问特定数据库。请参阅这篇优秀的文章，以及它所属的整个系列（Stariway to SQL Server Security）。 - Reversed Engineer

Answer 1

以下是在SQL Server 2005及更高版本中执行此操作的方法，而无需使用已弃用的syslogins视图：

IF NOT EXISTS 
    (SELECT name  
     FROM master.sys.server_principals
     WHERE name = 'LoginName')
BEGIN
    CREATE LOGIN [LoginName] WITH PASSWORD = N'password'
END

使用 server_principals 视图而不是 sql_logins，因为后者不会列出 Windows 登录。

如果您需要在创建用户之前检查特定数据库中是否存在该用户，则可以执行以下操作：

USE your_db_name

IF NOT EXISTS
    (SELECT name
     FROM sys.database_principals
     WHERE name = 'Bob')
BEGIN
    CREATE USER [Bob] FOR LOGIN [Bob] 
END

- Derek Morrison

---

最佳答案不涉及动态 SQL，也不使用任何已弃用的视图。谢谢！ - Casper Leon Nielsen

对于SQL Azure，两个目标表是sys.sql_logins和sys.sysusers -- 在回答中提到这一点可能很好。 - Brett

如果您的脚本需要使用变量用户名，则此内容无用。 - Ross Presser

@Derek Morrison，我们能否为SID添加一个条件？ - AstroBoy

Answer 2

这里来自此处

If not Exists (select loginname from master.dbo.syslogins 
    where name = @loginName and dbname = 'PUBS')
Begin
    Select @SqlStatement = 'CREATE LOGIN ' + QUOTENAME(@loginName) + ' 
    FROM WINDOWS WITH DEFAULT_DATABASE=[PUBS], DEFAULT_LANGUAGE=[us_english]')

    EXEC sp_executesql @SqlStatement
End

- Johnno Nolan

---

为防止 SQL 注入攻击，请使用 QUOTENAME 函数。攻击者可以传递一个类似于 x] with password ''y'';\r\ndrop table foo;\r\n 的 @loginName 参数。 - Remus Rusanu

为什么需要将语句创建为字符串，然后使用sp_executesql，而不是直接输入CREATE LOGIN [@loginName] FROM ...？请原谅我的无知，我想学习... - LarsH

需要将语句创建为字符串是必需的，因为CREATE LOGIN不能使用登录名的参数，它需要一个字符串字面值。不确定原因是什么，但我通过困难的方式发现这是真的。 - Joseph Bongaarts

@JosephBongaarts：好的，谢谢。我猜这就像SELECT语句中的表名一样。也许这个想法是为了减少易受攻击的表面积，但我不知道它是否有帮助。 - LarsH

我认为QUOTENAME()应该放在@loginName周围，而不是整个语句，这样你就可以摆脱手动添加方括号 [ ] 来限定 @loginName 的问题了。 - brianary

syslogins 视图已被弃用。 - Henrik Høyer

Answer 3

作为对这个主题的小补充，在一般情况下，你应该避免使用以sys.sys*开头的视图，因为微软只包括它们以保持向后兼容性。对于你的代码，你应该使用sys.server_principals。这是基于你使用的SQL 2005或更高版本。

- Bomlin

---

测试过，运行良好，而且比其他答案更更新。同样给你点赞。 - David

是的，自2005年起，微软取消了对系统表的直接访问权限。为了避免破坏旧代码，他们包含了与旧表同名的视图。然而，它们只适用于旧代码，新代码应该使用新视图。在BOL中，搜索“映射系统表”以找出您应该使用什么。 - Bomlin

Answer 4

你可以使用内置函数：

SUSER_ID ( [ 'myUsername' ] )

通过

IF [value] IS NULL [statement]

像：

IF SUSER_ID (N'myUsername') IS NULL
CREATE LOGIN [myUsername] WITH PASSWORD=N'myPassword', 
DEFAULT_LANGUAGE=[us_english], 
CHECK_EXPIRATION=OFF, 
CHECK_POLICY=OFF 
GO

https://technet.microsoft.com/en-us/library/ms176042(v=sql.110).aspx

- Hüda

---

这个解决方案非常好，因为它不需要 ALTER ANY LOGIN 权限，而其他所有解决方案都需要。在我的情况下，无法授予我们的工具查看数据库中所有登录的权限，但是仍然可以使用 SUSER_ID('loginName') 来测试登录是否已存在。谢谢！ - Steve Pick

Answer 5

为了处理登录名、角色、用户等之间的命名冲突，您应该根据Microsoft sys.database_principals 文档检查 type 列。

为了处理用户名中的特殊字符等，相应地使用 N'<name>' 和 [<name>]。

创建登录

USE MASTER
IF NOT EXISTS (SELECT 1 FROM master.sys.server_principals WHERE 
[name] = N'<loginname>' and [type] IN ('C','E', 'G', 'K', 'S', 'U'))
    CREATE LOGIN [<loginname>] <further parameters>

创建数据库用户

USE [<databasename>]
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE 
[name] = N'<username>' and [type] IN ('C','E', 'G', 'K', 'S', 'U'))
    CREATE USER [<username>] FOR LOGIN [<loginname>]

创建数据库角色

USE [<databasename>]
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE 
[name] = N'<rolename>' and Type = 'R')
    CREATE ROLE [<rolename>]

添加用户到角色

USE [<databasename>]
EXEC sp_addrolemember N'<rolename>', N'<username>'

授予角色权限

USE [<databasename>]
GRANT SELECT ON [<tablename>] TO [<rolename>]
GRANT UPDATE ON [<tablename>] ([<columnname>]) TO [<rolename>]
GRANT EXECUTE ON [<procedurename>] TO [<rolename>]

该SQL已经在SQL Server 2005、2008、2008 R2、2014、2016、2017和2019上进行了测试。

- Henrik Høyer

Answer 6

尝试以下操作（将“user”替换为实际的登录名）：

IF NOT EXISTS(
SELECT name 
FROM [master].[sys].[syslogins]
WHERE NAME = 'user')

BEGIN 
    --create login here
END

- Marc

---

@Marc：抱歉，但是你错了。表[syslogins]保存登录信息，而表[sysusers]则保存用户信息。 - abatishchev

Answer 7

这是针对Azure SQL的：

IF (EXISTS(SELECT TOP 1 1 FROM sys.sql_logins WHERE [name] = '<login>'))
    DROP LOGIN [<login>];

来源：如何检查Azure SQL数据库中是否已存在数据库用户

要检查Azure SQL数据库中是否已经存在一个特定的数据库用户，可以使用以下查询： SELECT COUNT(*) FROM sys.database_principals WHERE name = 'username'; 如果返回的计数为1，则表示该用户已存在，否则该用户不存在。

- Vinicius

---

似乎也适用于SQL SERVER 2019，可能往后版本也是。 - msgrockclimber

Answer 8

这适用于SQL Server 2000。

use master
select count(*) From sysxlogins WHERE NAME = 'myUsername'

在SQL 2005上，将第二行更改为：

select count(*) From syslogins WHERE NAME = 'myUsername'

我不确定 SQL 2008，但我猜它和 SQL 2005 相同。如果不是，这应该能帮你知道从哪里开始查找。

- David

Answer 9

你想要检查的是登录还是用户？登录是在服务器级别创建的，用户是在数据库级别创建的，因此登录在服务器中是唯一的。

同时，用户是针对登录创建的，没有登录的用户是孤立的用户，无法进行SQL Server登录。

也许您需要检查登录。

select 'X' from master.dbo.syslogins where loginname=<username>

以上查询如果登录存在则返回“X”，否则返回null。

然后创建一个登录。

CREATE LOGIN <username> with PASSWORD=<password>

这会在SQL Server中创建一个登录账户，但它只接受强密码。

为每个你想要登录的数据库创建一个用户账户。

CREATE USER <username> for login <username>

给用户分配执行权限

 GRANT EXECUTE TO <username>

您必须拥有系统管理员权限或称为"sa"

您可以在数据库上编写SQL存储过程来实现该功能

create proc createuser
(
@username varchar(50),
@password varchar(50)
)
as
begin
if not exists(select 'X' from master.dbo.syslogins where loginname=@username)
begin
 if not exists(select 'X' from sysusers where name=@username)
 begin
exec('CREATE LOGIN '+@username+' WITH PASSWORD='''+@password+'''')
exec('CREATE USER '+@username+' FOR LOGIN '+@username)
exec('GRANT EXECUTE TO '+@username)
end
end
end

- Akshita

Answer 10

开始学习SQL 2016：

DROP USER IF EXISTS [userName]

CREATE USER [userName] FOR LOGIN [loginName]

- Albert Zakhia

---

请注意，尽管此答案可能与此问题相关，但该问题确实指定了登录而不是用户。 - Chris Nevill