1

We have a database, lets name it DB1. In that DB1 we have "Views" like mee.v_ZBO_RCP_HEADERS. In SSMS I click get 1000 rows, code:

SELECT TOP (1000) [WERKS]
      ,[RECEIPT_ID]
      ,[POS_ID]
      ,[RECEIPT_NUMBER]
      ,[RCP_DATE]
      ,[RCP_TIME]
      ,[CASHIER_ID]
      ,[TOTAL_TAXES]
      ,[TOTAL_AMOUNT_INC]
      ,[Z_ID]
      ,[CCARD_ID]
      ,[STOR_ID]
      ,[lcn_id]
      ,[RCP_psls_date_id]
      ,[DURATION]
      ,[DURATION_FROM_LA]
      ,[RECEIPT_ROWS_DM]
      ,[FLAGS]
  FROM [DB1].[mee].[v_ZBO_RCP_HEADERS]

But using SELECT I have error:

The server principal "computer name" is not able to access the database "Receipts_2016" under the current security context.

What I can't understand is that this Receipts_2016 is another database where I do not have SELECT permissions I know but what has that got to do with the ZBO_RCP_HEADERS one?

Under this view I can't find a single line connected to that reciepts database.

What can I do to change it so this database view wouldn't need to connect to the other database since 2016 is outdated anyway?

Dale K
  • 25,246
  • 15
  • 42
  • 71
user245255
  • 175
  • 3
  • 14
  • 4
    please show us the view `v_ZBO_RCP_HEADERS` – Squirrel Jan 22 '20 at 08:29
  • Aaaand there is the solution, in that first comment :) – VBoka Jan 22 '20 at 08:39
  • Maybe dumb question but how do I show it? I can not see the info inside it since it gives me error that I can't SELECT anything since I don't have access to Receipts_2016 DB. View looks to me very empty. https://ibb.co/r7dzvC2 – user245255 Jan 22 '20 at 08:58
  • 1
    Try this: https://stackoverflow.com/questions/4765323/is-there-a-way-to-retrieve-the-view-definition-from-a-sql-server-using-plain-ado and if not possible then maybe you do not have permision to view the data or the definition of the view – VBoka Jan 22 '20 at 09:26
  • Right-click the view in SSMS and select "script view as..." - this should give you the code... – Tyron78 Jan 22 '20 at 09:38
  • In the code you posted, view `v_ZBO_RCP_HEADERS` is in the `DB1` database. – Nick.Mc Jan 22 '20 at 10:06
  • Yea seems I don't have permissions since I get "NULL" for answer using: `select definition from sys.objects o join sys.sql_modules m on m.object_id = o.object_id where o.object_id = object_id( 'mee.v_ZBO_RCP_HEADERS') and o.type = 'V'` I'll look into that, thanks. Guess I can figure this out on my own after I can see what's going on in there. – user245255 Jan 22 '20 at 10:21
  • Bad habits to NOT develop - using 3 part names without reason. If your view is created in database DB1, then you (or more likely someone else) will curse your name at some later date because the query in your view references objects within the same database using the database name (3 part name) without any reason. Now when someone needs to move the database or create a duplicate for some reason (UAT, phase 2 develop, move to production, etc.) the code must be modified to update the database name. – SMor Jan 22 '20 at 13:17
  • Did you _Right-click the view in SSMS and select "script view as..."_ as suggested? – Nick.Mc Jan 22 '20 at 23:32

0 Answers0