Logins
-
In SQL Server 2000 in Windows 2003 Server
I encounter strange thing...
What Happen:
In the EM, Security --> Logins, I see all the Name of Logins while
In Database --> Users, I see the lists of Users same as in Security -- > Logins in one computer (connected same loginname & password in the sql server) In other computer, I only see the name of users that have Login name beside it, know what I mean? any ideas?
Did anyone encounter what I encounter today?
-
i did the same and works fine for me ... strange thing, may be something is wrog in the second computer, try in a third computer
JR
-
Noelson,
You probably dealing with orphaned users which popup after a restore of you database.
There are serveral way of how to solve this;
Manualy - delete them from the database user list and reconnect them from the security logins
Script - there are some scripts doing this for you ( search the internet for this )
basic steps; run script on source database which retrieves all login information
from database and registry - copy/pate this outcome to you new environment
( restored database) - execute this script and all logins will be restored.
Very handy if you have a lot of users with different permissions.
Hopes this pushes you into the right direction.
GKramer
The Netherlands
-
Thanks Guus! You're Right! That what I did and it's works now! =)
-
Just wondering why this happen???
Luckily, I can see in one computer the list of users so that I can assign them...
-
Noelson,
Brief answer on your question:
When restoring ( overwrite ) a database all excisting logins will be disconnected from the security-logins to the database ( before the actual overwrite). The backup mechanism will restore ( overwrite ) the database including all know user for that database ( these are included in the backup file ( BAK )). After overwriting the old database it tries to restore the connection from the NEW database to the security-logins. If one of the users is known then it automaticly will be reconnected. IF NOT it stays open !! We know this phenomena as ORPHANED user. Mention this; The database users vs security-logins are case sensitive!!! ( "Report_user" is a different user as "report_user" ).
More info is to be found on the Internet
example;
Guus
-
Hi Guus! Just wondering if you have the scripts? email me leonson@hotmail.com
Cheers!
>Script - there are some scripts doing this for you ( search the internet for >this ) basic steps; run script on source database which retrieves all login >information from database and registry - copy/pate this outcome to you >new environment ( restored database) - execute this script and all logins >will be ?restored. Very handy if you have a lot of users with >different permissions.
-
Hi,
I had same problem about orphaned users, i just ran update query on the sysusers table for all the databases for orphaned users to update their sid to correct one, but it's not advisable to update system table directly until you know about it. so it is preferred to use sp_change_users_login.
One problem i am facing regarding orphaned users is, i can't see orphaned users in enterprise manager on my computer. But my collegue can see orphaned users in databases. How this can happen? Is there any way I can see orphaned users in my enterprise manager too? It shows up after it fix the logins. Any ideas?
-
To share some knowledge;
(taken from nij own notes)
Hopes this helps you guys...
Guus Kramer,
The Netherlands
8888888888888888888888888888888888888888888888888888888888888888888888888888888888
Move orphaned users from server A to B
- Run the following script on the source SQL Server. This script creates two stored procedures named sp_hexadecimal and sp_help_revlogin in your master database. Continue to step 2 when you finish creating the procedure.
Note The following procedure is dependent on SQL Server system tables. The structure of these tables may change between versions of SQL Server, and selecting directly from system tables is discouraged.
----- Begin Script, Create sp_help_revlogin procedure -----
USE masterGOIF OBJECT_ID ('sp_hexadecimal') IS NOT NULL DROP PROCEDURE sp_hexadecimalGOCREATE PROCEDURE sp_hexadecimal @binvalue varbinary(256), @hexvalue varchar(256) OUTPUTASDECLARE @charvalue varchar(255)DECLARE @i intDECLARE @length intDECLARE @hexstring char(16)SELECT @charvalue = '0x'SELECT @i = 1SELECT @length = DATALENGTH (@binvalue)SELECT @hexstring = '0123456789ABCDEF'WHILE (@i <= @length)BEGIN DECLARE @tempint int DECLARE @firstint int DECLARE @secondint int SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1)) SELECT @firstint = FLOOR(@tempint/16) SELECT @secondint = @tempint - (@firstint*16) SELECT @charvalue = @charvalue + SUBSTRING(@hexstring, @firstint+1, 1) + SUBSTRING(@hexstring, @secondint+1, 1) SELECT @i = @i + 1ENDSELECT @hexvalue = @charvalueGOIF OBJECT_ID ('sp_help_revlogin') IS NOT NULL DROP PROCEDURE sp_help_revlogin GOCREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL ASDECLARE @name sysnameDECLARE @xstatus intDECLARE @binpwd varbinary (256)DECLARE @txtpwd sysnameDECLARE @tmpstr varchar (256)DECLARE @SID_varbinary varbinary(85)DECLARE @SID_string varchar(256)DECLARE @DefaultDataBase VARCHAR(100)IF (@login_name IS NULL) DECLARE login_curs CURSOR FOR SELECT sid, name, xstatus, password, convert(sysname, db_name(dbid))FROM master..sysxlogins WHERE srvid IS NULL AND name <> 'sa'ELSE DECLARE login_curs CURSOR FOR SELECT sid, name, xstatus, password, convert(sysname, db_name(dbid)) FROM master..sysxlogins WHERE srvid IS NULL AND name = @login_nameOPEN login_curs FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @DefaultDataBaseIF (@@fetch_status = -1)BEGIN PRINT 'No login(s) found.' CLOSE login_curs DEALLOCATE login_curs RETURN -1ENDSET @tmpstr = '/* sp_help_revlogin script ' PRINT @tmpstrSET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'PRINT @tmpstrPRINT ''PRINT 'DECLARE @pwd sysname'WHILE (@@fetch_status <> -1)BEGIN IF (@@fetch_status <> -2) BEGIN PRINT '' SET @tmpstr = '-- Login: ' + @name PRINT @tmpstr IF (@xstatus & 4) = 4 BEGIN -- NT authenticated account/group IF (@xstatus & 1) = 1 BEGIN -- NT login is denied access SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + '''' PRINT @tmpstr END ELSE BEGIN -- NT login has access SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + '''' PRINT @tmpstr END END ELSE BEGIN -- SQL Server authentication IF (@binpwd IS NOT NULL) BEGIN -- Non-null password EXEC sp_hexadecimal @binpwd, @txtpwd OUT IF (@xstatus & 2048) = 2048 SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')' ELSE SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')' PRINT @tmpstrEXEC sp_hexadecimal @SID_varbinary,@SID_string OUT SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name +''', @pwd , ''' + @DefaultDataBase+ ''', @sid = ' + @SID_string+ ', @encryptopt = ' END ELSE BEGIN -- Null passwordEXEC sp_hexadecimal @SID_varbinary,@SID_string OUT SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name +''', NULL , ''' + @DefaultDataBase+ ''', @sid = ' + @SID_string+ ', @encryptopt = ' END IF (@xstatus & 2048) = 2048 -- login upgraded from 6.5 SET @tmpstr = @tmpstr + '''skip_encryption_old''' ELSE SET @tmpstr = @tmpstr + '''skip_encryption''' PRINT @tmpstr END END FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @DefaultDataBase ENDCLOSE login_curs DEALLOCATE login_curs PRINT CHAR(13)+CHAR(10)+ 'GO'RETURN 0GO ----- End Script ----- - After you create the sp_help_revlogin stored procedure, run the sp_help_revlogin procedure from Query Analyzer on the source server. The sp_help_revlogin stored procedure can be used on both SQL Server 7.0 and SQL Server 2000. The output of the sp_help_revlogin stored procedure is login scripts that create logins with the original SID and password.
Save the output, and then paste and run it in Query Analyzer on the destination SQL Server. For example:
EXEC master..sp_help_revlogin
- Run the following script on the source SQL Server. This script creates two stored procedures named sp_hexadecimal and sp_help_revlogin in your master database. Continue to step 2 when you finish creating the procedure.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply