SQL Server：如何根据登录名和数据库查询关联角色？

Got it, let's build a script that meets your exact requirement. Since you mentioned roles like db_owner, I'll assume you're working with SQL Server—here's a PowerShell script that takes a login name and database name as input parameters, then outputs the associated database role(s):

PowerShell Script (Get-DbRole.ps1) #

param(
    [Parameter(Mandatory=$true)]
    [string]$LoginName,
    [Parameter(Mandatory=$true)]
    [string]$DatabaseName
)

# Query to map server login to database user and retrieve assigned roles
$sqlQuery = @"
SELECT dp.name AS DatabaseRole
FROM [$DatabaseName].sys.database_role_members drm
JOIN [$DatabaseName].sys.database_principals dp ON drm.role_principal_id = dp.principal_id
JOIN [$DatabaseName].sys.database_principals du ON drm.member_principal_id = du.principal_id
JOIN sys.server_principals sp ON du.sid = sp.sid
WHERE sp.name = '$LoginName'
"@

# Execute the query (adjust server name if needed; uses local SQL instance by default)
Invoke-SqlCmd -Query $sqlQuery -ServerInstance "."

How to Use It #

1. Save the code above as Get-DbRole.ps1
2. Run it from PowerShell with your target login and database:

   .\Get-DbRole.ps1 -LoginName "ADMA1" -DatabaseName "fogbugzRelease"
   

3. You'll get output like:

   DatabaseRole
   ------------
   db_owner
   

Notes #

• If your SQL Server instance isn't local, replace "." in the Invoke-SqlCmd line with your server name (e.g., "SQLSERVER01" or "SQLSERVER01\INSTANCE01").
• The script returns all roles the login is assigned to in the target database (not just one—if the login has multiple roles, they'll all show up).

Alternative: T-SQL Script (for Direct Execution in SSMS) #

If you prefer a pure SQL solution, you can use this script by setting the variables manually or wrapping it in a stored procedure:

DECLARE @LoginName NVARCHAR(128) = 'ADMA1';
DECLARE @DatabaseName NVARCHAR(128) = 'fogbugzRelease';

DECLARE @Sql NVARCHAR(MAX) = 
'
SELECT dp.name AS DatabaseRole
FROM [' + @DatabaseName + '].sys.database_role_members drm
JOIN [' + @DatabaseName + '].sys.database_principals dp ON drm.role_principal_id = dp.principal_id
JOIN [' + @DatabaseName + '].sys.database_principals du ON drm.member_principal_id = du.principal_id
JOIN sys.server_principals sp ON du.sid = sp.sid
WHERE sp.name = ''' + @LoginName + '''
';

EXEC sp_executesql @Sql;

内容的提问来源于stack exchange，提问作者user8874956