473,320 Members | 2,048 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 3646
(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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: tbone | last post by:
We were using sql authentication on our sql server 2000 servers, But need to use windows authentication. I have an sql user jdoe that owns many different objects in multiple databases. I have...
2
by: wschlichtman | last post by:
In my Windows application, I am attempting to access an SQL database called PhotoAlbum.mdf. I keep receiving the following error: Cannot open database "PhotoAlbum" requested by the login. The...
2
by: Amedee Van Gasse | last post by:
Hello, Since it is the first time I'm posting in these groups, I believe a (short) introduction of myself would not be a bad thing. I am mainly a support engineer, not a programmer. I do have...
8
by: Nils Magnus Englund | last post by:
Hello, I am having trouble using Integrated Windows Authentication between our intranet server and our database server, both of which are on our local domain. Windows authentication works for...
0
by: AAJ | last post by:
Hi all I am a relatively experiences Windows/SQL Server database programmes and have just started on my first ever web app. I have managed with most of the simple stuff i.e. datagrids, crystal...
4
by: Preben Zacho | last post by:
Hi there The scenario I got is this: I have created a Windows application in VS and I want to deploy it to another machine running Windows Vista. Since I have no control over this other machine,...
30
by: diane | last post by:
I've got an application running with table-based security: i capture the user's windows login with fOsusername, then have them enter a password checked against their username/login in my own table....
7
by: coolsti | last post by:
I have the task to set up an application at work,using MS Access as a front end to a MySQL database. This will be done using an appropriate ODBC driver, and linking the MySQL database to Access. ...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.