Today in the morning I was contacted by my fellow DBA to look into an issue with a linked Server he setup from SQL Server to Oracle. He did setup a linked server from SQL Server 2005 to an Oracle database and tested successfully(From SSMS). Well, everything appeared to be okay, but the app teams are unable to use this Linked server.
The DBA tried expanding catalogs->default and Oops…he is receiving this weird message.
This looks like missing permissions issue at Oracle end, but wait……..he was able to test the linked server successfully! So what did he miss?
The answer is “Allow Inprocess” for OraOLEDB.Oracle provider. Once I asked him to check that box, Voilaaaa….it works 🙂
For more information on the properties of these providers, please see http://technet.microsoft.com/en-US/library/ms188095%28v=sql.90%29.aspx
Happy Friday all!
Property Owner is Not Available for Database
One of the Most annoying error Messages(which really irritates a novice DBA) when you try to look at your Database Properties/Options from SQL Server Management Studio(SSMS). You’ll get something similar to the below screenshot when you right click on the database and choose properties.
Okay…So Let’s run our legacy sp_helpdb and see the result set.
Well, it says the owner of ‘Adventureworks’ database is “UNKNOWN”…Umm Interesting!! So what can we do to fix this issue?
Resolution: Assigning an owner for this database from T-SQL. Typically i prefer to go with ‘sa’ as Database Owner for all the databases. We can do this in either way mentioned below
Legacy(SQL 2000) way:
New way starting SQL Server 2005:
ALTER AUTHORIZATION ON DATABASE::Adventureworks TO sa.
So what is the root cause for this strange behavior of database from SSMS? What happened exactly?? Well, consider a scenario where your coworker has created a database and he/she(Windows Account) is the current owner of the database(Just let’s assume). For some reason he/she left the organization and his/her Windows Account will be removed from the Active Directory by AD Administrator(and as a DBA you’ll drop his/her login from SQL Server). In other words his/her windows login is no longer a valid account.
I’ve created a windows user account called “baddba” and I’ve added him as a login in my SQL Server and made him the Database Owner for Adventureworks database as shown below.
Let’s me simulate a scenario where he/she leaves our company and our AD Admin removes his/her account and another DBA drops the login from SQL Server.(Actually i did both the steps behind the scenes).
Now let’s try to select database properties from SSMS….
Conclusion: Try to avoid having individual Login as a database Owner. Definitely a very bad practice as justified in this post! Instead, make it ‘sa’ on all the databases. You can make use of Powershell to achieve this if you are really supporting tons and tons of databases and have no patience to deal with individual database(s) as i do:)