This is the download page, where I’ve collected large scripts that aren’t practical to post in their entirety into a post, as well as other downloads. I’ve made an honest effort to test and verify them all, but bear in mind that I don’t have access to every type of advanced system setup and environment, so some of them may not work as planned on your particular server.
Utilities
These are scripts I’ve put a lot of work into, and/or that I frequently use myself.
sp_ctrl3
My take on building a better sp_help. Blog post, Github.
SQL Server calendar functions
I’ve built a collection of calendar functions to instantly generate calendar dimensions. Blog post, Github.
View active SQL Server sessions and locks
A heavy-weight interpretation of sp_whoisactive. Github.
sp_help_permissions
A script that compiles effective permissions across objects and principals. Blog post, Github.
Synchronizing logins between servers
A stored procedure to fetch logins and user-defined server roles from another server (like a primary AG replica) and sync them to the local instance. Blog post, Github, and how to do it with dbaTools instead.
Gists and other hacks
These are nice-to-have scripts and hacks that you may find useful. Typically built for a single use-case and not as thoroughly tested.
- Decrypting SQL Server objects. Blog post, Gist.
- Generate a list of recurring dates. Blog post, Github.
- Load an XML blob into one or more tables. Blog post, Gist.
- Visual representation of SQL Server Agent job schedules. Blog post, Gist.
- Script to identify potential primary key candidates. Blog post, Gist.
- A simple template parser for T-SQL: Blog post, Gist.
General disclaimer and copyright notice
These are scripts that I’ve written and made available to you under the condition that you take responsibility for any consequences that may arise from using them. I do not recommend running them directly in production-like environments without thorough testing.
Important:
I cannot assume any responsibility regarding the accuracy of any output information, performance impacts on your server, whether the code performs correctly in your environment or any other consequences of using these downloads. It’s free, so it’s your ass. If your jurisdiction does not allow for this kind of waiver/disclaimer, or if you do not accept these terms, you are NOT allowed to store, distribute or use this code in any manner.
Copyright/CC:
These documents are made available under the Creative Commons 4.0 (with attribution) license. In short, this allows you to freely use, modify and redistribute them without charge, though you have to quote the source (the URL of this blog, http://sqlsunday.com/, is fine).
Howdy! None of the links for the downloads worked in my experience. I would love to take a look at them and compare to queries I currently have. Always trying to learn from the experts – Thanks!
Strange. Let me check and get back to you!
I just checked, and they appear to be working for me. However, they all redirect to a Dropbox share, so maybe you’re behind a firewall that blocks access to Dropbox?
Probably the links are not allowed at John’s place of employment. Same thing happened to me when attempting to log in via my work computer.
Hi Daniel,
I’ve just tried your sp_ctrl3, very nice indeed!
I have one suggestion, which is to add “OPTION (MAXRECURSION 0);” to the CTE for indexes.
Without it I get the error “The maximum recursion 100 has been exhausted”, wich seems to be caused by our tables being heavily partitioned (often by day).
Thanks,
Michel
So glad you like it, and thanks for the feedback! Looks like you’ve got a pretty wide table with 100+ columns. 🙂
I’ve updated the script – let me know of that works for you.
Wow you are quick 😉
Ah yes it was the columns… 114 to be exact.
Thank you for the update!
Cheers,
Michel
Hi Daniel
I’m testing “Synchronizing logins from another server”
My collation is _CS_ and the script do not filter correct.
Fx. NT Service\% logins.
After adding UPPER to both sides it’s working
UPPER(sp.[name]) NOT LIKE UPPER(‘NT Service\%’)
Thanks
Jesper
Oh. Let me check on that.
Suggestions for “Synchronizing logins from another server”:
1. Filter out logins like ‘##%’ (or ‘##%##’) to remove processing of SQL internal certificate based logins.
2. Not a must (but I’ve changed the proc to do this for us): Dynamically create the Linked Server with a unique name with pass thru authority (I based name on timestamp) with the primary replica as the target….then when done with it, drop it (all in the proc). I don’t like linked servers in general and certainly don’t like them cluttering up instances if I can help it. Of course. the user executing the proc will need permission to manipuliate linkled servers…but in our context, they will in every case.
3. I would not put this in your script but I did for us: If Allow Drop is specified, the Print Only is turned on automatically. We have non-AG related logins in certain circumstances so we want to inspect all drops beforehand. So two runs…one to sync, and one to inspect and manually run drops. Not a heavy price to pay for our piece of mind.
Glad you put it together. Very nice. We used a free SQLSkills plugin but now use SSMS2106 all the time and and not compatible…and it didnt work great anyway.
Hey, thanks, so happy you like it. I wrote it for an environment with a large/volatile number of logins and relatively few machines. Having all the logins synced at any given time without a need for manual actions was key there. Anyway, good thing you adapted it for your needs.
If you wouldn’t mind, I’d be glad to update the code with your changes. If you want, you could e-mail me the modified proc at [email protected].
This is a fantastic script that I have used often! I came across two situations where the ID was not exactly replicated and I’m wondering if you could help or have seen this before.
1) When an ID on the primary has MUST_CHANGE turned on it does not get replicated when the ID is created on the secondary. I have found that I cannot include MUST_CHANGE with the HASHED command.
2) The GRANT IMPERSONATE does not get replicated when an ID gets created on the secondary.
Ex. GRANT IMPERSONATE ON LOGIN::[Login1] TO [Login2] AS [Login1];
If you have any suggestions for these scenarios, it would be of great help.
Hi Daniel
Super sync script.
I was running the sync sp and forgot to add the @primary_replica. I don’t have a linked server with the name of the AG server, I use an Alias for this.
I got a lot of errors, but the script runs and drop everything incl. my sysadmin rights on the secondary 🙂
A check like this is needed 🙂
IF not exists(select 1 from sys.servers where name = @primary_replica) BEGIN;
PRINT N’The primary_replica: ‘ + @primary_replica + ‘ is not registered as a Linked Server. No changes will be made.’;
RETURN;
END;
OUCH.
Thanks, I’ve added a check for this!
Hi again
Forgot to say, this is a super sync script.
If the default database isn’t at the secondary the user create fails. I fixed with this line, both at the create and alter login.
‘DEFAULT_DATABASE=[‘+ISNULL((SELECT name from master.sys.databases where name=p.default_database_name),’master’)+N’]’
Thanks Daniel , very helpful for me,
2 questions
-1 what is steps required if I want to check which one of two node is the primary depend on this the job will start?
2- the command from “SQLADIMS”
If the default database isn’t at the secondary the user create fails. I fixed with this line, both at the create and alter login.
‘DEFAULT_DATABASE=[‘+ISNULL((SELECT name from master.sys.databases where name=p.default_database_name),’master’)+N’]’
Can you please added to the script to be without an alert
thanks again
Hi!
1. The script checks the DMV sys.dm_hadr_availability_group_states for the name of the primary replica of the availability group. If the primary replica is equal to @@SERVERNAME, then the job is running on the primary.
2. Thanks for the input – I’ll try to remember to add it when I get around to revising the script. 🙂
Thanks a lot for your quick response, regarding the first point I mean if failover happen then the primary will be secondary , and the query should check which node the primary one then it will start,
thanks a lot again
Yeah. When the AG fails over, the name of the primary changes in the DMV, so you should be ok.
Have a great day!
Sorry Daniel , I got the below errors:
A HASHED password cannot be set for a login that has CHECK_POLICY turned on.
The server principal ‘##MS_PolicyEventProcessingLogin##’ already exists.
I have the same issue. Any resolution?
above error get after running the script , the other sql logins Synchronized successfully . can you help for that please.
Hello Daniel,
In couple of my environment we have Log shipping implemented. And as a new requirement we want to sync logins to the secondary server.
Would be great help if you would provide an solution.
Thanks,
Rajat
Hi, have you looked at my login sync script?
Hi Daniel
indeed it is a good script.
logins are getting sync between the replicas, but server level roles are not coming.
any help greatly appreciated
Is there a way to Report Only differences (just as a check to make sure everything is synchronized)?
I have a few sql users that seem to have different passwords between the nodes, I would like to run a report that compares the information between the nodes just to tell me if everything is correct.
Hi Daniel,
Thank you very much for the sp_ctrl3 script. It’s making my life a lot easier for creating new tables and finding references in our datawarehouse.
In the script you mention that putting quotes around the search parameter is only neccessary for older versions of SSMS. We are using version 18.6 but i still get an error every when searching for objects which i put a schema in front of. I tried searching for the setting which causes this behavior but could not find anything.
Do you know how i could remedy this situation? I have version 2018-10-24 of your script installed.
Regards,
Remko
Strange. I haven’t encountered that type of problem myself, so I wouldn’t know where to start looking. Maybe it’s related to the connection flags, or something else? If you find something, I’d be happy to fix it in the procedure, or you could create a pull request on the Github repository.
Daniel
Is there a way to automate the login synchronization process between the replicas without manually executing the script? Do you have any scripts or processes for such automation?
Check out contained availability groups on SQL Server 2022. Rolling this type of automation yourself sounds like a maintenance nightmare, to be honest. Why not just schedule a job?