upgraded from 2010 want to remove all old users and start from scratch

(0) Share

Report

Posted on by Community Member

Hello,

I am in the process of upgrading from 2010 to a new server. I restore the company databases and ran utilities to upgrade to 2015. I have a lot of old unneeded users and just want to start from scratch and remove all the old users and start from scratch. Is there a SQL script that will do this? I assume after blowing all the users away I will need to re-create DYNSA and sa. Is there instructions on how to clean up all the users and start again with new ones? Thank You

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (0)

Richard Wheeler 75,848 Moderator on at

Like (0)

Report

You could use the capture logins scripts from KB878449 and after it creates the scripts edit the script by replacing all CREATE LOGINS with DROP USER against each company database.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

So would I run the capture logins script against the old sql server or the new one? Could there be any drawbacks from wiping out all my users?

Thanks for your help

Was this reply helpful? Yes No
Richard Wheeler 75,848 Moderator on at

Like (0)

Report

You would run the capture logins scripts against the old server and then the modified version against each database on the new server. Then you will need to remove user records from the DYNAMICS..SY01400 table. Then run Tools->Utilities->System->Reconcile on everything to clear out the associated user records in other tables.

Was this reply helpful? Yes No
Richard Wheeler 75,848 Moderator on at

Like (0)

Report

The only drawback would be if you remove someone who should not be removed. Then you would owe them lunch.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

ok it looks like I only have 18 users can I just go into SQL Management Studio Security -> Users right click and delete them? There is some inconsistency between DYNAMICS and the two company databases, too, not sure how that happened.

Was this reply helpful? Yes No
Richard Wheeler 75,848 Moderator on at

Like (0)

Report

That would work but you would still need to remove them from the DYNAMICS..SY01400 TABLE. Otherwise you would have trouble in the future if you ever tried to use the same ID again. The cleanest way to do this would be to use the capture users scripts and get them all established on the new server and then use GP to delete the users. That way you cleanup GP and SQL in one step. Any orphaned users you can clean up manually.

Was this reply helpful? Yes No
Suggested answer

L Vail 65,271 on at

Like (0)

Report

Hi,

Here's a script that might help if you can do them one by one:

--Remove a user from all databases on the server

EXEC sp_Msforeachdb "IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'baduser')

DROP USER [baduser]"

GO

--Drop the login

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'baduser')

DROP LOGIN [baduser]

GO

Kind regards,

Leslie

Was this reply helpful? Yes No
Suggested answer

Beat Bucher GP Gee... 28,058 Moderator on at

Like (0)

Report

The GP Power Tools has a function that can do that :-) Yes it Can !... delete all the SQL users in the server security (and their company assignment of course)... Then in GP, with Power Tools you can recreated all the SQL users with a new default password, based on the stored GP logins from the SY01400 table.. and their respective company assignment.

This pretty much removes the need of the KB from Microsoft to recreate all the SQL security logins and even better, old 'ghost' user id's that have been orphaned for many years are getting cleared out in the process.

The tools used for this is called 'Database Validation' from the GPPT routines menu.. Check the user guide at page 160.

Note : Database validation does much more than just that... so it's 100 times worth the cost of the license (1$/day).. you can even get a 30 day trial key from Mekorma.

Was this reply helpful? Yes No