May 25, 2003 at 6:42 pm
Hi everyone,
I found an excellent article here by Gregory Larsen concerning removing orphan users.. happen whenever you restore database to another server. It works like magic. However i have a slightly different problem.
1)
Although these users are dropped from the respective database, they are after all valid users. How do i remap all those users again to the respective database?
2)
I would also like to drop all those users from the main SQL login.
Thanks.
May 25, 2003 at 9:15 pm
Would you be as kind to post a reference to the article?
May 26, 2003 at 4:06 am
quote:
2)I would also like to drop all those users from the main SQL login.
Take a look at sp_revokelogin, sp_denylogin, and sp_droplogin in BOL. Should do what you need.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
May 26, 2003 at 5:36 am
I think this is the article:
http://qa.sqlservercentral.com/columnists/glarsen/orphan_user.asp
Good stuff, assuming the orphaned users truly should be removed. In many cases (for example, restoring a bak from production to the dev server) you need to fix them, not remove them. Sp_change_users_login does that. Lot's of stuff here on the site about that if you need it. If you drop the user, you lose all the associated permissions.
Andy
May 27, 2003 at 3:18 am
Any ideas, if i cna get a hand on any pre-exisiting script that does the change users login.
Thanks
May 28, 2003 at 2:13 am
Good info Andy and neccessary tidy up, have fallen foul of this before. Now, before I create a database on a server from a backup and I know that the users of that database are still required then I create those users first with the same sid as the source server. Then no matter how many times I restore the database any access and permissions are intact.
Far away is close at hand in the images of elsewhere.
Anon.
May 28, 2003 at 3:33 am
I agree David, it saves SO much headache.
Rinoa, just run the proc with the 'report' option, that will show the orphaned users. Then run again using the 'auto_fix' option, it will update the SID to match the one in sysxlogins. Run again with the 'report' option when done to make sure everything worked.
Andy
May 30, 2003 at 2:28 pm
/* i use this script to fix database users when i move databases to a different server.
*/
declare @username varchar(100)
declare @usersid varchar(500)
declare @sql_cmd varchar(5000)
-- This gets all of the cc info from the utility table
declare cc_cursor cursor fast_forward for
select UserName = name, UserSID = sid from sysusers
where issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
order by name
OPEN cc_cursor
FETCH NEXT FROM cc_cursor into @username,@usersid
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql_cmd='sp_change_users_login '+char(39)+'auto_fix'+char(39)+','+char(39)+@username+char(39)
exec (@sql_cmd)
FETCH NEXT FROM cc_cursor into @username,@usersid
END
CLOSE cc_cursor
DEALLOCATE cc_cursor
go
sp_change_users_login 'report'
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply