July 14, 2016 at 4:05 pm
I ran into a bit of a mystery today.
We created an AD group that contains several remote users from Company A. Those users periodically upgrade an application (written by Company A) that we host in-house. To perform the upgrades, they need to have Sysadmin to the SQL Server instance and Local Administrator on Windows. To keep the remote users out of the SQL Server instance before and after an upgrade, in SSMS I connect to the SQL Server instance, go to Security\Logins and on the Status page for the AD group login, I change the 'Permissions to connect to the database engine' radio button from Grant to Deny.
Today, one of those remote users reported that he could still connect and do what he needed even though the group to which he belonged had its 'Permission to connect to database engine' set to Deny. I set up a test account and duplicated the situation - an account which was sysadmin on the SQL Server instance but had its 'Permissions to connect to database engine' value set to Deny. I completely shut down SSMS and re-opened it with the test account and lo and behold, I was still able to connect to the SQL Server instance as well as connect to a database and successfully execute a SELECT against a table. Only when I went into User Mapping and changed the access from SysAdmin to db_datareader was I prevented from doing anything.
Does anyone know why this is happening? Is it a bug or am I missing something obvious? I've always been told that one DENY will prevent a permission from being in effect but that doesn't seem to be the case here. Color me baffled.
I'm running SQL Server 2008 R2 (10.50.6220) 64 bit Enterprise and Windows Server 2008 R2 with 32 GB RAM.
Thanks for any pointers you can send my way.
Willem
July 14, 2016 at 4:12 pm
Membership in sysadmin implies that all permissions checks are voided. So that DENY has absolutely no effect. At all.
Had you only granted them CONTROL SERVER, I believe that DENY would work. However, with CONTROL SERVER there are a few things that you cannot do that sysadmin can. (But not very many.)
It should also work if you disable the login(s).
However, I think the best solution is to DENY andtake them out of sysadmin, and only add them to sysadmin they need to do work.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 15, 2016 at 8:57 am
Also keep in mind that these users may be members of yet another domain (or local admin) group that has a SQL Server login. For example, confirm if 'BUILTIN\Administrators' has a login, and if so investigate what it's members are. A few years back I posted an article that includes a query which attempts to report all accounts, domain groups, and members who have admin membership, either explicitly by account name or implicitly via membership in a group. Because implicit access by group membership is complicated, it's not perfect, meaning it might miss something, but it also may reveal some member access that you didn't know about.
http://www.sqlservercentral.com/articles/Security/76919/
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
July 15, 2016 at 9:28 am
Erland,
Thank you for your reply. I guess I was thinking that the DENY would outrank even the Sysadmin status, having heard the saying 'DENY always has precedence.....' for so long. Now that I think of it, if that applied to Syadmins, you could selectively remove permissions from a specific Sysadmin login with appropriately place DENYs - and that wouldn't make any sense. That login wouldn't be a Sysadmin anymore.
One additional assumption - this works the same way for a SQL Server login that was a Sysadmin as well, no? If I set the 'Permissions to connect to database engine' to DENY for a SQL Server login that was a member of SA, it would have no effect on the login?
Thanks for the education. I will change the way in which we deal with this AD account.
Willem
July 15, 2016 at 9:36 am
Any login (Windows or SQL) which is a member of the sysadmin fixed server role has all permissions and cannot be denied anything (basically SQL doesn't do permissions checks for any login that is a member of sysadmin)
DENY has precedence over GRANT, is the saying. sysadmin isn't technically granted permissions, it bypasses permissions checks.
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
July 15, 2016 at 10:16 am
Gail,
Your summary wraps the entire conversation into an easily understood statement. Thanks.
Willem
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply