Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favourite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved in your SSC briefcase from the favorites tab.
Download now (direct download link)

Restore Server/Database users/roles

By Lester Policarpio,

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

Total article views: 3012 | Views in the last 30 days: 9
 
Related Articles
FORUM

Script to delete orphaned users

Script to delete orphaned users

FORUM

Orphaned Users

Domain Accounts not Showing up as Orphaned

SCRIPT

List orphaned users from all databases

The script returns all orphaned database users across a SQL Server.

ARTICLE

Removing Orphan Users from All Databases on Server

In this article by Greg Larsen, he shows you how to remove those pesky orphaned users that can be a ...

BLOG

SQL Server Orphanage – Using sp_change_users_login to find and fix all orphaned SQL users

Every wonder who all your orphaned SQL Server users are on your server? Just run the script below an...

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones