Etiqueta: sql server

Si trabajas con SQL Server en entornos productivos, dominar la administración de permisos y el monitoreo es clave para mantener la seguridad, estabilidad y rendimiento del sistema.

En este artículo encontrarás desde comandos básicos hasta ejemplos más avanzados utilizados en escenarios reales.

---

🔐 Gestión de permisos (Nivel básico)

Dar permiso CONNECT en la base de datos

USE NombreBaseDatos;
GO
GRANT CONNECT TO NombreUsuario;

Permitir ver los Stored Procedures

GRANT VIEW DEFINITION ON DATABASE::TuBaseDeDatos TO TuUsuario;

Permitir ver el código de los SPs

GRANT VIEW DEFINITION TO TuUsuario;

Dar permiso EXECUTE sobre el esquema dbo o base de datos

GRANT EXECUTE ON SCHEMA::dbo TO NombreUsuario;
GRANT EXECUTE ON DATABASE::TuBaseDeDatos TO NombreUsuario;

Permisos sobre tablas o vistas específicas

GRANT SELECT ON dbo.MiTabla TO NombreUsuario;
GRANT INSERT, UPDATE, DELETE ON dbo.MiTabla TO NombreUsuario;
GRANT SELECT ON dbo.MiVista TO NombreUsuario;
GRANT SELECT ON DATABASE::MiDB NombreUsuario;

Acceso completo sobre un esquema

GRANT SELECT ON SCHEMA::dbo TO NombreUsuario;
GRANT INSERT, UPDATE, DELETE ON SCHEMA::dbo TO NombreUsuario;

---

🔐 Gestión avanzada de seguridad

Crear Login (nivel servidor)

CREATE LOGIN MiLogin WITH PASSWORD = 'PasswordSeguro123!';

Crear Usuario en la base de datos

USE NombreBaseDatos;
CREATE USER MiUsuario FOR LOGIN MiLogin;

Asignar roles predefinidos

ALTER ROLE db_datareader ADD MEMBER MiUsuario;
ALTER ROLE db_datawriter ADD MEMBER MiUsuario;

Crear un rol personalizado

CREATE ROLE RolLecturaEscritura;

GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO RolLecturaEscritura;

ALTER ROLE RolLecturaEscritura ADD MEMBER MiUsuario;

Revocar permisos (REVOKE vs DENY)

-- Revoca el permiso (lo quita si existe)
REVOKE SELECT ON dbo.MiTabla FROM MiUsuario;

-- Deniega explícitamente (tiene mayor prioridad)
DENY SELECT ON dbo.MiTabla TO MiUsuario;

💡 Tip importante: DENY siempre tiene prioridad sobre GRANT.

Ejecutar como otro usuario (testing de seguridad)

EXECUTE AS USER = 'MiUsuario';

SELECT * FROM dbo.MiTabla;

REVERT;

Verificar usuarios conectados

USE master;
GO 

SELECT
    spid,
    loginame,
    hostname,
    program_name,
    db_name(dbid) AS BaseDatos
FROM sys.sysprocesses
WHERE dbid = DB_ID('BaseDeDatos');

---

🔐 Respaldos y restauracion

Identificar usuarios huérfanos

SELECT dp.type_desc, dp.sid, dp.name AS user_name
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp
    ON dp.sid = sp.sid
WHERE sp.sid IS NULL
    AND dp.authentication_type_desc = 'INSTANCE';

Reparar usuarios huérfanos

--Crear inicio de sesión que no existe
CREATE LOGIN <login_name>
WITH PASSWORD = '<use_a_strong_password_here>',
SID = <SID>;

--Asignar usuario huérfano a inicio de sesión existente
ALTER USER <user_name> WITH Login = <login_name>;

---

📊 Monitoreo y diagnóstico

Que privilegios tiene un usuario

SELECT 
    dp.name AS Usuario,
    dp2.name AS Objeto,
    perm.permission_name,
    perm.state_desc
FROM sys.database_permissions perm
JOIN sys.database_principals dp 
    ON perm.grantee_principal_id = dp.principal_id
LEFT JOIN sys.objects dp2
    ON perm.major_id = dp2.object_id
WHERE dp.name = 'TuUsuario';

A que roles pertenece un usuario

SELECT 
    member.name AS Usuario,
    role.name AS Rol
FROM sys.database_role_members drm
JOIN sys.database_principals role 
    ON drm.role_principal_id = role.principal_id
JOIN sys.database_principals member 
    ON drm.member_principal_id = member.principal_id
WHERE member.name = 'TuUsuario';

Privilegios sobre un objeto especifico

EXECUTE AS USER = 'Usuario';
SELECT *
FROM fn_my_permissions('NombreTabla', 'OBJECT');
REVERT;

Uso de índices y tablas

SELECT
    DB_NAME(database_id) AS BaseDeDatos,
    OBJECT_NAME(i.object_id) AS Objeto,
    last_user_seek,
    last_user_scan,
    last_user_lookup,
    last_user_update
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes i 
    ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE database_id = DB_ID('TuBaseDeDatos')
ORDER BY 
    COALESCE(last_user_seek, last_user_scan, last_user_lookup, last_user_update) DESC;

Consultas más costosas (performance tuning)

SELECT TOP 10
    qs.total_elapsed_time / qs.execution_count AS AvgElapsedTime,
    qs.execution_count,
    qs.total_logical_reads,
    qt.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY AvgElapsedTime DESC;

Bloqueos activos

SELECT
    blocking_session_id,
    session_id,
    wait_type,
    wait_time,
    wait_resource
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;

Sesiones activas

SELECT
    session_id,
    login_name,
    status,
    host_name,
    program_name
FROM sys.dm_exec_sessions
WHERE is_user_process = 1;

Actividad con Default Trace

SELECT *
FROM fn_trace_gettable(
    (SELECT TOP 1 path FROM sys.traces WHERE is_default = 1),
    DEFAULT
)
WHERE DatabaseName = 'TuBaseDeDatos'
ORDER BY StartTime DESC;

Busqueda de objetos en la Base de Datos

SELECT 
    o.type_desc,
    s.name AS esquema,
    o.name AS objeto
FROM sys.sql_modules m
INNER JOIN sys.objects o
    ON m.object_id = o.object_id
INNER JOIN sys.schemas s
    ON o.schema_id = s.schema_id
WHERE m.definition LIKE '%ObjetoABuscar%';

---

🧠 Buenas prácticas

• Usa roles en lugar de permisos directos a usuarios
• Evita usar dbo para todo, segmenta por esquemas
• Aplica el principio de mínimos privilegios
• Audita accesos periódicamente
• Documenta cambios de seguridad

---

🚀 Conclusión

Estos comandos te permiten:

• Administrar accesos de forma segura
• Diagnosticar problemas de rendimiento
• Identificar bloqueos y uso de recursos
• Prepararte para escenarios reales en producción

Dominar estos scripts es clave para cualquier desarrollador backend o líder técnico que trabaje con SQL Server.

---

📌 Si te fue útil, considera guardar este artículo o compartirlo con tu equipo.