Earlier today I was required to pull the list of all SQL Login Accounts, Windows Login Accounts and Windows Group Login Accounts (basically all the Logins along with the Account Type of the Login) on one of the SQL Server instance where there are close to a hundred Login Accounts existing.
Doing it from SSMS GUI will take forever. So, I wrote a simple T-SQL script using which I was able to pull out all that information in less than a second!
Get the list of all Login Accounts in a SQL Server
SELECT name AS Login_Name, type_desc AS Account_Type FROM sys.server_principals WHERE TYPE IN ('U', 'S', 'G') and name not like '%##%' ORDER BY name, type_desc
Get the list of all SQL Login Accounts only
SELECT name FROM sys.server_principals WHERE TYPE = 'S' and name not like '%##%'
Get the list of all Windows Login Accounts only
SELECT name FROM sys.server_principals WHERE TYPE = 'U'
Get the list of all Windows Group Login Accounts only
SELECT name FROM sys.server_principals WHERE TYPE = 'G'
Note: Requires ALTER ANY LOGIN server permission to be able to view all the logins.
Thank you very much
Thanks for the handy scripts.
I would also throw in a ‘K’ to show any Asymmetric Key mapped logins too.
how to get the permission of all login
[…] Источник: SQL Server – Get all Login Accounts Using T-SQL Query – SQL Logins, Windows Logins, Windows Grou… […]
This is a very good post, thanks
These are great scripts. How can I modify them to include the permissions of these users?
SET NOCOUNT ON
DECLARE
@sql nvarchar(max)
, @Line int = 1
, @max int = 0
, @@CurDB nvarchar(100) = ”
CREATE TABLE #SQL
(
Idx int IDENTITY
,xSQL nvarchar(max)
)
INSERT INTO #SQL
( xSQL
)
SELECT
‘IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N”’
+ QUOTENAME(name) + ”’)
‘ + ‘ CREATE LOGIN ‘ + QUOTENAME(name) + ‘ WITH PASSWORD=’
+ sys.fn_varbintohexstr(password_hash) + ‘ HASHED, SID=’
+ sys.fn_varbintohexstr(sid) + ‘, ‘ + ‘DEFAULT_DATABASE=’
+ QUOTENAME(COALESCE(default_database_name , ‘master’))
+ ‘, DEFAULT_LANGUAGE=’
+ QUOTENAME(COALESCE(default_language_name , ‘us_english’))
+ ‘, CHECK_EXPIRATION=’ + CASE is_expiration_checked
WHEN 1 THEN ‘ON’
ELSE ‘OFF’
END + ‘, CHECK_POLICY=’
+ CASE is_policy_checked
WHEN 1 THEN ‘ON’
ELSE ‘OFF’
END + ‘
Go
‘
FROM
sys.sql_logins
WHERE
name ‘sa’
INSERT INTO #SQL
( xSQL
)
SELECT
‘IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N”’
+ QUOTENAME(name) + ”’)
‘ + ‘ CREATE LOGIN ‘ + QUOTENAME(name) + ‘ FROM WINDOWS WITH ‘
+ ‘DEFAULT_DATABASE=’
+ QUOTENAME(COALESCE(default_database_name , ‘master’))
+ ‘, DEFAULT_LANGUAGE=’
+ QUOTENAME(COALESCE(default_language_name , ‘us_english’))
+ ‘;
Go
‘
FROM
sys.server_principals
WHERE
type IN ( ‘U’ , ‘G’ )
AND name NOT IN ( ‘BUILTIN\Administrators’ ,
‘NT AUTHORITY\SYSTEM’ );
PRINT ‘/*****************************************************************************************/’
PRINT ‘/*************************************** Create Logins ***********************************/’
PRINT ‘/*****************************************************************************************/’
SELECT
@Max = MAX(idx)
FROM
#SQL
WHILE @Line <= @max
BEGIN
SELECT
@sql = xSql
FROM
#SQL AS s
WHERE
idx = @Line
PRINT @sql
SET @line = @line + 1
END
DROP TABLE #SQL
CREATE TABLE #SQL2
(
Idx int IDENTITY
,xSQL nvarchar(max)
)
INSERT INTO #SQL2
( xSQL
)
SELECT
'EXEC sp_addsrvrolemember ' + QUOTENAME(L.name) + ', '
+ QUOTENAME(R.name) + ';
GO
'
FROM
sys.server_principals L
JOIN sys.server_role_members RM
ON L.principal_id = RM.member_principal_id
JOIN sys.server_principals R
ON RM.role_principal_id = R.principal_id
WHERE
L.type IN ( 'U' , 'G' , 'S' )
AND L.name NOT IN ( 'BUILTIN\Administrators' ,
'NT AUTHORITY\SYSTEM' , 'sa' );
PRINT '/*****************************************************************************************/'
PRINT '/******************************Add Server Role Members *******************************/'
PRINT '/*****************************************************************************************/'
SELECT
@Max = MAX(idx)
FROM
#SQL2
SET @line = 1
WHILE @Line <= @max
BEGIN
SELECT
@sql = xSql
FROM
#SQL2 AS s
WHERE
idx = @Line
PRINT @sql
SET @line = @line + 1
END
DROP TABLE #SQL2
PRINT '/*****************************************************************************************/'
PRINT '/*****************Add User and Roles membership to Indivdual Databases********************/'
PRINT '/*****************************************************************************************/'
–Drop Table #Db
CREATE TABLE #Db
(
idx int IDENTITY
,DBName nvarchar(100)
);
INSERT INTO #Db
SELECT
name
FROM
master.dbo.sysdatabases
WHERE
name IN ('database name','database name' )
ORDER BY
name;
SELECT
@Max = MAX(idx)
FROM
#Db
SET @line = 1
–Select * from #Db
–Exec sp_executesql @SQL
WHILE @line <= @Max
BEGIN
SELECT
@@CurDB = DBName
FROM
#Db
WHERE
idx = @line
SET @SQL = 'Use ' + @@CurDB + '
Declare @@Script NVarChar(4000) = ''''
DECLARE cur CURSOR FOR
Select ''Use ' + @@CurDB + ';
Go
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'''''' +
mp.[name] + '''''')
CREATE USER ['' + mp.[name] + ''] FOR LOGIN ['' +mp.[name] + ''] WITH DEFAULT_SCHEMA=[dbo]; ''+ CHAR(13)+CHAR(10) +
''GO'' + CHAR(13)+CHAR(10) +
''EXEC sp_addrolemember N'''''' + rp.name + '''''', N''''['' + mp.[name] + '']'''';
Go''
FROM sys.database_role_members a
INNER JOIN sys.database_principals rp ON rp.principal_id = a.role_principal_id
INNER JOIN sys.database_principals AS mp ON mp.principal_id = a.member_principal_id
OPEN cur
FETCH NEXT FROM cur INTO @@Script;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @@Script
FETCH NEXT FROM cur INTO @@Script;
END
CLOSE cur;
DEALLOCATE cur;';
–Print @SQL
Exec sp_executesql @SQL;
–Set @@Script = ''
SET @Line = @Line + 1
END
DROP TABLE #Db
select highest_cpu_queries.plan_handle,highest_cpu_queries.
plan_generation_num,highest_cpu_queries.max_worker_time,
highest_cpu_queries.total_physical_reads,
highest_cpu_queries.total_logical_reads,
highest_cpu_queries.total_elapsed_time,q.[text],q.dbid,q.objectid,q.number,q.encrypted,query_plan
from (select top 50 qs.plan_handle,
qs.plan_generation_num,qs.creation_time, qs.execution_count, qs.total_worker_time,
qs.max_worker_time, qs.total_elapsed_time,
qs.max_elapsed_time, qs.total_logical_reads, qs.max_logical_reads,
qs.total_physical_reads, qs.max_physical_reads from sys.dm_exec_query_stats
qs order by qs.total_worker_time DESC)
as highest_cpu_queries
cross apply sys.dm_exec_sql_text (plan_handle) as q
cross apply sys.dm_exec_query_plan (plan_handle) as qp
order by highest_cpu_queries.total_worker_time DESC
Be sure to look for a program that may copy many game titles when you
find yourself in the market for one. Once you are acquainted with the terms from the game, you’re set to learn the
overall game to the fullest. It takes time for you to
develop camaraderie similar to this, however, you can speed the task
up a lttle bit by ensuring that all people have use of a lot of team development activities.
Winner Winner Chicken Dinner, this is great thanks!
[…] Earlier today I was required to pull the list of all SQL Login Accounts, Windows Login Accounts and Windows Group Login Accounts (basically all the Logins along with the Account Type of the Login) on one of the SQL Server instance where there are close to a hundred Login Accounts existing. Doing it from SSMS GUI… https://dataginger.com/2013/08/06/sql-server-get-all-login-accounts-using-t-sql-query-sql-logins-win… […]
[…] https://dataginger.com/2013/08/06/sql-server-get-all-login-accounts-using-t-sql-query-sql-logins-win… […]
Я уверен, что пост затронет всех пользователей авторского и действительно
очень хорошего блога.
Thank You for the scripts!!
[…] Earlier today I was required to pull the list of all SQL Login Accounts, Windows Login Accounts and Windows Group Login Accounts (basically all the Logins along with the Account Type of the Login) on one of the SQL Server instance where there are close to a hundred Login Accounts existing. Doing it from SSMS GUI… https://dataginger.com/2013/08/06/sql-server-get-all-login-accounts-using-t-sql-query-sql-logins-win… […]
[…] SQL Server – Get all Login Accounts Using T-SQL Query … […]
[…] SQL Server – Get all Login Accounts Using T-SQL Query … […]
[…] SQL Server – Get all Login Accounts Using T-SQL Query … […]
[…] SQL Server – Get all Iniciar Sesion Accounts Using T-SQL Query … […]
[…] SQL Server – Get all Login Accounts Using T-SQL Query … […]
[…] SQL Server – Get all Login Accounts Using T-SQL Query … […]
[…] SQL Server – Get all Login Accounts Using T-SQL Query … […]
[…] SQL Server – Get all Login Accounts Using T-SQL Query … […]
[…] SQL Server – Get all Login Accounts Using T-SQL Query … […]
[…] SQL Server – Get all Login Accounts Using T-SQL … […]
[…] SQL Server – Get all Login Accounts Using T-SQL Query … […]
[…] SQL Server – Get all Iniciar Sesion Accounts Using T-SQL Query … […]
[…] SQL Server – Get all Login Accounts Using T-SQL Query … […]
[…] SQL Server – Get all Login Accounts Using T-SQL Query … […]
[…] SQL Server – Get all Login Accounts Using T-SQL Query … […]
[…] SQL Server – Get all Login Accounts Using T-SQL Query … […]
[…] » Visit Now Aug 06, 2013 · Earlier today I was required to pull the list of all SQL Login Accounts, Windows Login Accounts and Windows Group Login Accounts (basically all the Logins along with the Account Type of the Login) on one of the SQL Server instance where there are close to a hundred Login Accounts existing. Doing it from SSMS GUI… […]
[…] » Visit Now Aug 06, 2013 · Earlier today I was required to pull the list of all SQL Login Accounts, Windows Login Accounts and Windows Group Login Accounts (basically all the Logins along with the Account Type of the Login) on one of the SQL Server instance where there are close to a hundred Login Accounts existing. Doing it from SSMS GUI… […]
[…] SQL Server – Get all Login Accounts Using T-SQL Query … […]
[…] » Visit Now Aug 06, 2013 · Earlier today I was required to pull the list of all SQL Login Accounts, Windows Login Accounts and Windows Group Login Accounts (basically all the Logins along with the Account Type of the Login) on one of the SQL Server instance where there are close to a hundred Login Accounts existing. Doing it from SSMS GUI… […]
[…] SQL Server – Get all Login Accounts Using T-SQL Query … […]
[…] » Visit Now Aug 06, 2013 · Earlier today I was required to pull the list of all SQL Login Accounts, Windows Login Accounts and Windows Group Login Accounts (basically all the Logins along with the Account Type of the Login) on one of the SQL Server instance where there are close to a hundred Login Accounts existing. Doing it from SSMS GUI… […]
[…] » Visit Now Aug 06, 2013 · Earlier today I was required to pull the list of all SQL Login Accounts, Windows Login Accounts and Windows Group Login Accounts (basically all the Logins along with the Account Type of the Login) on one of the SQL Server instance where there are close to a hundred Login Accounts existing. Doing it from SSMS GUI… […]
[…] » Visit Now Aug 06, 2013 · Earlier today I was required to pull the list of all SQL Login Accounts, Windows Login Accounts and Windows Group Login Accounts (basically all the Logins along with the Account Type of the Login) on one of the SQL Server instance where there are close to a hundred Login Accounts existing. Doing it from SSMS GUI… […]