November 30, 2011 at 2:56 am
I've just set up a new database, with one user - schema dbo, and owned schemas db_owner, with role of db_owner.
I created a table - and now that user has no access to the table
When I try to grant permissions I get:
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
So I guess the user should not own the schema db_owner - otherwise permissions cannot be granted to it.
So how can I remove db_owner from the owned schemas of the user? - I can't untick it in the UI.
November 30, 2011 at 2:59 am
Please don't try it with 'dbo' user or schema. Create a new user / schema ‘xyz’ and try the same with it. I assume you would be able to achieve what you want.
November 30, 2011 at 3:06 am
No, the user is 'TestUser'
User's Default Schema is 'dbo'
And user has db_owner under Schemas Owned by User
and user is in db_owner under Database Role membership
I just want to remove db_owner from that user's owned schemas.
November 30, 2011 at 3:18 am
May I know why do you want to do it?
November 30, 2011 at 3:24 am
Well after looking through ALTER USER, DROP SCHEMA, ALTER DATABASE - I couldn't find any way to do this.
So I've just dropped the database and re-created it, with the user set up correctly this time.
November 30, 2011 at 3:31 am
It's good that you are able to resolve it. 🙂 But I am curious to know the scenario & what actually worked out there. Did you achieve following?
The user is 'TestUser'
User's Default Schema is 'dbo'
And user has db_owner under Schemas Owned by User -- this you removed in new DB
And user is in db_owner under Database Role membership
November 30, 2011 at 3:57 am
Yes just created the user without db_owner under Schemas Owned by User
I am curious. You use 'Dev' as a nickname. Would you describe yourself as a Developer or a DBA? Or is the nickname nothing to do with developer.
November 30, 2011 at 4:16 am
It's my name. 'Dev' is a Hindi (Indian) word which means 'God'. Coincidently I have worked as DEV and DBA both.
My name creates confusion (in IT industry at least). That’s why I read SSC post min. 2 times when I find ‘Dev’ word there (just to make sure poster is criticizing his / her Development team not to me ;-)).
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply