Exporting Login script to a text file
-
Hi I am very new to this!!
I'm using the following stored procedure to generate list of users.
http://www.databasejournal.com/img/sp_help_revlogin.sql
how I can get the script generated from this stored procedure to be exported to a text file using dts?
I wan't to be able to create a text file of users on regular basis and apply them to the secondary server.
-
How about osql?
Type OSQL /?
-
[Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error
-
You'll need Database and Text file (Destination) connections. Then select both connections and click the Transform Data button to create a Transform Data Task.
Double click the Transform Data Task to access it's properties. On the first tab use the SQL Query option and put in
EXEC sp_help_revlogin.sql
as the query text. When you go to the Destination tab you'll be able to define the fields in the file.
Lastly click on the transformations tab to create the field mapping.
Click OK
Then you should be able to run the package to create the text file.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
Hi Phil the stored procedure that I am using does not return any rows?
The print command is being used.
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
-
Ok there is two things you'll need to do.
1) Replace your sp_help_revlogin procedure with this one,
quote:
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULLDROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL
AS
SET NOCOUNT ON
DECLARE @name sysname
DECLARE @xstatus int
DECLARE @binpwd varbinary (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)
CREATE TABLE #Tmp (
Textout varchar(2000)
)
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
INSERT INTO #Tmp VALUES ( @tmpstr )
SET @tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
INSERT INTO #Tmp VALUES ( @tmpstr )
INSERT INTO #Tmp VALUES ( '' )
INSERT INTO #Tmp VALUES ( 'DECLARE @pwd sysname' )
WHILE (@@fetch_status > -1)
BEGIN
IF (@@fetch_status > -2)
BEGIN
INSERT INTO #Tmp VALUES ( '' )
SET @tmpstr = '-- Login: ' + @name
INSERT INTO #Tmp VALUES ( @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 + ''''
INSERT INTO #Tmp VALUES ( @tmpstr )
END
ELSE BEGIN -- NT login has access
SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
INSERT INTO #Tmp VALUES ( @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 + ')'
INSERT INTO #Tmp VALUES ( @tmpstr )
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', NULL, @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'''
INSERT INTO #Tmp VALUES ( @tmpstr )
END
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
SELECT Textout FROM #tmp
DROP TABLE #Tmp
RETURN 0
GO
This will store the output in a temp table and return the rowset.
2) Because DTS won't recognise the column names from a stored procedure, you'll need to fool it. This is done by putting something like SELECT @@Version as Textout in the source tab. This will allow you to do the field mapping. Once you've done the field mapping go into Disconnected Edit ( right-click anywhere on the package and select "Disconnected Edit"). This will bring up all the properties avaiable within the pacakage. In the left-hand pane, navigate through the tasks and find the Data Pump task ( it'll be called something like DTSTask_DTSDataPumpTask_1 ) Select the task name, this will refresh the properties list on the right. Scroll to the bottom of the list till you see the SourceSQLStatement property. Double-click it to edit it. Replace the SELECT @@VERSION etc... with EXEC sp_help_revlogin.
Once thats done the package will run successfully. NOTE: you won't be able to modify, or view, the Transform Data Task without going into Disconnected Edit. If you do, it'll complain about invalid transformations and you'll have to start all over again.
Let me know if you have any problems.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
Edited by - phillcart on 08/27/2003 02:20:17 AM
--------------------
Colt 45 - the original point and click interface -
Howdy Phil,
I had problems though with the stored procedure. "Invalid pointer".
So I have decided to do this
Run stored procedure sp_help_revlogin
package selects * from #tmp and
transfers to text file.
drop #tmp table
Thanks again for your help
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply