Changing the GRANTOR for permissions

• Jpotucek

  SSCoach

  Points: 18583

  More actions

  August 13, 2010 at 6:12 am

  #387576

  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: 981

  More actions

  September 30, 2010 at 11:17 am

  #1229752

  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

  SSChasing Mays

  Points: 605

  More actions

  January 27, 2016 at 11:54 am

  #1854792

  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];