のえら

技術備忘とかメモとか.間違いはつっこんでいただきたい所存.

SQL Server 2008 R2 でログイン(ユーザ)の一覧をクエリで取得する

SQL Server 2008 R2 のログイン一覧を取得して加工したいんじゃが、コピペできる標準の機能としては存在しないらしい。
http://dba.stackexchange.com/questions/22803/listing-the-existing-sql-server-logins-and-users
クエリをがしがし組み合わせると取得可能っぽい。

http://sqlserverzest.com/2013/08/06/sql-server-get-all-login-accounts-using-t-sql-query-sql-logins-windows-logins-windows-groups/
単純な一覧だけならこっちのほうがシンプルかなー。

サンプルクエリ:

SELECT *
FROM sys.server_principals
WHERE TYPE IN ('S')
and name not like '%##%'
ORDER BY name, type_desc

typeがSのものは、SQLユーザという意味
##が付いているユーザはSQL Serverデフォルトユーザなので除外

sys.server_principals で取得できるカラム:
name
principal_id
sid type
type_desc
is_disabled
create_date
modify_date
default_database_name
default_language_name
credential_id


※ログイン情報の取得について
ログイン情報を取得するビューが2000と2005以降では微妙に異なる。
2000だと「syslogins」
2008だと「sys.server_principals」

システム情報取得についての比較表はこのページが参考になる。
http://mssqlfun.com/2012/08/02/sql-server-2000-system-tables-and-their-equivalent-dmv-in-sql-server-2005/


使用サンプル:
ユーザの既定のDBを移行するスクリプトを出力するクエリ。
⇒saやビルトインアカウントも出力されるので手動で除外しないといけない

SELECT 'Exec sp_defaultdb @loginame=''' + name + ''', @defdb=''' + default_database_name + ''''
FROM sys.server_principals 
WHERE TYPE IN ('S')
and name not like '%##%'
ORDER BY name, type_desc

※2000で同じ出力をする場合は以下のクエリとなる

SELECT 'Exec sp_defaultdb @loginame=''' + name + ''', @defdb=''' + dbname + ''''
FROM master.dbo.syslogins