logins store procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

logins store procedure

Author

Topic

asarak
Starting Member

36 Posts

Posted - 2005-09-06 : 06:19:01

Hello again,

I try to restore my database named 'diagnostik' daily, into another server with same specs, all will be fine unless the users login information. The restore works fine.
*****************************************************
--this is the job that make the restore daily

use master

DECLARE @mname VARCHAR(64)
DECLARE @pdate char(6) , @pday char(2) , @pmonth char(2) , @pyear char(2)

SET @pday = right('0'+convert(varchar(2),day(getdate())),2)
SET @pmonth= right('0'+convert(varchar(2),month(getdate())),2)
SET @pyear = right(convert(char(4),year(getdate())),2)

SET @mname ='e:\b'+@pyear+ @pmonth + @pday

select @mname

restore database diagnostik from DISK = @mname

******************************************************************

After the restore the only thing that is not ok is the users logins
I also found a tip using the following to make them work...

--AFTER RESTORE IN NEW SERVER
use master

DECLARE @pwd sysname

-- Login: loginname
SET @pwd = CONVERT (varbinary(256), password in hexdecimal)
EXEC master..sp_addlogin 'loginname', @pwd, @sid = sidnumber, @encryptopt = 'skip_encryption'

and after that i run this script with cursor to fix them.

use diagnostik
DECLARE @username varchar(25)
DECLARE fixusers CURSOR
FOR
SELECT UserName = name FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
OPEN fixusers
FETCH NEXT FROM fixusers
INTO @username
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_change_users_login 'update_one', @username, @username
FETCH NEXT FROM fixusers
INTO @username
END

CLOSE fixusers
DEALLOCATE fixusers

***************************************************************
My question is how can i make this running under a job after the restore?? Every time i tried to do this i take an error because the users already exists (but the logins are not correct) and after the first login that sql found out returns error, so the job being terminated!!!!! And the job history has the following ....

**********************
Executed as user: [user name]. The login 'login name' already exists. [SQLSTATE 42000] (Error 15025) The login 'login name' already exists. [SQLSTATE 42000] (Error 15025). The step failed.
**********************

Any ideas????

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-07 : 01:52:26

determine if the login exists by using
if not exists(select * from sysusers where sid=@sid)
begin
do your stuff here...
end

HTH

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2005-09-07 : 02:43:11

Just curious Jen, is that enough? Could the same sid value be in both servers but for different users? (Or is that something which cannot be handled/deteced/repared anyway?)

I don't do this very much, and when I do I'm never 100% sure!

Kristen

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-07 : 03:15:36

I believe they'll be the same since he's restoring the database routinely

quote:

I try to restore my database named 'diagnostik' daily, into another server with same specs, all will be fine unless the users login information. The restore works fine.

what i actually do is,
drop the users and recreate, applying permissions
As practice, I always script users and permissions

but not sure if he's doing the same...

--------------------
keeping it simple...

asarak
Starting Member

36 Posts

Posted - 2005-09-07 : 03:17:34

Jen, Kristen i think its the same sid because the second server that i restore database is only for reporting and testing database so there is no other users created in the restored database. I am going to try the "exists" today
thanks a lot

asarak
Starting Member

36 Posts

Posted - 2005-09-07 : 03:20:27

May i also ask another question, do you have a script that i could take the users and passwords (to create this kind of list)
***
SET @pwd = CONVERT (varbinary(256), password in hexdecimal)
EXEC master..sp_addlogin 'loginname', @pwd, @sid = sidnumber, @encryptopt = 'skip_encryption'
***

that will help me to drop and recreate them just as you

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-07 : 03:32:45

actually I save the script I used for creating the logins, along with the permissions, it makes modification of permissions easier for me and resynchronizing

not sure about retrieving the password, haven't tried that before

--------------------
keeping it simple...

asarak
Starting Member

36 Posts

Posted - 2005-09-07 : 03:59:17

actually we create users with a program i will check this out with the profiler to see it

asarak
Starting Member

36 Posts

Posted - 2005-09-07 : 05:59:28

i try to catch up the logins with profiler but with no effect, the profiler write down the following message while i create a new login using the program we use....
--'sp_addlogin' was found in the text of this event.
-- The text has been replaced with this comment for security reasons.

anyway i will try now the

if not exists(select * from sysusers where sid=@sid)
begin
do your stuff here...
end

thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources