Create Views issue

Question

Post reply

Create Views issue

psred

SSCrazy

Points: 2049
More actions
September 16, 2013 at 9:25 am

#277303

The user account has dbo permissions on the database but couldnt create the views not sure what is the issue. Need help.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 1

You should post some DDL code and the error.

If you can script the user, do that and show that as well. Hard to know what might be wrong from your post.

psred SSCrazy Points: 2049 More actions · Answer 2

CREATE VIEW permission denied in database 'medical'

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 3

Again, it seems like you don't really want help. Is this an error message, is this the command you ran?

Stop, imagine we have no idea of your environment. list the code you ran, format it as code and tell us this is the code I ran. Then give us the error message, set apart and noted as such.

If you don't put some effort into explaining what you did, we can't, and don't want, to help.

psred SSCrazy Points: 2049 More actions · Answer 4

Create View as

SELECT dbo.CLBALANCN.COMPANY, dbo.CLBALANCN.ACCOUNT, dbo.CLBALANCN.INCIDENTNO, dbo.CLINS.PLANTYPE AS insurance1FinancialClass,

CLINS_1.PLANTYPE AS insurance2FinancialClass, '' AS insurance3FinancialClass, CASE WHEN CLINS.PLANTYPE IS NOT NULL

THEN CLINS.PLANTYPE ELSE CLINS_1.PLANTYPE END AS currentFinancialClass, SUM(COALESCE (dbo.CLBALANCN.YTDCHARGES, 0))

AS totalCharges, SUM(COALESCE (dbo.CLBALANCN.BALEND, 0)) AS accountBalance, SUM(COALESCE (dbo.CLBALANCN.YTDPAYINS, 0))

+ SUM(COALESCE (dbo.CLBALANCN.YTDPAYINS2, 0)) + SUM(COALESCE (dbo.CLBALANCN.YTDPAYINS3, 0))

+ SUM(COALESCE (dbo.CLBALANCN.YTDPAYGUAR, 0)) AS totalPayments, SUM(COALESCE (dbo.CLBALANCN.YTDCHARGES, 0))

- SUM(COALESCE (dbo.CLBALANCN.YTDALLOWED, 0)) AS initialPatientResponsibility, SUM(COALESCE (dbo.CLBALANCN.GUARDUE, 0))

AS outstandingPatientBalance, SUM(COALESCE (dbo.CLBALANCN.YTDPAYINS, 0)) + SUM(COALESCE (dbo.CLBALANCN.YTDPAYINS2, 0))

+ SUM(COALESCE (dbo.CLBALANCN.YTDPAYINS3, 0)) AS insurancePayments, SUM(COALESCE (dbo.CLBALANCN.YTDPAYINS, 0))

AS insurance1Payments, SUM(COALESCE (dbo.CLBALANCN.YTDPAYINS2, 0)) AS insurance2Payments,

SUM(COALESCE (dbo.CLBALANCN.YTDPAYINS3, 0)) AS insurance3Payments, SUM(COALESCE (dbo.CLBALANCN.INS1DUE, 0))

+ SUM(COALESCE (dbo.CLBALANCN.INS2DUE, 0)) AS insuranceOutstandingBalance, SUM(COALESCE (dbo.CLBALANCN.INS1DUE, 0))

AS insurance1OutstandingBalance, SUM(COALESCE (dbo.CLBALANCN.INS2DUE, 0)) AS insurance2OutstandingBalance,

'0.0000' AS insurance3OutstandingBalance, SUM(COALESCE (dbo.CLBALANCN.YTDPAYGUAR, 0)) AS patientPayments,

CASE WHEN CLINS.PLANTYPE IS NOT NULL THEN CLINS.NAME ELSE CLINS_1.NAME END AS payer

FROM dbo.CLBALANCN INNER JOIN

dbo.CLINS ON dbo.CLBALANCN.COMPANY = dbo.CLINS.COMPANY AND dbo.CLBALANCN.INS1CODE = dbo.CLINS.CODE INNER JOIN

dbo.CLINS AS CLINS_1 ON dbo.CLBALANCN.COMPANY = CLINS_1.COMPANY AND dbo.CLBALANCN.INS2CODE = CLINS_1.CODE

GROUP BY dbo.CLBALANCN.ACCOUNT, dbo.CLBALANCN.INCIDENTNO, dbo.CLBALANCN.COMPANY, dbo.CLINS.PLANTYPE, CLINS_1.PLANTYPE,

CASE WHEN CLINS.PLANTYPE IS NOT NULL THEN CLINS.NAME ELSE CLINS_1.NAME END

HAVING (dbo.CLBALANCN.COMPANY = 'Main')

The user has database owner permissions. So when the user run above stmt on the database he is getting popup saying 'CREATE VIEW permission denied in database 'medical'

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 5

The user then has deny permission on the CREATE VIEW permission.

Script out the user and look at their permissions.