July 27, 2015 at 8:34 am
Hi All,
I have the below query which gives me statement to apply the login name, SID and PWD and that I statement I can apply to Dr and secondary server. Incase of failover there will be no connection issue.
My query is
SET NOCOUNT ON
SELECT 'EXEC sp_addlogin @loginame = ''' + loginname + ''''
,', @defdb = ''' + dbname + ''''
,', @deflanguage = ''' + language + ''''
,', @encryptopt = ''skip_encryption'''
,', @passwd ='
, cast(password AS varbinary(256))
,', @sid ='
, sid
FROM syslogins
WHERE name NOT IN ('sa')
AND isntname = 0
This query output I wanted in a sqlfile so that I can schedule a batch file or sqlcmd and apply the login detail from the new one inserted in the primary. Please advice.
"More Green More Oxygen !! Plant a tree today"
July 27, 2015 at 9:50 am
I used google and found this link:
http://www.sqlteam.com/article/exporting-data-programatically-with-bcp-and-xp_cmdshell
And put this together:
-- ENABLE XP_CMDSHELL
SP_CONFIGURE 'XP_CMDSHELL', 1
RECONFIGURE WITH OVERRIDE
GO
SET NOCOUNT ON;
DECLARE @FILENAME VARCHAR(50)
, @BCPCOMMAND VARCHAR(2000)
, @SERVER_NAME SYSNAME = @@SERVERNAME
-- FILE PATH WHERE YOUR SQL SCRIPT WILL BE STORED
SET @FILENAME ='C:\XXX\SQL_LOGINS_['+@SERVER_NAME+']_'+CONVERT(VARCHAR, GETDATE(), 112)+'.SQL'
-- QUERY FOR LOG DETAILS
SELECT 'EXEC SP_ADDLOGIN @LOGINAME = ''' + LOGINNAME + '''' AS LOGINAME
,', @DEFDB = ''' + DBNAME + '''' AS DEFDB
,', @DEFLANGUAGE = ''' + LANGUAGE + '''' AS DEFLANGUAGE
,', @ENCRYPTOPT = ''SKIP_ENCRYPTION''' AS ENCRYPTOPT
,', @PASSWD =' AS PASSWD
, CAST(PASSWORD AS VARBINARY(256)) AS PASSWORD
,', @sid =' AS SID_HEAD
, SID
INTO TEMP
FROM SYSLOGINS
WHERE NAME NOT IN ('SA')
AND ISNTNAME = 0
-- BUILD BCP COMMAND
SET @BCPCOMMAND = 'BCP "SELECT * FROM TEMP" QUERYOUT "'
SET @BCPCOMMAND = @BCPCOMMAND + @FILENAME + '" -c -T' PRINT @BCPCOMMAND
-- EXECUTE BCP COMMAND
EXEC MASTER..XP_CMDSHELL @BCPCOMMAND
-- CLEAN UP
DROP TABLE TEMP
GO
-- DISABLE XP_CMDSHELL
SP_CONFIGURE 'XP_CMDSHELL', 0
RECONFIGURE WITH OVERRIDE
GO
August 12, 2015 at 11:46 pm
The paswd being VARBINARY is not getting written to the output sql file. Could you please help here.
Or is it possible to copy the below statement to be one single command and can be apply on secondary server. This will create the login from primary to secondary automatically.
SELECT 'EXEC SP_ADDLOGIN @LOGINAME = ''' + LOGINNAME + '''' AS LOGINAME
,', @DEFDB = ''' + DBNAME + '''' AS DEFDB
,', @DEFLANGUAGE = ''' + LANGUAGE + '''' AS DEFLANGUAGE
,', @ENCRYPTOPT = ''SKIP_ENCRYPTION''' AS ENCRYPTOPT
,', @PASSWD =' AS PASSWD
, CAST(PASSWORD AS VARBINARY(256)) AS PASSWORD
,', @sid =' AS SID_HEAD
, SID
INTO TEMP1
FROM SYSLOGINS
WHERE NAME IN ('Test')
AND ISNTNAME = 0
"More Green More Oxygen !! Plant a tree today"
August 13, 2015 at 10:43 am
Please provide your input.
I wanted the output of the query to applied in another server.
"More Green More Oxygen !! Plant a tree today"
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply