November 8, 2011 at 5:45 am
I have a certificate login that is meant to manage logins and in one of stored procedure signed by the certificate user, we grant or revoke CONNECT SQL permission on a specified login. When I first ran this sproc, the SQL Server gave an error saying that GRANTOR doesn't have GRANT permission. So, I went and granted the certificate login CONNECT SQL ... WITH GRANT permission.
Strangely, that did not fix the error. I was able to work around this by making the certificate a member of securityadmin fixed server role, but I worry this is giving too much permission to the certificate login. I'd like to know why CONNECT SQL ... WITH GRANT wasn't sufficient or if I've missed additional requirement in order to have the certificate login be capable of altering other logins' connect permission.
FWIW: Before this, the certificate user already had ALTER ANY LOGIN server permission.
November 25, 2011 at 4:15 am
Banana-823045 (11/8/2011)
I have a certificate login that is meant to manage logins and in one of stored procedure signed by the certificate user, we grant or revoke CONNECT SQL permission on a specified login. When I first ran this sproc, the SQL Server gave an error saying that GRANTOR doesn't have GRANT permission. So, I went and granted the certificate login CONNECT SQL ... WITH GRANT permission.Strangely, that did not fix the error. I was able to work around this by making the certificate a member of securityadmin fixed server role, but I worry this is giving too much permission to the certificate login. I'd like to know why CONNECT SQL ... WITH GRANT wasn't sufficient or if I've missed additional requirement in order to have the certificate login be capable of altering other logins' connect permission.
FWIW: Before this, the certificate user already had ALTER ANY LOGIN server permission.
The certificate login which is used to run the SP, should have the CONTROL SERVER permission also in order to manipulate the rights of other logins.
November 25, 2011 at 10:17 am
Do you happen to have any documentation suggesting this?
From this page, the securityadmin fixed role does not have CONTROL SERVER and ALTER ANY LOGINS is the only permission granted to this role.
AFAICT, granting CONNECT to other logins requires the GRANT option on the CONNECT permission. I'd have at least expected this page to say otherwise if CONNECT needed more than just a GRANT option.
The case here is that granting the certificate login ALTER ANY LOGINS permission doesn't work but adding it to a securityadmin fixed role does. None of the documentations I've seen so far suggests anything about securityadmin having CONTROL SERVER, hence my curiosity if it was documented somewhere.
Thanks!
November 29, 2011 at 4:58 am
I would not consider giving CONTROL SERVER permission at any instance as it is equal to sysadmin (with some less authority). Same is the case with security admin server role, as member of this role can easily elevate the permissions for itself or any other to same as sysadmin. So by auditing point of view, this should not be done in any circumstance. As far as ALTER ANY LOGIN is concerned, AFAIK, it would not mean it can give server level permissions
November 29, 2011 at 5:25 am
It's a known behavior.
The grantor (or the principal specified with the AS option) must have either the permission itself with GRANT OPTION, or a higher permission that implies the permission being granted.
If Database user mapped to a certificate, Membership in the db_securityadmin fixed database role, membership in the db_owner fixed database role, or membership in the sysadmin fixed server role is additional requirement.
November 29, 2011 at 5:32 am
This is where the Server level DDL Trigger comes into picture. See this link
http://qa.sqlservercentral.com/articles/Security/70905/
OR
You can use impersonation/ EXECUTE AS in the stored procedure depending upon your environment.
November 29, 2011 at 5:34 am
Dev (11/29/2011)
If Database user mapped to a certificate, Membership in the db_securityadmin fixed database role, membership in the db_owner fixed database role, or membership in the sysadmin fixed server role is additional requirement.
That was the case here. I didn't see that on the MSDN documentation so didn't realize that certificate user had additional requirement which now explain the behavior I had observed. Where is this documented?
Thanks!
November 29, 2011 at 5:39 am
Banana-823045 (11/25/2011)
Do you happen to have any documentation suggesting this?From this page, the securityadmin fixed role does not have CONTROL SERVER and ALTER ANY LOGINS is the only permission granted to this role.
AFAICT, granting CONNECT to other logins requires the GRANT option on the CONNECT permission. I'd have at least expected this page to say otherwise if CONNECT needed more than just a GRANT option.
The case here is that granting the certificate login ALTER ANY LOGINS permission doesn't work but adding it to a securityadmin fixed role does. None of the documentations I've seen so far suggests anything about securityadmin having CONTROL SERVER, hence my curiosity if it was documented somewhere.
Thanks!
Did you find your explanation/solution ?
November 29, 2011 at 5:40 am
Banana-823045 (11/29/2011)
Dev (11/29/2011)
If Database user mapped to a certificate, Membership in the db_securityadmin fixed database role, membership in the db_owner fixed database role, or membership in the sysadmin fixed server role is additional requirement.
That was the case here. I didn't see that on the MSDN documentation so didn't realize that certificate user had additional requirement which now explain the behavior I had observed. Where is this documented?
Thanks!
Verify Permissions section...
November 29, 2011 at 5:51 am
Divine Flame (11/29/2011)
Did you find your explanation/solution ?
See Dev's post. I think that's the answer.
Dev (11/29/2011)
Verify Permissions section...
Gotcha. The page I linked was for server permissions (my OP was about CONNECT SQL permission), but I wonder if CONNECT SQL implied a requirement for CONNECT so the requirement would be still same when discussing server level permissions as it would be with database level permissions?
November 29, 2011 at 5:58 am
Gotcha. The page I linked was for server permissions (my OP was about CONNECT SQL permission), but I wonder if CONNECT SQL implied a requirement for CONNECT so the requirement would be still same when discussing server level permissions as it would be with database level permissions?
That's the beauty of MSDN articles. They are well organized. :hehe:
Once I was searching for Encryption of Views but I didn't find the details in CREATE VIEW article. I found it in CREATE PROCEDURE article. :w00t:
November 29, 2011 at 6:44 am
Dev (11/29/2011)
That's the beauty of MSDN articles. They are well organized. :hehe:
I'm appreciating this now! I'm in an odd position because I'm doing my darnedest to soak up all best practices by reading up everything I can on MSDN (which I assumed is the same thing as BOL) but already finding that it's not always self-evident. Nonetheless, I now know to try and look a bit with more wider scope next time. 🙂
Once I was searching for Encryption of Views but I didn't find the details in CREATE VIEW article. I found it in CREATE PROCEDURE article. :w00t:
Gee, that's perfectly logical place. Can't imagine why it took you so long. 😛
November 29, 2011 at 9:41 pm
Banana-823045 (11/29/2011)
Dev (11/29/2011)
That's the beauty of MSDN articles. They are well organized. :hehe:I'm appreciating this now! I'm in an odd position because I'm doing my darnedest to soak up all best practices by reading up everything I can on MSDN (which I assumed is the same thing as BOL) but already finding that it's not always self-evident. Nonetheless, I now know to try and look a bit with more wider scope next time. 🙂
Once I was searching for Encryption of Views but I didn't find the details in CREATE VIEW article. I found it in CREATE PROCEDURE article. :w00t:
Gee, that's perfectly logical place. Can't imagine why it took you so long. 😛
I hope you are clear now that why CONTROL SERVER permission was required.;-).
November 30, 2011 at 3:44 am
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 login; 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.
November 30, 2011 at 3:51 am
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.
You made right decision there. Always remember The Principle of Least Privilege.
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply