The script is a 3 part script that will generate another script that we will be using to restore the server/database log-ins/roles. You can run the generated script as a whole or for best results you can run it part by part.
1st PART
1st part that is generated is called "Generate scrip for server log-ins" before running this, you must supplement the neccessary password because as a default it will generate <password here> in the password section.
2nd PART
Second part is called "Generate script for server roles" which is the role of the server log-ins
3rd PART
Third part is called "Generate script for database user/roles" which is the database's users and roles
NOTE: Again I will repeat this script will also generate another script
After running the script (in the old server before the restoration) copy the result (result is another script) and run it to the server where youre stored your databases but before running this to the restore location please be reminded about the issue in orphaned users. You must delete all orphaned users first for the script in deleting orphaned users refer to this:
/***********************************
Script Made by: Lester A. Policarpio
Any Suggestions and Clarifications feel free
to email me at lpolicarpio2005@yahoo.com
This script will delete orphaned users in all
databases of a certain server
***********************************/
SET NOCOUNT ON
IF EXISTS (SELECT name from sysobjects where name = 'orphaned')
DROP TABLE orphaned
CREATE TABLE orphaned (DBName varchar(500), UserName varchar(250))
DECLARE @db varchar(500)
DECLARE @set varchar(1024)
DECLARE db CURSOR FOR
SELECT name FROM master..sysdatabases WHERE name NOT IN
('master','model','msdb','tempdb','pubs')--i didnt include the sysdatabases
OPEN db
FETCH NEXT FROM db INTO @db
WHILE (@@FETCH_STATUS = 0)
BEGIN
--SELECT statement below is from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12615
SET @set = 'SELECT UserName = name,'+''''+@db+''''+' as '+'''DBName'''+' FROM '+@db+'..sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
ORDER BY name'
INSERT INTO orphaned(UserName,DBName) exec (@set)
FETCH NEXT FROM db INTO @db
END
CLOSE db
DEALLOCATE db
--Second CURSOR
DECLARE @db2 varchar(500)
DECLARE @db3 varchar(500)
DECLARE @change_login varchar(50)
print 'Orphaned Users are : '
DECLARE db2 CURSOR FOR
SELECT DBName,UserName FROM orphaned
OPEN db2
FETCH NEXT FROM db2 INTO @db2,@db3
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @change_login = @db2+'..sp_revokedbaccess '+''''+@db3+''''
print '['+(@db3)+']'+' -------------------- in database: '+'['+(@db2)+']' -- lets you view first the orphaned users
--exec (@change_login) --execute this part and it will delete the orphaned users
FETCH NEXT FROM db2 INTO @db2,@db3
END
CLOSE db2
DEALLOCATE db2
DROP TABLE orphaned
SET NOCOUNT OFF
