Hi,

I am migrating SQL 2000 database from one machine to another using the
detach/attach method. I used the TSQL
script sp_help_revlogin procedure provided by microsoft to copy the
logins, this work for SQL Logins but not Window
Login.

I am able to logon to the SQL Server with the Window Login but have no
access to databases that I have right to
access. The Window Login name appears under the database's User node
but the value under the 'login name' is empty. So to relink it, I
change the database system table sysusers sid to match the Window
Login sid, this works but is there a better way to do this?
Is there a way to create Window Login with a specific security id
similar to how the SQL Login are create in sp_help_revlogin, cause I
am aware that SQL 2005 doesn't allow you to change system tables so
this way of doing it isn't very robust.

Thanks

(thaixyz@gmail.com) writes:
> I am migrating SQL 2000 database from one machine to another using the
> detach/attach method. I used the TSQL
> script sp_help_revlogin procedure provided by microsoft to copy the
> logins, this work for SQL Logins but not Window
> Login.
>
> I am able to logon to the SQL Server with the Window Login but have no
> access to databases that I have right to
> access. The Window Login name appears under the database's User node
> but the value under the 'login name' is empty. So to relink it, I
> change the database system table sysusers sid to match the Window
> Login sid, this works but is there a better way to do this?
> Is there a way to create Window Login with a specific security id
> similar to how the SQL Login are create in sp_help_revlogin, cause I
> am aware that SQL 2005 doesn't allow you to change system tables so
> this way of doing it isn't very robust.

SELECT 'EXEC sp_grantlogin ''' + name + ''''
FROM master.sys.syslogins
WHERE isntname = 1 OR isntgroup = 1

You should not have to play with sids for Windows logins, as they
should be the same on both servers.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx