Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (2)

Resolved Issue with sys.database_principals

  1. Issue
0
Votes
Undo
  1. Lynn A Haynes
    Lynn A Haynes
  2. PowerBuilder
  3. Thursday, 23 January 2020 17:56 PM UTC

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?

 

Multiline if statement
PowerBuilder 2017 R3 SaveAs PDF not retaining UTF-...
Responses (2)
Michael Kramer
Michael Kramer Accepted Answer Pending Moderation
  1. Thursday, 23 January 2020 18:22 PM UTC
  2. PowerBuilder
  3. # 1

Two questions come to mind:

  • What happens in PB IDE's Database Painter if you run that query using Interactive SQL?
  • Are you running the query in SSMS using same credentials = same access level as user connected through your PB app?

 

Comment
Roland Smith
  1. Roland Smith
  2. Thursday, 23 January 2020 19:24 PM UTC
I think it is likely your second point, differing security levels.
  1. Helpful
There are no comments made yet.
Lynn A Haynes
Lynn A Haynes Accepted Answer Pending Moderation
  1. Thursday, 23 January 2020 19:08 PM UTC
  2. PowerBuilder
  3. # 2

Hi Michael,

I had the same thought, to try the DB painter. It returns rows there, too. I am assuming that specifying INTEGRATEDSECURITY=SSPI in SQLCA.DBParm is the same as using SSMS with Windows authentication, so the answer to your second question would be yes.

Comment
Michael Kramer
  1. Michael Kramer
  2. Thursday, 23 January 2020 19:33 PM UTC
Try this one - sometimes helped me figure out what was going all.

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"
  1. Helpful
Michael Kramer
  1. Michael Kramer
  2. Friday, 24 January 2020 12:47 PM UTC
RE: ... INTEGRATEDSECURITY=SSPI ....

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.
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.