Changing the GRANTOR for permissions

  • Jpotucek

    SSCoach

    Points: 18583

    We recently had a new SQL Server (Active\Passive) Cluster built at a new Hosting Facility for all of our Web Applications.

    Once the Cluster was built, it was handed over to me and I logged on to the Cluster node with my own Domain Credentials and made some Instance Settings Changes and restored all user Databases.

    Now when I go back through all of the settings (Management Studio\Right click\Properties\Permissions) I see two windows on the permissions tab:

    top window LOGINS and bottom window EXPLICIT PERMISSIONS

    I see my own Domain ID as Grantor for all logins including the following:

    ##MS_SQLReplicationSigningCertificate##]

    ##MS_SQLResourceSigningCertificate##]

    BUILTIN\Administrators]

    ##MS_SQLAuthenticatorCertificate##]

    ##MS_AgentSigningCertificate##]

    NT AUTHORITY\SYSTEM]

    How do I change this to be a different accout (either SQL sa or a Domain Service Account)? Or does it matter? I believe they will be enabling password expiration to my account as well as taking me out of the local Admin Group and I don't want my domain account associated with anything to do with owning resources in the Instance (or the Cluster) any advice?

  • ntran777

    SSC Eights!

    Points: 841

    I would also like to know the answer to this. I'm trying to remove a user from my DB but he is the grantor on a few symmetric key permissions so it won't let me. How can I change the grantor?

  • louislamour2673

    Mr or Mrs. 500

    Points: 584

    I had this issue when trying to drop the login of a person that had done an AOAG install\config. That login had granted connect permission to the HADR endpoint for the SQL service account.

    Use this query to view endpoint information or just do * from sys.server_permissions to see everything.

    SELECT EPS.name, SPS.STATE,

    CONVERT(nvarchar(38),

    SUSER_NAME(SPS.grantor_principal_id))AS [GRANTED BY],

    SPS.TYPE AS PERMISSION,

    CONVERT(nvarchar(46),SUSER_NAME(SPS.grantee_principal_id))AS [GRANTED TO]

    FROM sys.server_permissions SPS , sys.endpoints EPS

    WHERE SPS.major_id = EPS.endpoint_id

    ORDER BY Permission,[GRANTED BY], [GRANTED TO]

    Used these queries to change the grantor and also re-grant connect on the endpoint for our SQL service account.

    ALTER AUTHORIZATION ON ENDPOINT::Name_of_endpoint TO sa;

    GRANT CONNECT ON ENDPOINT::Name_of_endpoint TO [SQL_Service_Login];

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply