December 14, 2009 at 10:33 am
how do i get just the sql logins using sp_help_revlogin?
December 14, 2009 at 11:37 am
jsb1212 (12/14/2009)
how do i get just the sql logins using sp_help_revlogin?
sp_help_revlogin serves a specific purpose: scripting out the commands to create the logins, complete with the password. it has output that looks like this:
-- Login: bob
CREATE LOGIN [bob] WITH PASSWORD = 0x0100A6CE867643093B664A1A9DCDFAFFD0B10711A847D74EA971 HASHED, SID = 0x2DB5FBE4EB1AC748B1F9108F23F2424E, CHECK_POLICY = ON, CHECK_EXPIRATION = OFF
-- Login: jeff
CREATE LOGIN [jeff] WITH PASSWORD = 0x01005CA0B17148F93640D050F0B9131827E869105247752CF8C1 HASHED, SID = 0xEE55FE87C32A284F9C767554AE980865, CHECK_POLICY = ON, CHECK_EXPIRATION = OFF
-- Login: NT AUTHORITY\Authenticated Users
CREATE LOGIN [NT AUTHORITY\Authenticated Users] FROM WINDOWS
-- Login: webdev
CREATE LOGIN [webdev] WITH PASSWORD = 0x0100A0710DCC125B265143BEDA8F8E2CD5A22921B735AECF1245 HASHED, SID = 0x1E78DDE589D7854FA03C07F636987B75, CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
-
if you just need the names of the logins, you just want to hit one of the sys vliews like syslogins:
select name from syslogins
Lowell
December 14, 2009 at 11:52 am
Thank you, but how do i get just the sql logins in the output? i dont need the other logins in the output because i need to automate it
December 14, 2009 at 12:05 pm
i THINK i understood what you are after;
it's still looking in the same view i mentioned.
try this, and comment/uncomment the WHERE/AND statements to see the differences:
select name,* from syslogins
where isntgroup = 0
and isntuser = 0
and name not like '##%'
and name <> 'sa'
Lowell
December 14, 2009 at 12:35 pm
sorry for not being clear enough. i need to automate the sp_help_revlogin and get the output into a folder all through a job(s). That includes getting just sql logins as output into the folder. so this is the whole situation. Thank you once again
December 14, 2009 at 12:41 pm
BCP is what you would use to get a table or query's results into a file; you'll really need to read Books On Line for the syntax, as I rarely use it.
you'd use BCP twice, once for running master..sp_help_revlogin, and another for "select name,* from syslogins where isntgroup = 0 and isntuser = 0 and name not like '##%' and name <> 'sa'"
once you get the BCP syntax down, you can create a job that does it.
show us what you've got so far, so we can help you better; talking theory is one thing, helping you tweak an actual SQL statement is what we are best able to do.
Lowell
December 14, 2009 at 2:20 pm
This is what i was trying. sorry if im wrong. I wanted to use a cursor and add the stored procedures in between.
Declare cursor_fetchrevlogin cursor
For
select name, * from syslogins
where isntgroup=0
and isntuser=0
and sysadmin=0
and name <> 'sa'
Open cursor_fetchrevlogin
Fetch Next from cursor_fetchrevlogin
WHILE @@FETCH_STATUS = 0
BEGIN
-- The Stored procedures hexadecimal and revlogin
Fetch Next from cursor_fetchrevlogin
END
CLOSE cursor_fetchrevlogin
DEALLOCATE cursor_fetchrevlogin
December 15, 2009 at 2:43 am
I dont exactly get what you are trying. But if it is smthng like Scripting the output of sp_help_revlogin for specific logins and then storing it in a specifc place then it can be done by
On your job the script would look like
sp_help_revlogin 'user1'
sp_help_revlogin 'user2'
//u can also make it dynamic using cursor/while loop.
On the advanced tab in under edit of step of a job you have a option to store the result of a job to a file.
If you want to make your file name dynamic think of bit of smart usage
of xp_cmdshell/copy at the next step of your job..
Regards,
Raj
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply