Skip to content

ExampleSQL.com

SQL Tips, Tools and Resources

Tag: DEFAULT DATABASE

Posted on October 9, 2012October 13, 2012

How to find the sql server default database for a user

In this post I’m going to discuss how to find the sql server default database for a user.  It should be noted first that ‘user’ in the title is a bit of a misnomer.  It is possible to determine a default database for a specific login (certificate, asymmetric key, windows, sql), role or group but not a particular ‘user’.  It is often useful to know the default database for a given login, role or group since it can make building queries easier. There is a catalog view named sys.server_principals that can be queried to find the desired information.  Further details can be found here.

SQL

select name, default_database_name from sys.server_principals;

Output

name                                                                                                                             default_database_name
-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
sa                                                                                                                               master
public                                                                                                                           NULL
sysadmin                                                                                                                         NULL

 

Tags

  • 2005
  • 2008
  • 2008 R2
  • 2012
  • 2014
  • BEGIN
  • CHARINDEX
  • CLOSE
  • CREATE
  • CURSOR
  • dbo
  • DEALLOCATE
  • DECLARE
  • DEFAULT DATABASE
  • DROP
  • DYNAMIC
  • END
  • FOR
  • FROM
  • GLOBAL
  • IDENTITY
  • INSERT
  • INTO
  • LEFT
  • loop
  • MySQL
  • NEXT
  • NOT
  • NULL
  • NVARCHAR
  • OBJECT_ID
  • OPEN
  • PRINT
  • REVERSE
  • RIGHT
  • SELECT
  • SET
  • SQL Server
  • T-SQL
  • TABLE
  • TEMP
  • TSQL
  • VALUES
  • VARCHAR
  • WHILE

Recent Posts

  • SQL Server Insensitive Cursor
  • SQL Server REVERSE String Function with Examples 2008, R2, 2012, 2014
  • DECLARE and SET Variables in one line SQL Server 2008, R2, 2012, 2014
  • SQL Server 2012 Date and Time Functions
  • SQL Server 2012 String Functions List

Recent Comments

    Archives

    • September 2019
    • August 2013
    • June 2013
    • January 2013
    • November 2012
    • October 2012

    Categories

    • mysql
    • sqlserver-t-sql-tsql

    Meta

    • Log in
    • Entries feed
    • Comments feed
    • WordPress.org
    Proudly powered by WordPress