July 18, 2018 at 5:51 am
Hello,
This is my first time here and I'm hoping you can can help.
I'm new to MS SQL and having problems deleting a server login. I can see the login was used to grant permissions on an Availability Group but that availability group no longer exists.
After much googling I've found the following but I'm not sure how to correct this.
SELECT class_desc,*
FROM sys.server_permissions
WHERE grantor_principal_id = (
SELECT principal_id
FROM sys.server_principals
WHERE NAME = N'[MYUser]');
This returns
class_desc, class, class_desc, major_id, minor_id, grantee_principle_id, grantor_principle_id, type, permission_name, state, state_desc
AVAILABILITY GROUP 108 AVAILABILITY GROUP 65536 0 268 260 AL ALTER G GRANT
AVAILABILITY GROUP 108 AVAILABILITY GROUP 65536 0 268 260 CL CONTROL G GRANT
AVAILABILITY GROUP 108 AVAILABILITY GROUP 65536 0 268 260 TO TAKE OWNERSHIP G GRANT
AVAILABILITY GROUP 108 AVAILABILITY GROUP 65536 0 268 260 VW VIEW DEFINITION G GRANT
AlsoSELECT NAME
,type_desc
FROM sys.server_principals
WHERE principal_id IN (
SELECT grantee_principal_id
FROM sys.server_permissions
WHERE grantor_principal_id = (
SELECT principal_id
FROM sys.server_principals
WHERE NAME = N'[MYUser]'));
returns
NAME, type_desc
DOMAIN\sqlservice WINDOWS_LOGIN
DOMAIN\sqlservice is the account used to run the SQL service.
If I do the below it returns no results.select * from sys.availability_groups;
Any assistance would be greatly appreciated.
Kind Regards
James
July 18, 2018 at 1:36 pm
jwrigley - Wednesday, July 18, 2018 5:51 AMHello,This is my first time here and I'm hoping you can can help.
I'm new to MS SQL and having problems deleting a server login. I can see the login was used to grant permissions on an Availability Group but that availability group no longer exists.After much googling I've found the following but I'm not sure how to correct this.
SELECT class_desc,*
FROM sys.server_permissions
WHERE grantor_principal_id = (
SELECT principal_id
FROM sys.server_principals
WHERE NAME = N'[MYUser]');This returns
class_desc, class, class_desc, major_id, minor_id, grantee_principle_id, grantor_principle_id, type, permission_name, state, state_desc
AVAILABILITY GROUP 108 AVAILABILITY GROUP 65536 0 268 260 AL ALTER G GRANT
AVAILABILITY GROUP 108 AVAILABILITY GROUP 65536 0 268 260 CL CONTROL G GRANT
AVAILABILITY GROUP 108 AVAILABILITY GROUP 65536 0 268 260 TO TAKE OWNERSHIP G GRANT
AVAILABILITY GROUP 108 AVAILABILITY GROUP 65536 0 268 260 VW VIEW DEFINITION G GRANTAlso
SELECT NAME
,type_desc
FROM sys.server_principals
WHERE principal_id IN (
SELECT grantee_principal_id
FROM sys.server_permissions
WHERE grantor_principal_id = (
SELECT principal_id
FROM sys.server_principals
WHERE NAME = N'[MYUser]'));
returnsNAME, type_desc
DOMAIN\sqlservice WINDOWS_LOGINDOMAIN\sqlservice is the account used to run the SQL service.
If I do the below it returns no results.
select * from sys.availability_groups;Any assistance would be greatly appreciated.
Kind Regards
James
Hello James -
Welcome to SSC. For your issue, what to do depends on what the error was - could have been owns endpoint, owns group, etc. Could you post the exact, full error message when you try to drop the login?
Sue
July 19, 2018 at 2:00 am
Hi Sue,
The exact error message is
Msg 15173, Level 16, State 1, Line 1
Server principal '[MYUser]' has granted one or more permission(s). Revoke the permission(s) before dropping the server principal.
July 19, 2018 at 10:44 am
jwrigley - Thursday, July 19, 2018 2:00 AMHi Sue,The exact error message is
Msg 15173, Level 16, State 1, Line 1
Server principal '[MYUser]' has granted one or more permission(s). Revoke the permission(s) before dropping the server principal.
You can revoke the permissions from that account (revoke take ownership..., revoke view definition...) but you would want to see if you need to have those granted again. I highly doubt it but you would want to monitor for any issues.
REVOKE Availability Group Permissions
Sue
July 19, 2018 at 10:49 am
Hi Sue,
It was only a test environment so I've deleted and re installed the SQL instance.
Thanks for your time though.
Regards
James
July 19, 2018 at 10:53 am
jwrigley - Thursday, July 19, 2018 10:49 AMHi Sue,It was only a test environment so I've deleted and re installed the SQL instance.
Thanks for your time though.Regards
James
If it was the service account for that instance, it wouldn't have been needed. It may have looked like it was needed as the virtual account is the one listed with sysadmin.
Sue
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply