Thank you Perry and Gail, your explanations helps a lot.

Unfortunately I'm not sole admin of the DB and will need to investigate who gave the permissions and why.

Ignorance is not...

Thank you all so much, can't believe how many hours whent into this silly thing.

I now looked at the dbo schema's permissions and there is a public user with explicit...

Perry, it returns the following:

state_descpermission_nameclass_descname

GRANTCONTROLSCHEMAdbo

GRANTCONTROLSCHEMAthys

GRANTCONTROLSCHEMApvn

GRANTCONTROLSCHEMAa

GRANTCONTROLSCHEMAutility

GRANTCONTROLSCHEMAutil

GRANTCONTROLSCHEMAmdt

DENYALTERSCHEMAdb_datareader

Hmm, i guess this should not be a factor, but this db was ported from 2005 where it was created to now 2008 R2.

Thank you Ankit Shah, it seems that I'm having a funny variant of this role, for mine allow alter and update.

Ok, here is the results:

namestate_descpermission_nameclass_desc

horizonReaderGRANTCONNECTDATABASE

horizonReaderDENYALTERSCHEMA

I am unable to remove the last entry due to an exception occurring.

But this deny also does not do anything, I am still able to alter.

Thanks...

I get

'Msg 297, Level 16, State...

Tried it, then right back to updates and altering ;(

Returned no results.

There is a default schema 'dbo' but none selected...

No object owner or db_owner ;(

Sorry guys, it must be related to my specific db some how.

I tried db_datareader on master, then alter is denied.

Here goes

q1:

namestate_descpermission_nameclass_desc

horizonReaderDENYALTERDATABASE

horizonReaderGRANTCONNECTDATABASE

horizonReaderDENYALTERSCHEMA

q2:

namename

horizonReaderdb_datareader

horizonReaderdb_denydatawriter

The denies is what I had to specify later.

It's a SQL server account

I did, the user had nothing else, even if I run:

SELECT * FROM fn_my_permissions (NULL, 'DATABASE');

I only get

entity_namesubentity_namepermission_name

databaseCONNECT

databaseSELECT

I had to explicitly deny alter and update on database permissions for the...

Interesting, took execution time down to 1 sec, but the dynamic is still faster.

Thanks for the info, will surely be useful in the future 🙂

Kind regards