How to determine MSSQL version and edition?

How to determine MS SQL version and edition?

1. Go to Open Query Analyzer 
2. Paste sql script below
   
   SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
3. Click on Execute button

There you can see the version and edition for Microsoft SQL server installed in your computer.

Database diagram support objects cannot be installed because this database does not have a valid owner.

I have restore a database from MSSQL 2000 to MSSQL 2005. When i try to open the database diagram, error message below pop up:

Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.

ALTER AUTHORIZATION ON DATABASE: Database_name TO valid_login

I use SSMS to solve my problem.
In SQL Server Management Studio do the following:

1. Right Click on your database, choose properties
2. Go to the Options
3. In the Dropdown at right labeled "Compatibility Level" choose "SQL Server 2005(90)"
4. Go to Files
5. Type "sa" in the owner textbox
6. Click OK

This solve my problem.

Alternatively, we can solve it using TSQL:
- My database name: avlDB
- MY Login: sa

EXEC sp_dbcmptlevel 'avlDB', '90';
GO
ALTER AUTHORIZATION ON DATABASE::avlDB TO "sa"
GO
USE [avlDB]
GO
EXECUTE AS USER = N'dbo' REVERT
GO

SQL server remap user - blank Login name

i come across this situation: my customer backup a db and i restore in our development server. The login user have empty "Login name". Hence, i'm not able to connect using this user name.



finally, i found out that we can use sp_change_users_login to map with login name.

for my case, my database name is HHotel, user login will be 'lam' and password 'l!a2m#'

USE HHotel;
GO
EXEC sp_change_users_login 'Auto_Fix', 'lam', NULL, 'l!a2m#';
GO

It's fixed after run above script.

For more example: http://msdn.microsoft.com/en-us/library/ms174378.aspx

Change stored proc owner in MSSQL 2000 & 2005

Below show how to change stored procedure owner in MS SQL 2000 and 2005

http://dotnetfish.blogspot.com/2008/04/change-table-and-stored-procedure.html