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_desc permission_name class_desc name

GRANT CONTROL SCHEMA dbo

GRANT CONTROL SCHEMA thys

GRANT CONTROL SCHEMA pvn

GRANT CONTROL SCHEMA a

GRANT CONTROL SCHEMA utility

GRANT CONTROL SCHEMA util

GRANT CONTROL SCHEMA mdt

DENY ALTER SCHEMA db_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:

name state_desc permission_name class_desc

horizonReader GRANT CONNECT DATABASE

horizonReader DENY ALTER SCHEMA

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:

name state_desc permission_name class_desc

horizonReader DENY ALTER DATABASE

horizonReader GRANT CONNECT DATABASE

horizonReader DENY ALTER SCHEMA

q2:

name name

horizonReader db_datareader

horizonReader db_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_name subentity_name permission_name

database CONNECT

database SELECT

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