Copy Logins - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Copy Logins

Author

Topic

SQLsearcher
Starting Member

47 Posts

Posted - 2005-08-08 : 10:36:44

Dear All

We are migrating to Active Directory (AD) and all our Domain accounts will be replaced. E.g. mine will change from "DOMAIN\Sql Searcher" into "INTRA\SSearcher". To ensure a smooth transition, I want to copy the old Windows logins to new AD logins, while keeping the same rights and roles. Is there a(n easy) way to do this? I tried sp_change_users_login, but it didn't do the trick.

Kind regards, Sql Searcher

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2005-08-08 : 10:50:35

-- grant tables
SELECT 'GRANT ' + PRIVILEGE_TYPE + ' ON ' + TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME + ' TO rlDBUser'
FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
WHERE GRANTEE = 'DBUser'

-- grant procs
SELECT 'GRANT EXEC ON ' + o.name + ' TO rlDBUser'
FROM syspermissions p
INNER JOIN sysobjects o ON p.id = o.id
INNER JOIN sysusers u ON p.grantee = u.uid
WHERE o.type = 'P' and USER_NAME(grantee) = 'DBUser'

-- grant functions
SELECT 'GRANT EXEC ON ' + o.name + ' TO rlDBUser'
FROM syspermissions p
INNER JOIN sysobjects o ON p.id = o.id
INNER JOIN sysusers u ON p.grantee = u.uid
WHERE o.type = 'FN' and USER_NAME(grantee) = 'DBUser'

--Add role members
select 'Exec sp_addrolemember ''' + R.Name + ''', ''' + 'rlDBUser' + ''''
from sysmembers M
inner join (select * from sysusers where issqlrole = 1) R on (M.GroupUID = R.UID)
inner join (select * from sysusers where issqlrole = 0) U on (M.MemberUID = U.UID)
Where USER_NAME(U.UID) = 'DBUser'

Daniel
SQL Server DBA
www.dallasteam.com

SQLsearcher
Starting Member

47 Posts

Posted - 2005-08-08 : 11:01:48

Hello SQLServerDBA_Dan

Thanks for the quick resonse. At first glance I miss the part about creating the new (AD) logins and their passwords. But because they are Windows logins, I don't need their passwords. And creating the users should not be a problem.

After submitting this topic I finally found something useful, it's an old topic you replied to. You referenced to KB article 246133. Together with your present reply, I think I can work it out. Should someone have beter solution, please tell.

Kind regards, Sql Searcher

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2005-08-08 : 11:14:25

quote:
Originally posted by SQLsearcher

Hello SQLServerDBA_Dan

Thanks for the quick resonse. At first glance I miss the part about creating the new (AD) logins and their passwords. But because they are Windows logins, I don't need their passwords. And creating the users should not be a problem.

After submitting this topic I finally found something useful, it's an old topic you replied to. You referenced to KB article 246133. Together with your present reply, I think I can work it out. Should someone have beter solution, please tell.

Kind regards, Sql Searcher

yeah what I posted today primarily works on a one by one basis; but, if you are using windows authentication then hopefully most of your logins should be defined in NT Groups and not in NT Users. So if you dont have a lot then I would just add the new nt groups one by one and then run this script on each. You will want to change the script to make it work with the user you are working on. "rlDBUser" is the new user and "DBUser" is the old one. I'm sure the script can probably be modified further but I just posted what I had in a saved .sql file. I use this script to change out user granted permissions for roles and I have a revoke script that is very similar that I run after I have transferred all the permissions from the user to the new role.

Good Luck.

Daniel
SQL Server DBA
www.dallasteam.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources