When a user that doesn’t have sysadmin rights creates objects by default they will be created in the schema that is the users default schema. Now the catch to this is that if you grant the user rights into the database via a domain group that domain group then the user doesn’t have a default schema.
So, now how do you fix this? Unfortunately the only fix to this is to grant the users Windows login as a separate login, then grant this login rights into the database. You can then grant the user which is mapped directly to the users Windows login a default schema of dbo.
Because of this the user should specify the schema when creating objects.
The downside to this is that they won’t be able to use the object editor to create new tables. All new tables will need to be created in T/SQL directly.
And as a bonus one lucky attendee will win SQL Server expert Kevin Kline’s new essential reference book, SQL in a Nutshell.
See you on the webcast.
When using SQL Server 2000 and the user doesn’t have sysadmin rights, and their login isn’t mapped to the dbo user within the database all objects created will be, by default created under the user schema.
This is the normal behavior of SQL Server 2000. In order to allow users who are not members of the sysadmin fixed server role to create objects under the dbo schema by default you have to map their login to the dbo user, even if they are a member of the dbo fixed database role.
To work around this, in the T/SQL code specify the owner of the database object. If your developer is using Enterprise Manager to create the new tables before saving the table, click on the properties button in the upper left hand corner of the Enterprise Manager window (second from the left). Then change the owner drop down from their username to dbo. There is no way to default this setting to dbo so it will need to be changed for each new table being created.
When using VMs and Windows 2008 you have the ability to easily grow the C drive on your servers. This makes it much easier to assign only the space needed to the C drive of your servers saving you a ton of space.
However I recently had a few machines say that the disk was extanded in Computer Manager and diskpart, but Windows Explorer (aka My Computer) showed the old amount. And growing the disk a second time doesn’t help. Shrink the partition (either in diskpart or Computer Management) then extend it again seamed to fix it for me.
Today a new article that I wrote was posted up on SearchSQLServer.com.
The article is about setting up SQL Service Broker to use Certificates to secure the traffic between the instances.
While working on moving my companies data center from Texas to California we ran across an issue with IIS 7 on Windows 2008 because we were using the ASPState database to handle session state.
If you’re like me you probably upgrade when you have the chance. If you’re like my company you don’t run just one website per server. In our case this cluster of web servers runs 6 different sites, and the session state for one of them keeps resetting just about every page view.
Apparently one of the values which is used to create the session id is the ID number of the website in IIS. Up through IIS 6 this number was a hash of the name of the site, so as long as the sites all had the same name you were set. In IIS 7 this ID number is now just a running number based on the order you create the websites in. So if you create the websites in a different order on one or more machines then your ASPState information will keep expiring since the hash values don’t match correctly.
The good news is that you can change the ID number by clicking the Advanced Settings link on the right hand side.
When installing SQL Server 2008 Enterprise onto my new SQL Cluster I downloaded the media from Microsoft and dropped it onto a network share and installed it. The first node went perfectly, the second node, not so much. When I went to install on the second node I would get to the screen which asked for the Key (it is a volume license install so the key was already there) and when I clicked next it told me that the key was invalid and the installer exited. Continued »
So you are probably a lot like me, you were a teen who liked to play with computers and you managed to end up in IT. This is awesome you figured, I play with computers and someone actually pays me for this.
During the 90’s life was good, there wasn’t any regulations to deal with, you followed best practices to the extent that the company you worked for could afford them. Change control usually consisted of sending out an email saying “Hey, were going to change a bunch of stuff, nothing should break.”. Today however things are different, very different depending on how large the company is that you work for, and if your company is public or private.
Today I work for a private company, so we are able to run things much like the “good old days”, but most are not so lucky. Change control processes are cumbersome at best, and the number of legal compliance issues that we have to not only be aware of the existence of, but actually understand is quite daunting. You’ve got everyone’s favorite SOX which says that lots of stuff needs to be controlled and duties must be separated, but doesn’t give any sort of guidelines as to how to do this, or what duties should be done by who. If you take credit cards over the web, or process credit cards over the web then you’ve got PCI (which I’m dealing with now on our shopping cart server). If you work for a company which stored medical records then got help you when it comes to HIPAA. For those that aren’t aware of HIPAA part of it basically says that every lookup to medical records has to be logged. Within an application that’s easy. Windows SQL Server 2008 that’s easy, but what about the legacy SQL 2000 medical application? It provides no guidance other than to say “do it”.
I remember that when Windows 2003 SP 1 was released (it may have been SP 2) there was a thread on a forum somewhere (probably tek-tips.com) where we were discussing SP1 and HIPAA. Somewhere in HIPAA is says that you have to keep your systems patched. Somewhere else it also says that systems which stored medical information on there cannot report data back to a vendor. Well SP1 introduced code that would allow a sysadmin to have the server report usage and error data back to Microsoft. So which part of HIPAA should you violate?
On top of all the federal regulations, states are now passing data encryption laws which have to be dealt with. Here in California we’ve had data encryption regulations in place since 2003 or so. At several companies that I’ve worked at the IT managers didn’t know anything about the law and what it meant. The law here in California is so vague that it is almost meaningless. It says (in laymen terms) that if your data is breached, and the data isn’t encrypted then you have to tell your customers either directly or via the media. But it doesn’t define encryption, or how strong that encryption has to be. It at least defines what data items it includes (name, address, username, password, social security number, etc) but if you take the law at face value doing a simple character replacement is sufficient to comply with the law. While this complies with the letter of the law it obviously doesn’t comply with the spirit of the law, but the letter of the law is what matters in court.
Having to keep track of all these laws which apply to us is mind boggling at best, and impossible at worst. And reading the laws is amazingly painful. The California law I sited above, which I’ve read several times, still confuses me to no end; and I’ve reviewed it with the legal team at one company already due to a data breach. And consider that there are data encryption laws in several states, all of which you have to comply with if you have customers in that state, or if you do business in that state. I have no idea which states have these laws, or even how many states have these laws. Even if I did, I’d then need to find the overlaps and the exceptions, then figure out how to build our database to meet these laws. Beyond that I’d have to anticipate the future laws that could be coming in the other states and account for those potential laws at the same time.
At this point handling the database design is just getting more complex.
Now the new Transparent Data Encryption is great for handling data at rest. It keeps your backups all encrypted and save. But what happens when the bad guy breaks in and swipes the data by logging into the database. Yea the data is encrypted on disk, so technically we are covered, but the data is still out there and usable because the bad guy was able to login as a database user with select access to the tables and dump the data to his system via a SELECT statement. What has to happen now?
I don’t know about you, but I got into this field so that I wouldn’t have to worry about stuff like this. I guess those times are over with.
This rant is now complete. See what happens when I get on a plane at 6:30am and the nice lady starts poring coffee down my throat for the entire flight.
There is a VMware setting which you can set which should give you a small but noticeable performance improvement in SQL Server performance when that SQL Server is running within a VMware Virtual Machine.
SoCal Code Camp is coming up in a couple of weeks now on June 27th and 28th. I’m presenting 3 sessions solo, and I’m one of a few people doing an IT Pro Pannel to allow Developers to grill some IT Pros to see why we do things the way we do.
I’ll be posting the slide decks and sample code shortly before Code Camp.