January 12, 2015 at 9:41 am
Other than with linked servers and permissions outside of the instance that the user is sysadmin on, are there any scenarios that come to mind where you can be SA/sysadmin and be DENIED rights to an object?
A few of us were just discussing this, I assume the answer is NO, sysadmin always has full rights which trump even explicit deny?
I created an SQL login as SA, then created a database user with deny read/deny write and mapped them to each other.
That user can still read and write, which is due to sysadmin rights trumping all.
January 12, 2015 at 10:02 am
As far as I know, sysadmin grants all permissions regardless of what has been denied.
I even tried to deny CONTROL SERVER to a sysadmin login, but it had no effect, so I think it's safe to assume that there's no way to revoke/deny permissions from sysadmins.
-- Gianluca Sartori
January 12, 2015 at 1:25 pm
spaghettidba (1/12/2015)
As far as I know, sysadmin grants all permissions regardless of what has been denied.I even tried to deny CONTROL SERVER to a sysadmin login, but it had no effect, so I think it's safe to assume that there's no way to revoke/deny permissions from sysadmins.
Agreed. You can deny anything you want, but the sysadmin server role trumps everything else.
January 12, 2015 at 3:17 pm
As I understand it, membership in sysadmin means that all permissions checks are waived.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
January 13, 2015 at 1:51 am
Erland Sommarskog (1/12/2015)
As I understand it, membership in sysadmin means that all permissions checks are waived.
Correct.
Any login that's a member of the sysadmin fixed server role bypasses ALL security checks. Hence it is impossible to deny anything to any member of sysadmin as the permission chain is never checked.
Worth noting that the same is not for the server-level permission CONTROL SERVER
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
January 13, 2015 at 2:58 pm
GilaMonster (1/13/2015)
Erland Sommarskog (1/12/2015)
As I understand it, membership in sysadmin means that all permissions checks are waived.Correct.
Any login that's a member of the sysadmin fixed server role bypasses ALL security checks. Hence it is impossible to deny anything to any member of sysadmin as the permission chain is never checked.
Worth noting that the same is not for the server-level permission CONTROL SERVER
Moreover CONTROL SERVER does not really mean the same as sysadmin: many system stored procedures still check for sysadmin membership and CONTROL SERVER is no good for that.
-- Gianluca Sartori
January 14, 2015 at 12:34 am
spaghettidba (1/13/2015)
GilaMonster (1/13/2015)
Erland Sommarskog (1/12/2015)
As I understand it, membership in sysadmin means that all permissions checks are waived.Correct.
Any login that's a member of the sysadmin fixed server role bypasses ALL security checks. Hence it is impossible to deny anything to any member of sysadmin as the permission chain is never checked.
Worth noting that the same is not for the server-level permission CONTROL SERVER
Moreover CONTROL SERVER does not really mean the same as sysadmin: many system stored procedures still check for sysadmin membership and CONTROL SERVER is no good for that.
Correct.
Simplest example is : xp_readerrorlog
Unavailable for non sysadmins who have been granted Control Server.
+ you can actually DENY stuff for Control Server.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply