June 7, 2005 at 1:12 pm
My SQL Server is using Windows Authntication and have around 30 Db's on this server. Recently the network team decide to change few NT Ids, is there any way to sync to my new Id rather than me changing manually.
If drop the old one and add new one I will lose the the prior security set on database. Any suggestions wouuld be great.
Example.
Domain/JDoe ==>Domain/JDoe02
June 8, 2005 at 3:00 am
This is not an easy one to sort out. One of the problems is that the sid held in sysxlogins is the NT account sid, but the name value is the old account name and there is no account rename facility in SQL Server.
One way to resolve this would be 1) Detach all user databases, 2) Delete all the renamed accounts from SQL Server, 3) Create new entries for the renamed account - these will have the same sid value as the old names, 4) Attach the user database. All your security should continue to work OK as SQL ties everything together with the sid value which has not changed.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
June 8, 2005 at 3:08 am
We had a similar problem where some NT group names were changed, but our standard security setup meant this was not a big problem for us.
We use database roles to provide security templates in all of our databases. All table, view, sp, etc authorities are granted only to the database roles. We then make the relevant NT groups members of relevant roles. When the group names were changed, we could quickly and safely drop the accounts out of SQL then re-create them and add them back into the roles. Because our security is NT group and DB role orientated, we feel it provides quick and accurate management of SQL security.
I know this may not help your immediate problem, but if you use DB roles you could find security is easier to manage in the future.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply