November 30, 2011 at 4:19 am
Banana-823045 (11/30/2011)
Divine Flame (11/29/2011)
I hope you are clear now that why CONTROL SERVER permission was required.;-).Uh, no. CONTROL SERVER was never ever needed for what I needed to do with the certificate server; as I've already explained before, adding the certificate user to the securityadmin fixed role was all what was needed to fix the problem. The securityadmin fixed role does not have CONTROL SERVER permission. Dev's explanation of the additional requirement for certificate user was sufficient, especially in light of the fact that a fixed role doesn't always equal the set of permissions assigned to the fixed role.
I suppose I could have just given it CONTROL SERVER and thus avoid the extra hassles with insufficient privileges to perform its job but to be honest, it just strikes me as too big a security risk to paint with so broad a brush. I was hoping that I was just missing a extra privilege and didn't needed securityadmin fixed role but Dev's explanation makes it clear that is what is needed in order to enable the certificate user to grant / deny CONNECT SQL to other logins.
I hope you keep in mind that adding member to security admin is almost equal to giving CONTROL SERVER permission. 🙂
November 30, 2011 at 4:25 am
Usman, correct. That's what I was really hoping to not have to do.
At least the certificate login cannot be impersonated, and signing any sprocs requires CONTROL permission on the certificate, which is available only to the sysadmins' logins and I have no intention of giving anyone else that permission. So if they somehow achieved the capability to sign their own procedure with this certificate, I think I've got bigger problems at that point.
November 30, 2011 at 4:28 am
Usman Butt (11/30/2011)
I hope you keep in mind that adding member to security admin is almost equal to giving CONTROL SERVER permission. 🙂
Not Exactly. CONTROL SERVER has more privileges.
http://msdn.microsoft.com/en-us/library/ms175892(v=SQL.105).aspx
November 30, 2011 at 4:31 am
This is why I was hoping 🙂 So you keep the situation under control.
November 30, 2011 at 4:38 am
Dev (11/30/2011)
Usman Butt (11/30/2011)
I hope you keep in mind that adding member to security admin is almost equal to giving CONTROL SERVER permission. 🙂Not Exactly. CONTROL SERVER has more privileges.
http://msdn.microsoft.com/en-us/library/ms175892(v=SQL.105).aspx
I said it is almost equal to, as It can be made equal to in some other manner. Please read this to understand it better.
http://qa.sqlservercentral.com/articles/Security/70905/
Most of the time we overlook this.
November 30, 2011 at 4:39 am
Yes, you made the right decision :-). Although, SecurityAdmin itself is equivalent to sysadmin with few minor exceptions. If you are that much concerned, you must read this article from Brian Kelley so that you can restrict SecurityAdmin users too.
http://qa.sqlservercentral.com/articles/Security/70905/
November 30, 2011 at 4:43 am
Divine Flame (11/30/2011)
If you are that much concerned, you must read this article from Brian Kelley so that you can restrict SecurityAdmin users too.
You got late by some seconds referring to this thread 🙂
November 30, 2011 at 4:47 am
You got late by some seconds referring to this thread 🙂
lol right 😀
November 30, 2011 at 4:52 am
Thanks usman & sujeet for the link to the article. The idea of using DDL trigger to block the CONTROL SERVER grant sounds like a good idea if I can't work around not granting securityadmin fixed role. I bet I could also add something to also block IMPERSONATE (I'm inclined to only want to grant this, if ever, using sysadmins only; from what I've seen about IMPERSONATE, it doesn't strike me as very useful permission compared to say, signing a sproc or using EXECUTE AS clause in the sprocs).
Edit: blargh. Read comprehension fail on my part; the simple trigger Brian wrote already handled IMPERSONATE as well, and unconditionally as I would like it.
November 30, 2011 at 4:53 am
It might be a security issue in SS2K, not any more.
•Members of the securityadmin role cannot make any login a member of the sysadmin fixed server role.
•Members of the securityadmin role cannot reset the passwords on any logins which are members of the sysadmin fixed server role.
November 30, 2011 at 4:57 am
Dev, that much is clear, yes but securityadmin can grant CONTROL SERVER to other logins. While CONTROL SERVER isn't same as being a sysadmin, that's just too much for my liking.
November 30, 2011 at 5:03 am
Dev (11/30/2011)
It might be a security issue in SS2K, not any more.•Members of the securityadmin role cannot make any login a member of the sysadmin fixed server role.
•Members of the securityadmin role cannot reset the passwords on any logins which are members of the sysadmin fixed server role.
Please read the whole article in detail. You will get our point.
November 30, 2011 at 5:09 am
Usman Butt (11/30/2011)
Dev (11/30/2011)
It might be a security issue in SS2K, not any more.•Members of the securityadmin role cannot make any login a member of the sysadmin fixed server role.
•Members of the securityadmin role cannot reset the passwords on any logins which are members of the sysadmin fixed server role.
Please read the whole article in detail. You will get our point.
Yup. I see your point. It's BIG loop hole in SQL Server Security. I don't doubt on Brian's article but would try it myself (to hack other DBA's DBs :w00t:)
November 30, 2011 at 5:25 am
Dev (11/30/2011)
Usman Butt (11/30/2011)
Dev (11/30/2011)
It might be a security issue in SS2K, not any more.•Members of the securityadmin role cannot make any login a member of the sysadmin fixed server role.
•Members of the securityadmin role cannot reset the passwords on any logins which are members of the sysadmin fixed server role.
Please read the whole article in detail. You will get our point.
Yup. I see your point. It's BIG loop hole in SQL Server Security. I don't doubt on Brian's article but would try it myself (to hack other DBA's DBs :w00t:)
I know that you said it as a joke, but you should not have mentioned hacking on this professional forum. Hope you edit your post. Thanks.
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply