Thank you both, Perry and Phil for the help.

My ALTER syntax was incorrect GRANT ALTER DATABASE TO mymanageduseridentity

Perry gave the correct syntax GRANT alter on DATABASE::[somedb] TO myuserbob

And the correct command worked.  Many thanks!

It is just Azure SQL, not a SQL managed instance so it is not close to mssql on-prem server at all.  Thanks!

Scott,

Thank you for your reply.  We plan to use ADF CI/CD so will definitely investigate further for implementation.

Scott,

I ran

select HAS_DBACCESS('product')

select IS_MEMBER('db_owner')

and found I am a member of db_owner.

Thank you very much for the help.

Now I have to find someone not sysadmin, not db_owner to test the DENY...

We will try that approach.  Thanks for the advice!

I agree with you totally, Views is the way to go.  What we are trying to avoid is having to grant permissions at each database object which is tedious therefore...

I totally agree with you.   Please continue with your teachings about data types best practices.

Most of those settings are in binary state (1/0, true/false, Yes/No) and where they can be...

Point taken.  On the real life table, we do have an index  on ( ClinicID, ItemID ).  I will probably drop the clustered PK and make ( ClinicID, ItemID )...

Thank you, Jeff.  Another good use for CROSS APPLY!

Thank you, Jeff.  A good use of CROSS APPLY.

Thank you, Desnorton.  We run in Azure SQL on a low end service tier so we don't use temp tables much.

I will look into the other possibility.

Hi Phil,

Pretty cool, your solution runs fastest compared to  Row_Number solution and max/group by solution.  Thank you very much!

Yes,, under Project Properties,  there is a target platform dropdown that goes from SQL Server 2005 to Microsoft Azure SQL Database V12.  I set Microsoft Azure SQL Database V12.
Setting...

Many thanks to both Eirikur and Luis for providing a solution though the statement is unclear .  That works for what I am planning to do.  It is 

April 10, 2017 at 1:10 pm

#1937497

Hugo,

Thank you very much for the solution which is much compact and easier to read and follow that what I had.