469,076 Members | 1,529 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,076 developers. It's quick & easy.

Linking Windows SQL Login to user database

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
Aug 1 '08 #1
1 3474
(th*****@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, es****@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
Aug 1 '08 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by wschlichtman | last post: by
2 posts views Thread by Amedee Van Gasse | last post: by
8 posts views Thread by Nils Magnus Englund | last post: by
30 posts views Thread by diane | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.