Archive for July, 2009

Enumerate Distribution Groups Script Updated…

July 17th, 2009 4 comments

A reader sent in a question asking how to enumerate groups that have spaces in them and this lead me to realize I didn’t follow the specifications for valid distinguished names as well as I thought I had.  If you take a look at RFC 2253 and the Microsoft page that defines security group names, you will see what the allowed characters are for these names.  At this point the validation is a lot better, but it still isn’t perfect.

If you encounter a situation where you need to enumerate members of a group that my validation does not allow, you can scroll down to line 271 in the script and change the $rx variable to “.+” to make it simple which will allow you to pass any characters.  If you pass invalid characters you will get some funny errors happening, but it should work.  You may have to use quotes around the name if you are looking to use spaces or other allowed special characters.

Taking another look at the code, I also found a small bug that was causing the display of notes associated with a group to print out an error about null strings.  This should be fixed now.  If anyone notices any other problems, feel free to comment and let me know and I will try to fix it or add in the change as soon as possible.  I’ll be posting another update soon that goes the other direction of this script, one that enumerates group membership for a specific user.

Thanks again to Darren from Brisbane(?), Australia for pointing this out.

Here is the updated script download link: Download enumerate_groups.ps1Download enumerate_groups.ps1

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: , ,