Archive for the ‘SQL’ Category

sp_help_revlogin cleaned up and revised with roles

July 8th, 2009 No comments

When working with SQL Server there are often times where you will need to script your logins over to another server such as when you have database mirroring or log shipping configured for certain databases.   This is such a common thing that Microsoft provides in-depth instructions on how to do this as well as the T-SQL code required to do it, which you can read more about here:  The script they provide is missing a few things so I took a little time to clean up the code, revise some of it, and add in a few of the missing pieces.

The three things that are overlooked in the Microsoft article that I have included are default language and default database (being mandatory) for logins as well as the scripting of roles.  The lack of the default language is pretty minor, but it’s something that could be important to people so I am including it in my script.  The lack of default databases being an option in the Microsoft script is not a big deal because it assumes you are scripting everything, but if you are only concerned with logins that are specific to a certain database you will get errors if you haven’t created that database on the target.  The lack of server roles is another important one that I have needed in previous configurations so it is also included here.  The resulting script is pretty long so you can check out the source of the revised procedure after the jump, which includes the sp_hexadecimal script from the Microsoft article.

Additionally, something just as important as the revlogin script itself is the question of “Well now that I have this script, how do I schedule this to happen automatically for me instead of just generating more code that I have to execute on the target server?” If you are asking that question then you might want to take a look at the next block of code. In order to automate sp_help_revlogin there are a few options, but the one that I prefer over all else is using osql and a SQL Agent job with a single T-SQL step because it is very simple to implement in most server configurations. The following script is relatively straight forward in that you only have one variable, the target server, to worry about. The only pre-requisites being that xp_cmdshell is enabled and that the Windows account that the job is being executed under has sysadmin rights to both servers. This script could easily be turned into a stored procedure but for the sake of brevity I have included it in its shortest form.

Disclaimer: Use these scripts at your own risk!!

-- Declare and set the target server...
SET @TargetServer = N'DB1.DOMAIN.LOCAL'

-- Define a temporary file to store the script output
DECLARE @guidfile VARCHAR(160)
SET @guidfile = '%TEMP%\' + CONVERT(SYSNAME, NEWID()) + '.txt'

-- execute sp_help_revlogin_roles and save the output to the temp file
SET @cmd = 'osql -E -n -h-1 -d master -w 8000 -Q "exec sp_help_revlogin_roles" -o "' + @guidfile + '"'
EXEC master.dbo.xp_cmdshell @cmd

-- execute the temp file on the target server
SET @cmd = 'osql -E -S ' + @TargetServer + ' -d master -w 8000 -i "' + @guidfile + '"'
EXEC master.dbo.xp_cmdshell @cmd

-- delete the temp file
SET @cmd = 'del "' + @guidfile + '"'
EXEC master.dbo.xp_cmdshell @cmd

Click here to view the code for sp_help_revlogin_roles

Categories: SQL Tags: , ,

The problem with SQL Injection…

March 23rd, 2009 No comments

Oh yes, Little Bobby Tables we call him.

I know it’s an old comic, but XKCD really hit the nail on the head with this one.  It is something that I deal with on a regular basis and I think there are a lot of people out there who still don’t really understand the concept of SQL Injection, how it works, and how to get away from it.  While working with a client who fell victim to a SQL Injection attack on their website, I wrote some information for them to take and use as reference for what SQL Injection is, how to prevent it, and what to do about it when it happens.  I have generalized that info and hopefully it can help some others.
Read more…

Categories: SQL Tags: , , , , ,