October 20, 2008 at 9:52 am
Could someone explain the diiference between Grant and Enabled or Deny and Disabled? Doesn't Grant and Enabled essentially do the same thing - allow access to a SQL Server?
October 20, 2008 at 10:40 am
Grant and Deny are security settings. Enable and Disable are configuration and administration settings.
Enable/Disable control the state of the object that they are referring to: they turn it "ON" or "OFF", thus effectively making it either available or unavailable for everyone at once.
Grant, Revoke and Deny, are used to specify an individual's or group's specific rights to access the object. They do not affect the state of the object at all and do not either turn it "ON" or "OFF". They do make the object either available or unavailable for the specific group or individual.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 21, 2008 at 6:47 am
Thanks for the detailed response.
So if i don't want a SQL Server Login to access SQL Server itself I set the status to disabled and this will overide everything.
If a login has Grant rights to objects within databases, setting the status to Deny will overide all these? They will still be able to log into the SQL Server though.
To me though even though they do different things it is a very subtle difference. Even if a login has a status of enabled and denied they wouldn't be able to do anything anyway??
October 21, 2008 at 7:21 am
I don't think it's so subtle a difference. If a login is disabled, as rbarryyoung said, it can't do anything at all. If it's enabled, you can chose what to allow it to do with GRANT, DENY on the various securables. They're two very different things.
October 21, 2008 at 8:20 am
I agree with celestia, they are very different.
Steve Hindle (10/21/2008)
So if i don't want a SQL Server Login to access SQL Server itself I set the status to disabled and this will overide everything.
Technically, no. It does not override those other things, because it never gets to them. You have to login to the Server first, before you can access anything and if you are disabled, you cannot get past that first step.
If a login has Grant rights to objects within databases, setting the status to Deny will overide all these? They will still be able to log into the SQL Server though.
As long as they still have access to the initial database (or default DB, if initial DB is not specified). The second step of the Login process is to connect to the Initial/Default database. If it cannot do that, the SQL Server will automatically issue an error and log you back out again.
To me though even though they do different things it is a very subtle difference. Even if a login has a status of enabled and denied they wouldn't be able to do anything anyway??
"Denied" is not a status that a Login can have. You DENY a Login access to a class or group or level of items (or a specific item). You can have many different GRANTs and DENY's on the same LOGIN for different items and classes of items.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 21, 2008 at 8:30 am
Good explanation from Barry and others.
The idea behind disable/enable, is to grant access to the server or not. It's like having keys to the front door. You have them or you don't.
Grant/Revoke/Permission are permission statements. Those might be where you're allowed to go inside the building. The reason they're different is that you might want to allow access for someone, set it up, but perhaps disable their account (contractor, maternity leave,etc.) for some time and don't want to remove permissions. Or maybe you want to duplicate their permissions for the next person.
October 21, 2008 at 9:45 am
Thanks all. The penny has dropped!
The thing that threw me was the Grant / Deny option on the status tab of the Login Properties dialog.
October 21, 2008 at 7:53 pm
With respect to connecting to SQL Server, they effectively do the same thing. If the login is disabled or if it is denied the ability to connect to SQL Server, you can't log on with that login. Where there is a difference is with Windows groups. A Windows group login cannot be disabled within SQL Server. The only way to prevent members of a Windows group from coming in is to deny connect to that group. This, of course, functions as you would expect a DENY to function, meaning regardless of the other Windows group memberships, the user is prevented from logging on to the SQL Server if a member of the group denied connect.
K. Brian Kelley
@kbriankelley
October 22, 2008 at 7:18 am
Yes that does make sense. As well as an AD group, you can't disable a user AD account either.
Maybe it would have been clearer if the options were hidden or greyed out depending on which type of login you were changing the Status of.
Anyway, thanks for all the replies.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply