如何检查 USER 是否已在数据库中创建或未在 SQL 中创建？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6722540/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to check if the USER is already created in the database or not in SQL?
提问by Q8Y
Is there is a way that from it I can know if the user(notthe login) is already created in the database? I mean the user not the login, since, I know how to check for the login. I need to check for the user that is created inside a specific DB & a role assigned to it.
有没有办法从中我可以知道用户（不是登录名）是否已经在数据库中创建？我的意思是用户不是登录名，因为我知道如何检查登录名。我需要检查在特定数据库中创建的用户和分配给它的角色。
This is the code for checking for the login:
这是检查登录的代码：
SELECT name FROM sys.server_principals WHERE name = 'test_user'
but how about the user? Since I need to create the user and assign a role to it if its not created. Otherwise, I will continue without creating.
但是用户呢？因为我需要创建用户并为其分配角色（如果未创建）。否则，我将继续不创作。
Thanks
谢谢
回答by marc_s
How about:
怎么样：
USE (your database you want to check the user's existence in)
SELECT *
FROM sys.database_principals
WHERE name = '(your user name to check here)'
sys.server_principalsshows you the logins defined on the server level - sys.database_principalsshows you the principals (e.g. user accounts) on a database level.
sys.server_principals向您显示在服务器级别定义的登录名 -sys.database_principals向您显示数据库级别的主体（例如用户帐户）。
回答by gbn
This will tell you the matching user name for a given login name
这将告诉您给定登录名的匹配用户名
USE MyDB
SELECT
sp.name AS ServerLoginName,
dp.name AS DBUserName
FROM
sys.server_principals sp
LEFT JOIN
sys.database_principals dp ON sp.sid = dp.sid
WHERE
sp.name = 'MyLogin'
回答by YearsLater
use SomeDatabase
go
/* built-in system function */
select database_principal_id('UserNameHere')
/* check if exists and drop */
if database_principal_id('UserNameHere') is not null
drop user 'UserNameHere'
go
回答by Damien_The_Unbeliever
If you've found sys.server_principals, I'm surprised you haven't found sys.database_principals. You can join the views based on the sidcolumn.
如果你找到了sys.server_principals，我很惊讶你没有找到sys.database_principals。您可以根据sid列加入视图。
回答by Prisoner ZERO
You might care for this method as well...
您可能也喜欢这种方法......
IF DATABASE_PRINCIPAL_ID('domain\lanid') IS NULL
BEGIN
CREATE USER [domain\lanid] FOR LOGIN [domain\lanid] WITH DEFAULT_SCHEMA=[dbo]
EXEC sp_addrolemember N'db_ApplicationUserRole', N'domain\lanid'
END
