Unable to disable login mapped to Windows AD group while logged in as 'sa'

Question

question

MikeWatson-3755 asked May 12, '22 ErlandSommarskog commented May 13, '22

Unable to disable login mapped to Windows AD group while logged in as 'sa'

Hello,

I'm getting the following error while trying to disable a login mapped to a global group in Active Directory:

Cannot alter the login '<domain>\<username>', because it does not exist or you do not have permission. (Microsoft SQL Server, Error: 15151)

I have sufficient permissions (I am logged in as 'sa'), and this only occurs with Windows AD groups. Also, I am able to delete this logins; I just cannot disable them.

Is this a bug, or is there any other way to fix? Right now I'm having to strip all permissions/roles granted to this login.

Also, this is running on an ancient version of SQL Server (2008 SP3 10.0.5538.0) that we must keep around for legal purposes.

Any help is much appreciated -- thanks!

sql-server-general

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2022-05-13T06:46:52.08Z

Yufeishao-msft answered May 13, '22 ErlandSommarskog commented May 13, '22

Hi @MikeWatson-3755,

From the article:ALTER LOGIN

You cannot use ALTER LOGIN with the DISABLE argument to deny access to a Windows group. For example, ALTER LOGIN [domain\group] DISABLE will return the following error message:

"Msg 15151, Level 16, State 1, Line 1 "Cannot alter the login 'Domain\Group', because it does not exist or you do not have permission."

so what was your operation at that time, whether made the same error?

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 2

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MikeWatson-3755 · May 13 at 12:14 PM

BINGO! I completely forgot to check Microsoft's documentation but this perfectly explains the issue I was having. I'm able to delete the login but not disable it b/c apparently this is not supported with Windows Groups.

Thank you so much for your assistance, and especially thanks for keeping your tone and response both professional and courteous. Have a wonderful day.

0 ·

ErlandSommarskog MikeWatson-3755 · May 13 at 08:19 PM

Actually, if you want to keep the Windows group but prevent login, this is possible, with the DENY statement I suggested:

DENY CONNECT SQL TO [Domain\Group]

However, I am not sure that I recommend it. This can be OK to do with a single user, but if you do it with a group, this may have undesirable consequences since DENY always trumps GRANT. This means that if UserA belongs to this group as well as a second group that should be able to login, the effect is that UserA will not be able to log in.

Nevertheless, I wanted to mention it, so that you know about the option.

0 ·

Answer 2 · 2022-05-12T18:58:01.54Z

TomPhillips-1744 answered May 12, '22

How exactly are you getting that error? Are you running a TSQL command in SSMS, using the GUI in SSSM, running SQLCMD?

That error is almost certainly because the login you entered does not exist. Do not confuse "logins" with "database users". They are different and can have different names.

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 3 · 2022-05-12T20:05:11.97Z

MikeWatson-3755 answered May 12, '22

It doesn't work via the GUI or TSQL. It's a login, not a user.

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 4 · 2022-05-12T20:23:49.32Z

ErlandSommarskog answered May 12, '22

You need to create the login before you can disable it:

CREATE LOGIN [Domain\KeepThisUserOut]
ALTER LOGIN [Domain}KeepThisUserOut] DISABLE

However, I suspect that you will find that this user can still connect, since apparent the user is member of an AD group which has access.

If the aim really is to keep this user out, you can add this

REVOKE CONNECT SQL TO [Domain\KeepThisUserOut]

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 5 · 2022-05-12T20:46:00.11Z

MikeWatson-3755 answered May 12, '22 MikeWatson-3755 commented May 13, '22

No need to create it. The login already existed.

· 2

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog · May 12 at 08:48 PM

Apparently not, judging from the error message.

Do you see the login in sys.server_principals?

0 ·

MikeWatson-3755 ErlandSommarskog · May 13 at 12:10 PM

Yes, I saw the login listed in sys.server_principals and when viewing server logins in Object Explorer/Object Explorer Details.

0 ·

question