We recently migrated our database server OS from Windows 2008 R2 to Windows 2012 R2. Note that we're still running SQL Server 2008 R2, but the 64-bit SQL Server 2008 R2 was installed, whereas we were running 32-bit SQL Server 2008 R2 in the old environment. Also, database connection uses IntegratedSecurity=SSPI.
Our PB application queries the sys.database_principals view...
SELECT sys.database_principals.principal_id AS uid, sys.database_principals.name
FROM sys.database_principals
WHERE ( sys.database_principals.type IN ('S','U') ) AND ( sys.database_principals.sid IS NOT NULL ) AND ( sys.database_principals.sid <> 0) AND ( sys.database_principals.name not in (select username from securityGroupUsers) )
ORDER BY sys.database_principals.name ASC
The query returns no rows, even though running it using SSMS with Windows authentication returns the appropriate rows. No error is raised either.
Any ideas on why the query would return no rows from the app?
SELECT ORIGINAL_LOGIN( ), USER_NAME, DB_NAME( ), @@SERVERNAME
When that failed to help, I would check role memberships also to understand if certain role wasn't "active"
I have AdventureWorks upgraded and running in local MSSQL 2019 instance so I have a active playing ground. ==> What DBMS are you using?
I have connection setup using SNC where DBParm settings are named slightly different -- or we are configuring differently for single sign-on using O/S credentials via AD.