ohemaa
asked on
Script users
What is the best way to script user logins after a databases restore from another server
all users came over but no logins
currently using this:
exec sp_addrolemember N'db_owner', N'Tman'
I have many users. Is there a way to script logins for all the users
all users came over but no logins
currently using this:
exec sp_addrolemember N'db_owner', N'Tman'
I have many users. Is there a way to script logins for all the users
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
rboyd56:
Are you referring to these 2 options
1.Script database users and database roles
2.Script Sqlserver Logins(windows and server Logins)
Are you referring to these 2 options
1.Script database users and database roles
2.Script Sqlserver Logins(windows and server Logins)
Even if you use Windows authentication there has to be a SQL Server login. The NT user (or group) is just mapped to a SQL Server login.
ASKER
so are these the 2 scripts that I need
No it will create one that has both
That should have been:
No it will create one that has both logins and users
No it will create one that has both logins and users
ASKER
did get some logins but not all
It may get only the logisn that are mapped to uses in that particular database. So you may need to do this for all databases to get all the logins.
ASKER
ok I think restoring the system Db's (master, model, msdb) is easier. it brings all the users and logins to the new server
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I restored system db's to the same path as old. The users databases came as suspects. I restored them from backups and they are all ok. I have all users and logins. Am i good? I cannot test it now becasue the old one is still in production. downtime will be next week. this is just practise. Am I going to be ok if I do the same during actual downtime.
If all the databases came up tehn I think you should be ok. You may want to restore the msdb database as well. It holds any scheduled jobs and DTS packages.
ASKER
Yes. I restored all the user databases from recent backup ok. I restored MSDB as well. it brought all the jobs and DTS packages over so I think I am good. what do you think?
I would think so
Select the origina database
Right click and choose all tasks
Generate SQL Script
On the options tab select script users and logins options.