December 20, 2011 at 9:56 am
Another vote for 'db_owner' option.
Please uncheck all except 'db_owner' and you are good to go ahead. 🙂
December 20, 2011 at 10:01 am
Make sure you are not logged in as the user when you are trying to change their permissions.
Don't uncheck public. Leave it checked as well.
December 20, 2011 at 10:02 am
Did that, and the roles are reflected in the database user properties - but everything is checked in the owned schema's area, and greyed out so I can't change them
I'm thinking I should maybe drop the user and recreate, drop the tables then recreate under that users account -
December 20, 2011 at 10:06 am
Robert Davis (12/20/2011)
Make sure you are not logged in as the user when you are trying to change their permissions.Don't uncheck public. Leave it checked as well.
I believe it's against OP's checklist. I don't see any harm in un-checking it as well. If the user is part of application then it should be independent of Public role.
December 20, 2011 at 10:09 am
Glenstr (12/20/2011)
...I'm thinking I should maybe drop the user and recreate, drop the tables then recreate under that users account -
Please don't try so many options, we will lose the track. At this moment, Please verify your access from the application. It should work.
December 20, 2011 at 10:11 am
You can't drop a user that owns schemas. You have to change the schema owner first. Expand the schemas tab and change the owner of each schema.
December 20, 2011 at 10:17 am
Yes, I agree. Shouldn't be any harm in unchecking it.
December 20, 2011 at 11:01 am
Thanks all - great bunch of helpful people here - after unchecking all but db_owner it seems to be ok now. Still too used to Oracle, where everything is more or less denied by default, and select, insert, update etc. has to be granted specifically.
I need that MSSQL for Oracle DBA's course I think.. 😛
December 20, 2011 at 11:11 am
In SQL, by default permissions are neutral. Nothing granted, nothing denied. That means that the user cannot get any access to anything. There are two levels from there.
Grant: Explicitly give permission.
Deny: Explicitly refuse permission.
Deny overrules grant, so what can be done, for example, is to grant permission to read all tables (via the db_datareader role) and then explicitly deny read on one table. Net result, user has read access to all but one table and, no matter how many grants are done from various roles, will not be able to read that table.
Denies are a bit more complex and usually only done when you have multiple groups or users members of multiple groups and you want to do specific things with the permissions. Normally just grant what's necessary and leave it at that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply