SQL Server 2012: SELECT Machine Name, Server Name, Edition, Product Level, Product Version, Licence Type

Description

This post shows how to SELECT property information for a SQL Server.



Solution

The SELECT statement below retrieves Machine Name, Server Name, Edition, Product Level, Product Version, and License Type.


SELECT
SERVERPROPERTY('MACHINENAME') as [Machine Name],
SERVERPROPERTY('SERVERNAME') as [Server Name],
SERVERPROPERTY('EDITION') as [Edition],
SERVERPROPERTY('PRODUCTLEVEL') as [Product Level] ,
SERVERPROPERTY('PRODUCTVERSION') as [Product Version],
SERVERPROPERTY('LICENSETYPE') as [License Type]



The results of the query are shown below.

References

MSDN (2014).  SERVERPROPERTY (Transact-SQL).  Retrieved January 8, 2014 from http://msdn.microsoft.com/en-us/library/ms174396.aspx

How to Associate SQL Server Login with Existing Database User

Description

Sometimes a SQL Server Login  is created and corresponding Database User is created at the same time.  However, sometimes the Database User is not associated with a SQL Server Login and needs to be.  For example, this could happen during an database environment migration or if when the Active Directory User Account associated with SQL Server Login is deleted and a new one is created.


Solution

The steps below can be used to lookup database principals and then associate a Login with a User.

1.  Run the SELECT query below to view all database principals, including:
- DATABASE_ROLE
- SQL_USER
- WINDOWS_USER

SELECT * FROM sys.database_principals


2.  Run  ALTER USER query below to alter the database user and associate a login with it.  In the example below, a Windows Login is being associated with a database user.

ALTER USER "DOMAIN\Username"
WITH
LOGIN = "DOMAIN\Username"

(Microsoft TechNet, 2014)


References

Bertrand, A. (November, 2013).  Script to Set the SQL Server Database Default Schema For All Users.  Retrieved January 7, 2014 from
http://www.mssqltips.com/sqlservertip/3098/script-to-set-the-sql-server-database-default-schema-for-all-users/

Microsoft TechNet (2014).  ALTER USER (Transact-SQL).  Retrieved January 7, 2014 from http://technet.microsoft.com/en-us/library/ms176060.aspx

Tharaka MTR (May, 2013).  How to Fix Orphaned SQL Users.  Retrieved January 7, 2014 from http://www.codeproject.com/Articles/594134/How-to-Fix-Orphaned-SQL-Users