On occasion, I have encountered folders or files getting flagged with incorrect attributes. Some of these are easy to address, such as read only. The more problematic changes tend to be when something gets flagged as system.
Fortunately, powershell offers a very concise and easy way to reset the attributes of a folder or file back to their default values.
1 2 3 | $path = 'F:\stuff' $dir = get-item $path -force $dir.attributes = 'Normal' |
This script will get an item that may otherwise be inaccessible and reset its attributes back to whatever is normal for that item type. Just replace the value for $path with the path to the item you are concerned with.
Script Disclaimer, for people who need to be told this sort of thing:
Never trust any script, including those that you find here, until you understand exactly what it does and how it will act on your systems. Always check the script on a test system or Virtual Machine, not a production system. Yes, there are always multiple ways to do things, and this script may not work in every situation, for everything. It’s just a script, people. All scripts on this site are performed by a professional stunt driver on a closed course. Your mileage may vary. Void where prohibited. Offer good for a limited time only. Keep out of reach of small children. Do not operate heavy machinery while using this script. If you experience blurry vision, indigestion or diarrhea during the operation of this script, see a physician immediately.
Credit for this fantastic disclaimer goes to Buck Woody (Blog | Twitter).
Going through your databases to find and clean up users with no associated server logins can be a pain. It is fairly easy to find scripts that will go out and find those users, and even some will drop those users automatically. Unfortunately, sometimes it is not so simple to just drop a user from a database because they own one or more schema.
To drop these users from a database the schema owned by that user must be altered to remove the dependency.
Thus I set to work to come up with a powershell script to identify users with no associated login, the schema that need to be altered to enable the dropping of those users and create a document logging the changes to be made.
Here is the script I came up with:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 | $servername = 'localhost' #change to the name of the server you are interested in $outputfile = 'C:\localhost modifications.csv' $targetowner = 'dbo' [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null $s = new-object ("Microsoft.SqlServer.Management.SMO.Server") $servername #Uncomment this section if you want to use SQL Authentication #$s.connectioncontext.loginsecure = $false $cred = get-credential #$s.connectioncontext.set_Login($($cred.username.replace("\",""))) #$s.connectioncontext.set_securepassword($cred.password) #> $dbs = $s.databases | ?{-not $_.IsSystemObject} #explicitly declare the collection variables as arrays [Microsoft.SqlServer.Management.Smo.User[]]$users = $Null [Microsoft.SqlServer.Management.Smo.Schema[]]$schemas = $Null [PSObject[]]$output = $Null #Get the users to drop and all schema owned by those users $dbs | %{ $db = $_ [Microsoft.SqlServer.Management.Smo.User[]]$dbusers = $db.users | ?{$_.Login -eq '' -and -not $_.IsSystemObject} $users += $dbusers [string[]]$OrphanSchema = $Null If ($dbusers.count -gt 0) { $dbusers | %{$OrphanSchema += $_.EnumOwnedObjects()} $schemas += $db.Schemas | ?{$OrphanSchema -contains $_.urn} } } If ($schemas.count -gt 0) { $output += $schemas | SELECT @{Name='Database';Expression={$_.Parent}}, Name, @{Name='Type';Expression={'Schema'}}, @{Name='Action';Expression={"Change owner from $($_.Owner) to $targetowner"}} } If ($Users.count -gt 0) { $output += $Users | SELECT @{Name='Database';Expression={$_.Parent}}, Name, @{Name='Type';Expression={'User'}}, @{Name='Action';Expression={"Drop"}} } If ($output -eq $Null) { $output = '' } $output | export-csv $outputfile -Force -notype #uncomment these lines when you are ready to perform the modifications #$schemas | %{$_.owner = $targetowner; $_.Alter()} #$users | %{$_.Drop()} |
Initially, this script should be run with the last two lines commented out to generate a csv file of all the schema the script will change and all of the users it will drop. Once you feel confident that this script will do exactly what you want it to, these lines can be uncommented to execute the changes.
Script Disclaimer, for people who need to be told this sort of thing:
Never trust any script, including those that you find here, until you understand exactly what it does and how it will act on your systems. Always check the script on a test system or Virtual Machine, not a production system. Yes, there are always multiple ways to do things, and this script may not work in every situation, for everything. It’s just a script, people. All scripts on this site are performed by a professional stunt driver on a closed course. Your mileage may vary. Void where prohibited. Offer good for a limited time only. Keep out of reach of small children. Do not operate heavy machinery while using this script. If you experience blurry vision, indigestion or diarrhea during the operation of this script, see a physician immediately.
Credit for this fantastic disclaimer goes to Buck Woody (Blog | Twitter).
Day 1 of 24 hours of PASS is off to a fantastic start and I have already learned about a number of things that I did realize powershell could do, but really am not surprised by.
Much like day 1, day 2 looks to have a fantastic set of sessions that makes me wish I had time for them all. Thus, I have to be judicious about what will be most applicable to me at this time.
Statistics: How to Prove Everything but the Truth: Without good statistics, it is unlikely that the query optimizer will pick a good execution plan. Knowing about how statistics work will be invaluable in making sure that the query optimizer has up to date and useful information when it is doing its job.
Preparing for Your Next Job: I may have just started a new job recently and have no intention going anywhere anytime soon, but professional development is always useful.
Storage for the DBA: I want to make sure I can give good advice when configuring storage for the database servers that I am going to support and this seems like the right place to get that information. Plus I have seen Denny Cherry (blog | @mrdenny) present once before and he was quite entertaining, so I expect a good show this time too.
T-SQL Brush-Up: The Best Things You Forgot You Knew: It is so easy to forget about tools that I don’t use often. Refreshers are always useful and I may even encounter toolsI have never used before.
Relational Database Design for Utter Newbies: By getting involved in the design process for new databases, DBAs can make their job down the line much easier. Knowing how to design databases well makes it much easier for me to be successful in such endeavors.
Top 10 Design Mistakes: Everyone makes mistakes more often than they want to. Reminders of mistakes that I make when I should know better decreases the frequency at which I will make such mistakes.
Understanding SARGability: I just finished tackling an ugly problem where SARGability was broken but it wasn’t clear why. Knowing more about how to write where and join clauses that allow the query optimizer to do its job well will reduce these problems in the future.
Unfortunately I was unable to attend most of the original 24 hours of PASS. Setting time aside to watch the sessions after the fact proved to be extremely challenging, so this time I am making a distinct point of attending the sessions when they first occur.
Without further ado, here are the sessions I plan to attend tomorrow.
Gather SQL Server Performance Data with PowerShell: Since I first tried out powershell, I have been hooked. It has a pretty steep learning curve, however, so figuring out how to script new things tends to take longer than I would like. As such, collecting as many scripts as possible to adapt to my environment is a fantastic method of smoothing out the learning curve.
Hardware 201: Selecting and Sizing Database Hardware for OLTP Performance: I could read blogs, white papers and more for days on hardware and not get bored, but information always sticks better for me when I am not just reading. Having more information about current and upcoming hardware options can help me give better advice when planning new servers or upgrades.
Isolation vs Concurrency: What Are the Choices?: Isolation and concurrency is a big issue at my company and understanding the implications of each isolation level decision and when to suggest deviations from the standard practices would be extremely valuable for me.
Identifying Costly Queries: I don’t believe it is possible to know enough about how to find and fix poor performance. The more tools available, the better and this session looks to be full of excellent tools.
How to Rock Your Presentations: I may not be presenting yet, but I have every intention of moving in that direction. Having a good basis of knowledge about how to present is important as I accumulate knowledge that is worth presenting.
TempDB Configuration and Management: The session description on its own sums up why I want to attend better than anything I could write.
