{"id":408,"date":"2017-03-14T15:33:56","date_gmt":"2017-03-14T15:33:56","guid":{"rendered":"https:\/\/sqldoubleg.live-website.com\/?p=408"},"modified":"2022-12-25T10:13:04","modified_gmt":"2022-12-25T10:13:04","slug":"t-sql-tuesday-88-the-security-audit-wtf","status":"publish","type":"post","link":"https:\/\/www.sqldoubleg.com\/es\/2017\/03\/14\/t-sql-tuesday-88-the-security-audit-wtf\/","title":{"rendered":"T-SQL Tuesday #88 \u2013 Auditando la seguridad, WTF?"},"content":{"rendered":"

En esta entrada quiero compartir un procedimiento almacenado que uso para auditar los diferentes logins y sus permisos en nuestra instancia de SQL Server \"tsql2sday150x150\"<\/a><\/p>\n

 
\nEsta publicaci\u00f3n forma parte del T-SQL Tuesday<\/a>, que es una fiesta de blog mensual el segundo martes de cada mes. Todos son bienvenidos y tienen la oportunidad de escribir sobre SQL Server.
\nEl tema de este mes es Tu WTF del d\u00eda a d\u00eda (relacionado con la base de datos)<\/strong><\/em><\/a>, y una de las cosas m\u00e1s importantes para los administradores de bases de datos es la seguridad, de modo que ah\u00ed est\u00e1 mi \u00abAuditando la seguridad, WTF?\u00bb.<\/p>\n

 
\nAntecedentes<\/strong><\/p>\n

La seguridad es una tarea muy importante para los profesionales de bases de datos y no importa si acaba de aterrizar en una nueva empresa, si es un consultor o si trabajas en un equipo donde varias personas tienen privilegios elevados en los servidores, siempre es bueno saber qui\u00e9n puede hacer qu\u00e9 en un servidor, (tambi\u00e9n conocido como qui\u00e9n puede tumbar el servido).<\/p>\n

No estoy seguro de qu\u00e9 me asustar\u00eda m\u00e1s, si hay muchos [sysadmin] o simplemente [sa] porque el primero da miedo, pero el segundo implica descubrir qui\u00e9n tiene acceso a la contrase\u00f1a de [sa] y para saber qui\u00e9n hizo qu\u00e9, puede ser un verdadero dolor en el cuello.<\/p>\n

De una forma u otra, como dije, quiero saber las diferentes personas y el nivel de acceso a mi(s) servidor(es), por lo que en su d\u00eda cre\u00e9 este procedimiento almacenado, y ahora quiero compartirlo con ustedes.<\/p>\n

Podemos encontrar toda esa informaci\u00f3n usando diferentes DMVs y en mi caso utilizo sys.server_principals<\/a>, sys.server_role_members<\/a> and sys.server_permissions<\/a> y un procedimiento almacenado que apuesto a que no es tan conocido, sys.xp_logininfo<\/a> que ayuda a obtener una imagen m\u00e1s detallada de los Grupos del Directorio Activo de Windows.<\/p>\n

Los grandes WTF que he llegado a encontrar van desde logins de aplicaciones de terceros con privilegios elevados en un servidor compartido (sysadmin, serveradmin, etc.), logins de desarrollo o test en producci\u00f3n, mont\u00f3n de logins de personas que dejaron la empresa hace a\u00f1os, muchos logins que se podr\u00edan agrupar en un Grupo de Windows AD y mucho m\u00e1s, hay demasiada creatividad por ah\u00ed \ud83d\ude42<\/p>\n

 
\nThe truco<\/strong><\/p>\n

As\u00ed que ah\u00ed vamos, este es el script, y debo decir que es uno de esos que utilizo muy a menudo para verificar los problemas de seguridad.<\/p>\n

\r\nUSE [master];\r\nGO\r\nIF OBJECT_ID('dbo.sqlg_securityAuditServerLogins') IS NULL EXECUTE sp_executesql N'CREATE PROCEDURE dbo.sqlg_securityAuditServerLogins AS RETURN';\r\nGO\r\nSET ANSI_NULLS ON;\r\nGO\r\nSET QUOTED_IDENTIFIER ON;\r\nGO\r\n-- =============================================\r\n-- Author:\t\tRaul Gonzalez @SQLDoubleG\r\n-- Create date: 28\/06\/2013\r\n-- Description:\tReturns Server Login information\r\n--\t\t\t\tThis SP returns\r\n--\t\t\t\t\t- Server logins with server roles and permissions, logins included if it is a Windows Group, groups the login belong to and database users mapped to the login\r\n--\r\n-- Change log:\t2014-03-04 RAG\t- Removed the last 2 resultsets and included that info into que main one, columns [IncludedLogins] and [IncludedInWindowsGroups]\r\n--\t\t\t\t\t\t\t\t- Functionality to search a AD user when is included in a Windows Group\r\n--\t\t\t\t2014-05-15 RAG\t- Included list of database users the login is mapped to \r\n--\t\t\t\t2014-09-09 RAG\t- Added column CREATE_LOGIN, which contain the script required to recreate the login and its server roles if any\r\n--\t\t\t\t2016-05-13 RAG\t- Fixed bug when scripting server roles\r\n--\t\t\t\t2017-03-14 RAG\t- Removed deprecated view syslogins\r\n--\t\t\t\t\t\t\t\t- Added state_desc to the permisssion list (DENY, REVOKE, GRANT, GRANT_WITH_GRANT_OPTION)\r\n--\t\t\t\t\t\t\t\t- Added server permisssions to the CREATE LOGIN statement\r\n--\r\n-- Copyright:   (C) 2017 Raul Gonzalez (@SQLDoubleG https:\/\/sqldoubleg.live-website.com)\r\n--\r\n--              THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF \r\n--              ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED \r\n--              TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND\/OR FITNESS FOR A\r\n--              PARTICULAR PURPOSE.\r\n--\r\n--              THE AUTHOR SHALL NOT BE LIABLE TO YOU OR ANY THIRD PARTY FOR ANY INDIRECT, \r\n--              SPECIAL, INCIDENTAL, PUNITIVE, COVER, OR CONSEQUENTIAL DAMAGES OF ANY KIND\r\n--\r\n--              YOU MAY ALTER THIS CODE FOR YOUR OWN *NON-COMMERCIAL* PURPOSES. YOU MAY\r\n--              REPUBLISH ALTERED CODE AS LONG AS YOU INCLUDE THIS COPYRIGHT AND GIVE DUE CREDIT. \r\n--\r\n-- =============================================\r\nALTER PROCEDURE [dbo].[sqlg_securityAuditServerLogins]\r\n\t@loginName\tSYSNAME = NULL\r\nAS\r\nBEGIN\r\n\t\r\n\tSET NOCOUNT ON;\r\n\r\n\tDECLARE @sqlString\t\tNVARCHAR(4000);\r\n\r\n\tDECLARE @groupName\t\tSYSNAME\r\n\t\t\t, @numGroups\tINT\r\n\t\t\t, @countGroups\tINT = 1;\r\n\r\n\tDECLARE @version\t\tNVARCHAR(128)\t= CONVERT(NVARCHAR(128),SERVERPROPERTY('ProductVersion'))\r\n\tDECLARE @numericVersion\tDECIMAL(3,1)\t= CONVERT(DECIMAL(3,1), (LEFT( @version,  CHARINDEX('.', @version, 0) + 1 )) )\r\n\r\n\tCREATE TABLE #usersInGroups (\r\n\t\taccountName\t\t\tSYSNAME\r\n\t\t, [Type]\t\t\tSYSNAME\r\n\t\t, [privilege]\t\tSYSNAME\r\n\t\t, [mappedLogin]\t\tSYSNAME\r\n\t\t, [permissionPath]\tSYSNAME);\r\n\r\n\tCREATE TABLE #allDbUsers(\r\n\t\tdatabase_name\t\tSYSNAME\r\n\t\t, database_username SYSNAME\r\n\t\t, sid\t\t\t\tVARBINARY(85)\r\n\t);\r\n\r\n\tDECLARE @GO\t\t\t\tCHAR(4) = CHAR(10) + 'GO' + CHAR(10);\r\n\r\n\tSELECT IDENTITY(INT, 1, 1) AS ID\r\n\t\t\t, name AS GroupName\r\n\t\tINTO #windowsGroups\r\n\t\tFROM sys.server_principals AS sp\r\n\t\tWHERE sp.type = 'G'\r\n\t\t\tAND name NOT LIKE 'NT SERVICE\\%';\r\n\r\n\tSET @numGroups = @@ROWCOUNT;\r\n\r\n\tWHILE @countGroups <= @numGroups BEGIN\r\n\t\tSELECT @groupName = GroupName\r\n\t\t\tFROM #windowsGroups\r\n\t\t\tWHERE ID = @countGroups;\r\n\r\n\t\tSET @sqlString = 'EXEC XP_LOGININFO ' + QUOTENAME(@groupName) + ', [members]';\r\n\r\n\t\tINSERT INTO #usersInGroups\r\n\t\t\tEXECUTE sp_executesql @sqlString; \r\n\r\n\t\tSET @countGroups += 1;\r\n\r\n\tEND;\r\n\r\n\t-- Get all DB users\r\n\tEXECUTE sp_MSforeachdb N'\r\n\t\tUSE [?]\r\n\t\tINSERT INTO #allDbUsers\r\n\t\t\tSELECT DB_NAME() AS database_name\r\n\t\t\t\t\t, name AS database_username\r\n\t\t\t\t\t, sid AS principal_sid\r\n\t\t\t\tFROM sys.database_principals\r\n\t\t\t\tWHERE is_fixed_role = 0\r\n\t\t\t\t\tAND type <> ''R''\r\n\t\t\t\t\tAND sid IS NOT NULL\r\n\t\t\t\t\tAND name NOT IN (''guest'')\r\n\t';\r\n\r\n\t-- All server logins with their server roles\r\n\tSELECT @@SERVERNAME AS ServerName\r\n\t\t\t, sp.principal_id \r\n\t\t\t, sp.name AS LoginName\r\n\t\t\t, sp.type_desc AS LoginType\r\n\t\t\t, CASE WHEN sp.is_disabled = 1 THEN 'Yes' ELSE 'No' END AS IsDisabled\r\n\t\t\t, sp.default_database_name\r\n\t\t\t, STUFF((SELECT ', ' + sp2.name\r\n\t\t\t\t\t\tFROM sys.server_role_members AS srm\r\n\t\t\t\t\t\t\tLEFT JOIN sys.server_principals AS sp2\r\n\t\t\t\t\t\t\t\tON sp2.principal_id = srm.role_principal_id\r\n\t\t\t\t\t\tWHERE srm.member_principal_id = sp.principal_id\r\n\t\t\t\t\t\tFOR XML PATH('')), 1, 2, '') AS ServerRoles\r\n\t\t\t, STUFF((SELECT ', ' + p.state_desc + ' ' + p.permission_name \r\n\t\t\t\t\t\tFROM sys.server_permissions AS p\r\n\t\t\t\t\t\tWHERE p.grantee_principal_id = sp.principal_id \r\n\t\t\t\t\t\tFOR XML PATH('')), 1, 2, '') AS ServerPermissions\t\t\t\t\r\n\t\t\t, STUFF( (SELECT ', ' + QUOTENAME(mappedLogin) \r\n\t\t\t\t\t\tFROM #usersInGroups AS u WHERE u.permissionPath = sp.name ORDER BY mappedLogin FOR XML PATH('')), 1, 2, '') AS IncludedLogins\r\n\t\t\t, STUFF( (SELECT ', ' + QUOTENAME(permissionPath) \r\n\t\t\t\t\t\tFROM #usersInGroups AS u WHERE u.mappedLogin = sp.name ORDER BY permissionPath FOR XML PATH('')), 1, 2, '') AS IncludedInWindowsGroups\r\n\t\t\t, STUFF( (SELECT ', ' + QUOTENAME(database_name) + '.' + QUOTENAME(database_username) \r\n\t\t\t\t\t\tFROM #allDbUsers AS u WHERE u.sid = sp.sid ORDER BY database_name FOR XML PATH('')), 1, 2, '') AS MappedToDBuser\r\n\t\t\t, STUFF((SELECT @GO + \r\n\t\t\t\t\t\t\t\tCASE WHEN @numericVersion >= 11 THEN 'ALTER SERVER ROLE ' + QUOTENAME(sp2.name) + ' DROP MEMBER ' + QUOTENAME(sp.name)\r\n\t\t\t\t\t\t\t\t\tELSE 'EXECUTE sys.sp_dropsrvrolemember ' + QUOTENAME(sp.name) + ', ' + QUOTENAME(sp2.name) \r\n\t\t\t\t\t\t\t\tEND\r\n\t\t\t\t\t\tFROM sys.server_role_members AS srm\r\n\t\t\t\t\t\t\tLEFT JOIN sys.server_principals AS sp2\r\n\t\t\t\t\t\t\t\tON sp2.principal_id = srm.role_principal_id\r\n\t\t\t\t\t\tWHERE srm.member_principal_id = sp.principal_id\r\n\t\t\t\t\t\tFOR XML PATH('')), 1, 4, '') AS DROP_SERVER_ROLE\r\n\t\t\t,\t'USE [master]' + @GO\r\n\t\t\t\t+ CASE \r\n\t\t\t\t\tWHEN sp.type IN ('U', 'G') THEN 'CREATE LOGIN ' + QUOTENAME(sp.name) + ' FROM WINDOWS WITH DEFAULT_DATABASE = ' + QUOTENAME(sp.default_database_name)\r\n\t\t\t\t\tELSE 'CREATE LOGIN ' + QUOTENAME(sp.name) + ' WITH PASSWORD = ' + CONVERT(NVARCHAR(256), LOGINPROPERTY( sp.name, 'PasswordHash' ), 1) + \r\n\t\t\t\t\t\t' HASHED, SID = ' + CONVERT(NVARCHAR(256), (sp.sid), 1) + ', DEFAULT_DATABASE = '  + QUOTENAME(sp.default_database_name) +\r\n\t\t\t\t\t', CHECK_POLICY = ' + CASE WHEN sqll.is_policy_checked = 1 THEN 'ON' ELSE 'OFF' END + \r\n\t\t\t\t\t', CHECK_EXPIRATION = ' + CASE WHEN sqll.is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END + @GO\r\n\t\t\t\t\t+ (SELECT p.state_desc + ' ' + p.permission_name + ' TO ' + QUOTENAME(sp.name) + @GO AS [text()]\r\n\t\t\t\t\t\t\t\tFROM sys.server_permissions AS p\r\n\t\t\t\t\t\t\t\tWHERE p.grantee_principal_id = sp.principal_id \r\n\t\t\t\t\t\t\t\tFOR XML PATH(''))\r\n\t\t\t\tEND +\r\n\t\t\t\tCASE WHEN sp.is_disabled = 1 THEN 'ALTER LOGIN ' + QUOTENAME(sp.name) + ' DISABLE' + @GO ELSE '' END +\r\n\t\t\t\tISNULL(STUFF( (SELECT CHAR(10) + \r\n\t\t\t\t\t\t\t\t\t\t\tCASE WHEN @numericVersion >= 11 THEN 'ALTER SERVER ROLE '  + QUOTENAME(name) + ' ADD MEMBER ' + QUOTENAME(sp.name) \r\n\t\t\t\t\t\t\t\t\t\t\t\tELSE 'EXECUTE sp_addsrvrolemember ' + QUOTENAME(sp.name) + ', ' + QUOTENAME(name)\r\n\t\t\t\t\t\t\t\t\t\t\tEND\t\t\t\r\n\t\t\t\t\t\t\t\t\t\t+ CHAR(10) + 'GO'\r\n\t\t\t\t\t\t\t\t\tFROM sys.server_role_members AS rm\r\n\t\t\t\t\t\t\t\t\t\tINNER JOIN sys.server_principals AS r\r\n\t\t\t\t\t\t\t\t\t\t\tON r.principal_id = rm.role_principal_id\r\n\t\t\t\t\t\t\t\t\t\t\t\tAND r.type = 'R'\r\n\t\t\t\t\t\t\t\t\tWHERE rm.member_principal_id = sp.principal_id\r\n\t\t\t\t\t\t\t\t\tFOR XML PATH('')), 1, 1, ''), '') \r\n\t\t\tAS CREATE_LOGIN\r\n\r\n\t\tFROM sys.server_principals AS sp\r\n\t\t\tLEFT JOIN sys.sql_logins AS sqll\r\n\t\t\t\tON sqll.sid = sp.sid\r\n\t\tWHERE sp.type <> 'R' -- R = Server role\r\n\t\t\tAND sp.name NOT LIKE '#%#'\r\n\t\t\tAND sp.name NOT LIKE 'NT %\\%'\r\n\t\t\tAND ( sp.name LIKE ISNULL(@loginName, sp.name)\r\n\t\t\t\t\t-- Lookup for the given login within all windows groups in the server\r\n\t\t\t\t\tOR EXISTS ( SELECT 1 FROM #usersInGroups AS uig WHERE uig.permissionPath = sp.name AND uig.mappedLogin LIKE ISNULL(@loginName, uig.mappedLogin) ) )\r\n\t\tORDER BY ServerRoles DESC\r\n\t\t\t, LoginType ASC\r\n\t\t\t, LoginName ASC;\r\n\r\n\tDROP TABLE #windowsGroups;\r\n\tDROP TABLE #usersInGroups;\r\nEND;\r\nGO \r\n\r\n<\/pre>\n
\"\"<\/a><\/p>\n
 
\nConclusi\u00f3n<\/strong><\/p>\n
Si ejecutas este procedimiento almacenado puede que te lleves alguna sorpresa y veas cosas que te hagan decir WTF!, pero es mucho mejor si conocemos la situaci\u00f3n antes de que sea demasiado tarde.<\/p>\n
Y por \u00faltimo quiero agradecer de nuevo a Kennie Nybo ser el aanfitri\u00f3n este mes y a vosotros por leer este post.<\/p>\n
Como siempre, cualquier pregunta o comentario, no dud\u00e9is en usar los comentarios.<\/p>\n
 <\/p>","protected":false},"excerpt":{"rendered":"
En esta entrada quiero compartir un procedimiento almacenado que uso para auditar los diferentes logins y sus permisos en nuestra instancia de SQL Server    Esta publicaci\u00f3n forma parte del T-SQL Tuesday,…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,19,43],"tags":[59,5,15,60],"_links":{"self":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/408"}],"collection":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/comments?post=408"}],"version-history":[{"count":0,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/408\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/media?parent=408"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/categories?post=408"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/tags?post=408"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}