July 6, 2009 at 3:13 am
Need to trasfer or create login on the secondary databases ??
Is there any possibility ?? as login creation is not allowed on standby DB / Read Only (LogShipping already configured)...
I have a DB role but no login... My requirement is to provide the access for TEST team...
Help required...
Cheers,
- Win.
" Have a great day "
July 6, 2009 at 3:44 am
Is the secondary database a logged shipped database?
edited: punctuation added
Gethyn Elliswww.gethynellis.com
July 6, 2009 at 3:46 am
Thanks for quick reply,
Yes, its a Logshipped server (secondary database server)
I can see DB role and i need to have the same account for Login. With that account name we are running some services...
Cheers,
- Win.
" Have a great day "
July 6, 2009 at 3:52 am
Not sure if there is another method but i think the only way around this would be to create the login and database user on the primary...The DB user will ship to the secondary database, you can then follow the method described in this article http://support.microsoft.com/kb/918992 to re-create the login with the correct SID on the secondary server...Thus the login and user matching up on the secondary. You can the remove the login (Not the database user on the primary server)
Gethyn Elliswww.gethynellis.com
July 6, 2009 at 4:18 am
Logshipping is DB Based . Logins are SQL Server related. Logshipping would do transfer all transactions using transaction logs but to access the data in Secondary, Logins have to be transferred from Primary server to Secondary Server.
July 6, 2009 at 4:18 am
Thanka for the post.
But cannot do the same as "SID is already in use" thisis the error am facing with the process...
Anyother help...
Cheers,
- Win.
" Have a great day "
July 6, 2009 at 4:34 am
you try a reverse of the process:
1 create the login on the secondary box...
2. Get the sid of this login.
3. Recreate the login on the priamry with the exact same sid as the secondary...map the login to a user in the primary and allow the user in the primary to ship to the secondary creating the db user in the secondary with matching login...you will then have a login with matching sid in the secondary
Gethyn Elliswww.gethynellis.com
July 6, 2009 at 4:53 am
Can't create a Login on the secondary server right ?
as the database will be in ReadOnly mode.... It will be like a dummy login, though its same in DB user role....
coming to Primary server we have a same login for that DB , but not getting shipped to secondary...
any other help pls...
Cheers,
- Win.
" Have a great day "
July 6, 2009 at 5:06 am
If you have created a database user in the primary database, it will be included in the log shipped version when that log is restored. basically the creation of a user in the database will be included in that databases transaction logs...The creation of the server login, will not be included.
Does you secondary db have the user you need in it?
If it does then you need someway of creating a login on the secondary box with the same sid as the user, because the database is in read-only mode sp_change_users_login won;t work you need to create the login with a matching sid to that user thus removing the need to run sp_change_users_login.
Gethyn Elliswww.gethynellis.com
July 6, 2009 at 5:45 am
winslet (7/6/2009)
Can't create a Login on the secondary server right ?as the database will be in ReadOnly mode.... It will be like a dummy login, though its same in DB user role....
coming to Primary server we have a same login for that DB , but not getting shipped to secondary...
any other help pls...
What kind of authentication do you use? I mean SQL or Windows?
July 6, 2009 at 8:02 am
Are you using SQL 2000? If so, I once created a SQL Agent job that does the trick, but I have not tested it on SQL 2005.
It is made up of a few steps, including a a script checking whether any logins are missing on the secondary server, and two scripts creating the secondary login where necessary.
One for windows logins (using sp_grantlogin) and one for SQL logins (using sp_help_revlogin) so sid and password are preserved. The last one sets the default db using 'sp_defaultdb'.
It has been working fine for me for a few years...
Let me know if you want it posted in more detail
-- Willem
July 6, 2009 at 8:07 am
The SID shouldn't matter. You can map the login to the user.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 7, 2009 at 5:11 am
Yes,
I can see the DB user role in the secondary database... But cant create Login....
Ours is a mixed autheticated... SQL and WIN..
Any suggestions please...
Cheers,
- Win.
" Have a great day "
July 7, 2009 at 5:36 am
Are you using SQL 2000? If so, I once created a SQL Agent job that does the trick, but I have not tested it on SQL 2005.
It is made up of a few steps, including a a script checking whether any logins are missing on the secondary server, and two scripts creating the secondary login where necessary.
One for windows logins (using sp_grantlogin) and one for SQL logins (using sp_help_revlogin) so sid and password are preserved. The last one sets the default db using 'sp_defaultdb'.
It has been working fine for me for a few years...
Let me know if you want it posted in more detail
----------------------------------------------------------------------------------------------
Ours is SQL 2005 server but DB is compatible to 2000.
Please post the process so that i can check the same for my server as well.....
Thanks for the reply...
Cheers,
- Win.
" Have a great day "
July 7, 2009 at 6:59 am
My script (not yet tested on 2005!) assumes the secondary server is logshipping only, so sids can be kept identical
If not, and sids differ, you should map the db-user sid to the server-login sid.
The setup consists of two batch files, that add Windows logins and SQL logins to SQL, respectively, using an input file and an output file created by the script.
Make sure the 'FOR ... DO ' commands do not linewrap in the batch file..
The script for sp_help_revlogin (2005) can be found here: http://support.microsoft.com/kb/918992
[font="Courier New"]================ STEP 1 - Add Windows logins =================
Run batch file below that adds Windows logins to secondary SQL server.
Contents of batch file:
--------------------------------------------------------------------------------
rem STEP 1 == put missing logins (name + default database) in output file
osql -E -S -n -o MissingWindowsLogins.txt -i ListMissingWindowsLogins.sql
rem STEP 2 == generate new login(s) from output file
FOR /F "tokens=1 delims=," %%a in (MissingWindowsLogins.txt) do osql -E
-S -w250 -Q "exec sp_grantlogin '%%a'"
> grant_windowslogin.log
rem STEP 3 == set default database
FOR /F "tokens=1,2 delims=," %%a in (MissingWindowsLogins.txt) do osql -E
-S -w250 -Q "exec sp_defaultdb @loginame = '%%a',
@defdb = '%%b' " >> grant_windowslogin.log
rem Useless echo to prevent SQL Agent from mailing 'unsuccessful' in case no missing logins were found
echo Ready
--------------------------------------------------------------------------------
Text of input script "ListMissingWindowsLogins.sql":
--------------------------------------------------------------------------------
-- Retrieve name and default database of missing Windows logins
SET NOCOUNT ON
DECLARE list_logins CURSOR FOR
SELECT name,
dbname
FROM .master.dbo.syslogins
WHERE (isntuser = 1
OR isntgroup = 1)
AND name NOT IN (SELECT name FROM .master.dbo.syslogins)
DECLARE @name VARCHAR(100),
@db VARCHAR(100)
OPEN list_logins
FETCH NEXT FROM list_logins INTO @name, @db
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @name + ',' + @db
FETCH NEXT FROM list_logins INTO @name, @db
END
CLOSE list_logins
DEALLOCATE list_logins
GO
=============== STEP 2 - Add SQL logins ==================================
Run batch file below that adds SQL logins to secondary SQL server.
Contents of batch file:
--------------------------------------------------------------------------------
rem STEP 1 == put missing logins (name + default database) in output file
osql -E --S -n -o MissingSqlLogins.txt -i ListMissingSqlLogins.sql
rem STEP 2 == generate new login(s) from output file in step 1
FOR /F "tokens=1 delims=," %%a in (MissingSqlLogins.txt) do osql -E -S
-w250 -Q"exec sp_help_revlogin '%%a'" > revlogin_output_%%a.sql
rem STEP 3 == add new login from outputfile in step 2 (identical sid + password)
FOR /F "tokens=1 delims=," %%a in (MissingSqlLogins.txt) do osql -E
-S -w250
add_login_%%a.log
rem STEP 4 == set default database
FOR /F "tokens=1,2 delims=," %%a in (MissingSqlLogins.txt) do osql -E -S -w250 -e -Q"exec sp_defaultdb @loginame = '%%a',
@defdb = '%%b' " >> add_login_%%a.log
rem STEP 5 == delete outputfile(s)
IF EXIST revlogin_output_*.sql (del /F revlogin_output_*.sql)
--------------------------------------------------------------------------------
Text of input script "ListMissingSqlLogins.sql":
--------------------------------------------------------------------------------
-- Retrieve name and default database of missing SQL logins
SET NOCOUNT ON
DECLARE list_logins CURSOR FOR
SELECT name,
dbname
FROM .master.dbo.syslogins
WHERE isntuser = 0
AND isntgroup = 0
AND name NOT IN (SELECT name FROM .master.dbo.syslogins)
DECLARE @name VARCHAR(100),
@db VARCHAR(100)
OPEN list_logins
FETCH NEXT FROM list_logins INTO @name, @db
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @name + ',' + @db
FETCH NEXT FROM list_logins INTO @name, @db
END
CLOSE list_logins
DEALLOCATE list_logins
GO
--------------------------------------------------------------------------------[/font]
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply