March 7, 2024 at 3:05 pm
Yesterday I configured some new databases and granted a couple of AD-groups auth to perform their stuff.
Today, the admin-group could not create objects in their database.
Ad-group membership was OK
Account was not locked out.
Only thing I did after this initial setup is upgrade the SQL Server 2019 to CU25, upgrade the Windows to the latest updates, reboot the node and failover the instance.
Turned out, the AD-group 's SID changed !!!
Checked in the AD, create date of that group is still 2023-08-14.
AD-admin confirms nothing has changed for this AD-group.
Fix:
Drop and create the AD-group Login at SQL Server instance level.
and Drop / create the AD-group on the DB ( and grant the needed auth in each db )
Anyone else experienced such wierd things?
if OBJECT_ID('tempdb..#wrk') is not null
drop table #wrk ;
CREATE TABLE #wrk(
[DbName] [nvarchar](128) NULL,
[name] [sysname] NOT NULL,
[InstanceSID] [varbinary](85) NULL,
[DbSID] [varbinary](85) NULL,
[is_disabled] [bit] NULL
);
exec sp_MSforeachdb @command1 = N'use [?];
insert into #wrk
Select DB_NAME() DbName, SP.name, SP.sid InstanceSID, DP.sid DbSID, SP.is_disabled
from sys.server_principals SP
inner join sys.database_principals DP
on SP.name = DP.name collate SQL_Latin1_General_CP1_CI_AS
where SP.sid <> DP.sid
and SP.name <> ''public'';
'
Select *
from #wrk
order by name;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 8, 2024 at 8:47 am
#RCA :
- Instance was installed and prepared on 2023-08-08.
- AD-groups were created and granted for the instance.
- 2023-08-14 for some reason the AD-group has been deleted and recreated in Active Directory.
- 2024-03-06 db setup has finally been performed by the project team and the issue showed up due to "CREATE TABLE permission denied in database"
This is a quick reminder that AD-group's SID only get recorded by SQL Server at "CREATE LOGIN" time and can be used to grant to databases.
However, the cannot be used until the sid mismatch has been fixed.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply