SQL Server 用户名函数

发布于 2024-08-08 14:25:17 字数 959 浏览 1 评论 0原文

考虑这个 T-SQL：

CREATE USER my_test_user WITHOUT LOGIN;
SELECT USER_NAME(), USER, SUSER_SNAME(),SYSTEM_USER, suser_name();
EXECUTE AS USER = 'my_test_user' WITH NO REVERT;
SELECT USER_NAME(), USER, SUSER_SNAME(),SYSTEM_USER, suser_name();

我正在考虑使用这些帐户WITHOUT LOGIN 进行审计。基本上，我的“获取数据库连接”代码返回一个已执行 EXECUTE AS USER...WITH NO REVERT 的连接。

问题是我无法从这些用户名函数中获得一致的结果。两行输出是：

dbo           dbo           original_user      original_user     original_user
my_test_user  my_test_user  S-1-9-3-XXXXX..    S-1-9-3-XXXXX..   S-1-9-3-XXXXX..

USER 函数在 'EXECUTE AS' 之后生成正确的输出，但在此之前它们显示 dbo 而不是用户名 SUSER 函数正好相反 - 它们最初是正确的，但在模拟之后，它们显示某种 ID

SUSER_SNAME 明确给出了一个应该起作用的示例。

更新：我正在寻找的是一个函数，它将在第一种情况下生成“original_user”，在第二种情况下生成“my_test_user”。

原文

Consider this T-SQL:

CREATE USER my_test_user WITHOUT LOGIN;
SELECT USER_NAME(), USER, SUSER_SNAME(),SYSTEM_USER, suser_name();
EXECUTE AS USER = 'my_test_user' WITH NO REVERT;
SELECT USER_NAME(), USER, SUSER_SNAME(),SYSTEM_USER, suser_name();

I'm looking at using these accounts WITHOUT LOGIN for auditing purposes. Basically, my "get a database connection" code returns a connection on which the EXECUTE AS USER...WITH NO REVERT has already been executed.

The problem is that I can't get consistent results from any of these user name functions. The two lines of output are:

dbo           dbo           original_user      original_user     original_user
my_test_user  my_test_user  S-1-9-3-XXXXX..    S-1-9-3-XXXXX..   S-1-9-3-XXXXX..

The USER functions produce correct output AFTER the 'EXECUTE AS', but beforehand they're showing dbo rather than the user name
The SUSER functions are just the opposite -- they're correct initially but after impersonation they're showing some sort of ID

The MSDN docs for SUSER_SNAME explicitly give an example where this is supposed to work.

UPDATE: What I'm looking for is a function that will produce 'original_user' in the first case and 'my_test_user' in the second.

分享到QQ

分享到微博

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

发布评论

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

紫﹏色ふ单纯 2024-08-15 14:25:17

更新：您还需要此处的 ORIGINAL_LOGIN fn

原文：

之后没有匹配的系统级用户。因此，它无法解析数据库级别 sid，因此它只是从 sys.database_principals

CREATE USER my_test_user WITHOUT LOGIN;
SELECT USER_NAME(), USER, SUSER_SNAME(),SYSTEM_USER, suser_name();
EXECUTE AS USER = 'my_test_user' WITH NO REVERT;
SELECT USER_NAME(), USER, SUSER_SNAME(),SYSTEM_USER, suser_name(),
                        SUSER_SID();

SELECT * FROM sys.database_principals WHERE sid = SUSER_SID();

我不知道这是否是设计使然，但它解释了该数字的来源。其余内容如预期的那样，如下所述。

注意：

您将获得 USER_NAME() 的 dbo，因为您使用系统管理员权限登录。使用数据库级别用户函数时，每个拥有“sysadmin”的人都是 dbo。
更改用户上下文后，数据库级用户函数解析为数据库用户上下文
对于系统级用户函数，您将得到您之前使用过的登录名

Update: you need the ORIGINAL_LOGIN fn here too

Original:

Afterwards, there is no matching system level user. So, it can't resolve the database level sid, so it simply returns the sid from sys.database_principals

CREATE USER my_test_user WITHOUT LOGIN;
SELECT USER_NAME(), USER, SUSER_SNAME(),SYSTEM_USER, suser_name();
EXECUTE AS USER = 'my_test_user' WITH NO REVERT;
SELECT USER_NAME(), USER, SUSER_SNAME(),SYSTEM_USER, suser_name(),
                        SUSER_SID();

SELECT * FROM sys.database_principals WHERE sid = SUSER_SID();

I don't know if this is by design, but it explains where the number comes from. The rest is as expected as explained below

Notes:

You'll get dbo for USER_NAME() because you are logged on with sysadmin rights. Everyone with "sysadmin" is dbo when using db level user functions.
After changing user context, db level user functions resolve to the database user context
For system level user functions, you'll get the login you used before

回复收藏 0 原文