Linking Windows SQL Login to user database

[thaixyz@gmail.com]

Hi,

I am migrating SQL 2000 database from one machine to another using the
detach/attach method. I used the TSQL
script sp_help_revlogi n 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_revlogi n, 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

[Erland Sommarskog]

Re: Linking Windows SQL Login to user database

(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_revlogi n 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_revlogi n, 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.sysl ogins
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@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx