Viewing 15 posts - 1 through 15 (of 38 total)
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...
January 5, 2012 at 12:54 pm
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...
January 5, 2012 at 11:04 am
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.
January 5, 2012 at 10:44 am
Thank you Ankit Shah, it seems that I'm having a funny variant of this role, for mine allow alter and update.
January 5, 2012 at 10:40 am
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...
January 5, 2012 at 10:37 am
Lowell (1/5/2012)
try this: maybe the login is a sysadmin:you'll need to change the name of the login to your specific login/sql user in question:
I get
'Msg 297, Level 16, State...
January 5, 2012 at 10:23 am
GilaMonster (1/5/2012)
Can you remove the denys and check if the user still has those permissions?
Tried it, then right back to updates and altering ;(
January 5, 2012 at 10:22 am
Perry Whittle (1/5/2012)
ajsnyman (1/5/2012)
ALTER DATABASEALTER SCHEMA
These are not object level but higher permissions and imply certain other permissions. Does the user own a schema?
There is a default schema 'dbo' but none selected...
January 5, 2012 at 6:09 am
Sorry guys, it must be related to my specific db some how.
I tried db_datareader on master, then alter is denied.
January 5, 2012 at 4:22 am
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.
January 5, 2012 at 4:10 am
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...
January 5, 2012 at 2:55 am
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
November 14, 2011 at 4:08 am
Viewing 15 posts - 1 through 15 (of 38 total)