Administrar bases de datos, usuarios y permisos en MSSQL Server 2022

Microsoft SQL Server comúnmente conocido como MSSQL Server, es un sistema de gestión de bases de datos relacionales (RDBMS) desarrollado por Microsoft Corporation. Se utiliza para almacenar y administrar grandes cantidades de datos y se puede utilizar en una variedad de aplicaciones, incluidas aplicaciones basadas en web, cliente/servidor y aplicaciones independientes.

MS SQL Server encuentra uso en sectores empresariales, esto incluye servicios financieros, comercio electrónico y atención médica. Esto se debe principalmente a que es capaz de manejar una gran cantidad de usuarios simultáneos y proporciona una plataforma segura y confiable para almacenar y administrar datos confidenciales. MSSQL también admite una gran cantidad de lenguajes y plataformas de programación como .NET, Java, Node.js, etc. Esto la convierte en una plataforma de base de datos versátil que se puede utilizar en una amplia gama de aplicaciones.

MSSQL ofrece una variedad de características que incluyen:

Escalabilidad: puede manejar grandes cantidades de datos y escalar para adaptarse a un número creciente de usuarios y aplicaciones.
Seguridad: proporciona funciones de seguridad sólidas, que incluyen cifrado, control de acceso y auditoría, para proteger los datos y garantizar el cumplimiento de las regulaciones.
Alta disponibilidad: incluye funciones como agrupación en clústeres y replicación para garantizar una alta disponibilidad y minimizar el tiempo de inactividad.
Integración: MSSQL se integra con una variedad de lenguajes y plataformas de programación, incluidos .NET, Java y Node.js, lo que facilita el desarrollo y la implementación de aplicaciones.
Inteligencia empresarial: también incluye funciones avanzadas de análisis e informes, como minería de datos, almacenamiento de datos y OLAP (procesamiento analítico en línea), que permiten a los usuarios obtener información a partir de grandes cantidades de datos.
Rendimiento: proporciona funciones como optimización e indexación de consultas para garantizar una recuperación y procesamiento de datos rápidos y eficientes.
Capacidades de la nube: MSSQL se puede implementar localmente o en la nube, con soporte para escenarios de nube híbrida.

Únase a mí en esta guía mientras profundizamos en cómo administrar bases de datos, usuarios y permisos en MS SQL Server 2022.

Empezando

Antes de profundizar en el meollo de este asunto, debe tener MSSQL Server 2022 instalado en su sistema. Esto se puede hacer de varias maneras dependiendo de su entorno. A continuación se muestran algunas de las guías dedicadas para ayudarlo a instalar el servidor MSSQL 2022.

Ejecute Microsoft SQL Server 2022 en el contenedor Docker/Podman
Instale Microsoft SQL Server 2022 en Rocky Linux 8/AlmaLinux 8
Cómo instalar Microsoft SQL Server 2022 en Ubuntu 20.04

Una vez instalado, proceda como se muestra a continuación.

1. Instale las herramientas de línea de comandos de SQL Server

Para administrar bases de datos en MS SQL Server 2022, puede utilizar varias herramientas, incluidas SQL Server Management Studio (SSMS), Azure Data Studio y herramientas de línea de comandos.

Para esta guía, aprenderemos cómo administrar las bases de datos de MSSQL Server 2022 usando la línea de comandos. Instalaremos las herramientas de línea de comandos de SQL Server: sqlcmd y bcp.

Dependiendo de su sistema, agregue el repositorio e instale sqlcmd y bcp usando los siguientes comandos.

Debian/Ubuntu

Comience agregando el repositorio al sistema.

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list

Una vez agregado, actualice su sistema e instale sqlcmd y bcp:

sudo apt-get update
sudo apt-get install mssql-tools unixodbc-dev

Ahora exporte la RUTA del entorno:

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

En Rocky Linux/Alma Linux/CentOS/RedHat

Agregue el repositorio con el comando:

curl https://packages.microsoft.com/config/rhel/8/prod.repo | sudo tee /etc/yum.repos.d/msprod.repo

Una vez agregados, instale los paquetes sqlcmd y bcp:

sudo yum remove mssql-tools unixODBC-utf16-devel
sudo yum install mssql-tools unixODBC-devel

Ahora exporte el entorno PATH

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

En SLES

También es posible instalar mssql-tools en SUSE Linux. Comience agregando el repositorio:

sudo zypper addrepo -fc https://packages.microsoft.com/config/sles/12/prod.repo
sudo zypper --gpg-auto-import-keys refresh

Una vez agregado, instale mssql-tools con el comando:

sudo zypper install mssql-tools unixODBC-devel

Exportar el medio ambiente:

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

En MacOS/OS-X

Puede instalar sqlcmd y bcp en MacOS/OS-X usando HomeBrew. Agregue el repositorio con el comando:

brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release

A continuación, instale mssql-tools con el comando:

brew update
brew install mssql-tools

2. Administrar bases de datos en MSSQL Server 2022

Ahora podemos usar las declaraciones del lenguaje Transact-SQL para administrar MSSQL Server 2022. Primero, acceda a la base de datos usando el comando con la siguiente sintaxis:

sqlcmd -S IP_ADDRESS -U sa -P '<YourPassword>'

En el comando, hay varias banderas:

-S: Especifica el nombre/dirección IP de SQL Server
-U : establece el nombre de usuario
-P : proporciona la contraseña del usuario

El usuario predeterminado en MSSQL es SA y la contraseña se proporcionó durante la instalación. A continuación se muestra una ejecución de muestra.

Una vez conectado al MSSQL Server 2022, ahora puede administrar las bases de datos.

a. Crear una base de datos en MSSQL

Para crear una base de datos en MSSQL Server 2022, use un comando con la siguiente sintaxis:

CREATE DATABASE [database_name];

Por ejemplo:

CREATE DATABASE sampledb;
GO

Una vez creada la base de datos, puede enumerar las bases de datos con el comando:

SELECT Name from sys.databases;
GO

Salida de muestra:

Crear tablas en MSSQL

Ahora en la base de datos anterior, podemos agregar tablas. Para esta demostración, agregaré una tabla simple llamada my_table:

USE sampledb;
CREATE TABLE my_table ( id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, age INT );
GO

Para insertar datos en la tabla:

INSERT INTO my_table (id, name, age)
VALUES (1, 'John', 25)
GO

Para ver los datos en la tabla:

SELECT id, name, age FROM my_table
GO

Salida de muestra:

Eliminar una base de datos|Tabla en MSSQL

También puede eliminar una base de datos o una tabla en MSSQL. Para eliminar una tabla, use el comando con la siguiente sintaxis:

DROP TABLE [table_name]
GO

Por ejemplo;

DROP TABLE my_table;
GO

Para eliminar una base de datos, use el comando con la siguiente sintaxis:

DROP DATABASE [database_name]
GO

Por ejemplo:

DROP DATABASE sampledb;
GO

3. Administrar usuarios y permisos en MSSQL Server 2022

De forma predeterminada, MSSQL viene con un usuario, el usuario administrador con el nombre de usuario "sa". Sin embargo, es posible crear otros usuarios y otorgarles permiso para las distintas bases de datos.

En MSSQL, gestión de usuarios, existen varios conceptos:

Iniciar sesión: se requiere un inicio de sesión para conectarse a la instancia del servidor MSSQL. Se utiliza para autenticar al usuario y otorgar permiso para acceder al motor de base de datos. Se puede crear un inicio de sesión utilizando SQL Server Management Studio (SSMS) o utilizando el comando T-SQL CREATE LOGIN.
Usuario: un usuario es una entidad de seguridad asociada a una base de datos específica. Se utiliza para otorgar permisos para acceder y modificar datos dentro de la base de datos. Se puede crear un usuario usando SSMS o usando el comando T-SQL CREAR USUARIO
Alcance: un inicio de sesión es un objeto a nivel de servidor que existe en la base de datos maestra y se puede utilizar para conectarse a cualquier base de datos en el servidor. Por otro lado, un usuario es un objeto a nivel de base de datos que existe en el contexto de una base de datos específica.
Autenticación: el servidor MSSQL autentica un inicio de sesión mediante la autenticación de Windows o la autenticación de SQL Server. Por el contrario, el motor de base de datos autentica a un usuario utilizando las credenciales de inicio de sesión.
Permisos: los inicios de sesión reciben permisos a nivel de servidor, mientras que a los usuarios se les otorgan permisos a nivel de base de datos. Esto significa que un usuario puede acceder a cualquier base de datos del servidor, pero un usuario sólo puede acceder a la base de datos específica a la que está asociado.

a. Crear un usuario de inicio de sesión en MSSQL

Como se explicó anteriormente, un usuario que inicia sesión se utiliza para autenticar a un usuario o una aplicación que se conecta al servidor de la base de datos.

Para crear un usuario de inicio de sesión, use el comando con la siguiente sintaxis:

CREATE LOGIN login_name WITH PASSWORD = 'password';
GO

Por ejemplo;

CREATE LOGIN testuser WITH PASSWORD = 'Passw0rd!';
GO

Una vez creado, puede usarlo para iniciar sesión en el servidor de base de datos MSSQL:

sqlcmd -S 127.0.0.1 -U testuser

Salida de muestra:

De forma predeterminada, el inicio de sesión no tiene ningún permiso para acceder a ninguna base de datos u objetos de bases de datos. Sin embargo, un usuario tiene la capacidad de conectarse al servidor de la base de datos y se le pueden otorgar permisos a nivel de servidor para realizar tareas administrativas, como crear bases de datos o administrar la configuración del servidor. Para asignar permisos, continúe y etiquete a un usuario.

Para eliminar un inicio de sesión, utilice:

DROP LOGIN [username]
GO

Para cambiar la contraseña de inicio de sesión, utilice:

ALTER LOGIN [login_name] WITH PASSWORD = 'new_password';
GO

Reemplace [login_name] con el nombre del inicio de sesión para el cual desea cambiar la contraseña y 'new_password' con la nueva contraseña que desea configurar.

b. Crear un usuario en MSSQL

Una vez que se ha creado un inicio de sesión, se debe asociar un inicio de sesión con un usuario de la base de datos para otorgarle a ese usuario acceso a bases de datos y objetos de base de datos específicos. Sin esta asociación, el usuario no podrá acceder a ninguna base de datos. Un usuario se utiliza para otorgar acceso a bases de datos y objetos de bases de datos específicos al inicio de sesión.

Ahora inicie sesión como usuario administrador y agregue un usuario al inicio de sesión creado anteriormente. La sintaxis del comando es:

CREATE USER user_name FOR LOGIN login_name;

Por ejemplo:

CREATE USER my_user FOR LOGIN testuser;
GO

Para eliminar un usuario, use el comando con la sintaxis:

DROP USER [username]
GO

Para cambiar la contraseña, utilice;

ALTER USER [userName] WITH PASSWORD = 'New Password'
GO

Ver los usuarios disponibles con el comando;

SELECT * FROM sys.database_principals WHERE Type = 'S'
GO

C. Conceder permisos a un inicio de sesión|Usuario en MSSQL

En MSSQL, hay varios roles de usuario integrados que se utilizan para administrar permisos y acceder a diversos recursos. Estos roles están diseñados para simplificar el proceso de otorgar permisos a usuarios y grupos. Estos son algunos de los roles de usuario más utilizados en MSSQL:

Los inicios de sesión pueden tener permisos y roles a nivel de servidor, como “sysadmin”, “securityadmin”, “dbcreator “ y “processadmin ”, mientras que los usuarios tienen permisos a nivel de base de datos. A un usuario se le puede conceder permiso para acceder a objetos de la base de datos, como tablas, vistas, procedimientos almacenados y funciones. A los usuarios se les pueden otorgar roles a nivel de base de datos, como "db_owner", "db_datareader", "db_datawriter", y "db_ddladmin". “

Para inicios de sesión: (Para otorgar estos permisos, utilice la base de datos master)
- SysAdmin: esta función tiene acceso administrativo completo a la instancia de MSSQL y puede realizar cualquier acción en el servidor.
- DbCreator: esta función puede crear, modificar y eliminar bases de datos.
- SecurityAdmin: esta función permite a los usuarios administrar inicios de sesión, usuarios y funciones.
- BackupOperator: esta función permite a los usuarios realizar copias de seguridad y restaurar una base de datos.
- BulkAdmin: esta función permite a los usuarios ejecutar operaciones de importación y exportación masivas.
- DDLAdmin: esta función permite a los usuarios crear, modificar y eliminar objetos de la base de datos, como tablas, vistas y procedimientos almacenados.
Para usuarios:
- DbOwner: esta función tiene acceso administrativo completo a una base de datos específica y puede realizar cualquier acción en esa base de datos.
- DataReader: esta función permite a los usuarios leer todos los datos de todas las tablas de usuarios en una base de datos.
- DataWriter: esta función permite a los usuarios insertar, actualizar y eliminar datos en todas las tablas de usuarios de una base de datos.

Se pueden agregar privilegios al usuario creado | Inicie sesión para poder otorgar permisos, primero conéctese al servidor MSSQL como usuario administrador;

sqlcmd -S 127.0.0.1 -U sa

Podemos usar el comando sp_addrolemember para otorgar los permisos. Puede agregar cualquiera de los roles anteriores para el usuario.

Otorgar rol de escritor de base de datos:

USE [database_name]
GO
EXEC sp_addrolemember 'db_datawriter', 'username'
GO

Otorgar rol de lector de base de datos:

USE [database_name]
GO
EXEC sp_addrolemember 'db_datareader', 'username'
GO

Concesión del rol dbcreator:

USE master
GO
EXEC sp_addsrvrolemember 'dbcreator',  'loginname';
GO

Otorgar rol de administrador de sistemas:

USE master
GO
EXEC sp_addsrvrolemember 'sysadmin', 'loginname'
GO

Para que los comandos anteriores funcionen, debe reemplazar database_name con el nombre real de la base de datos y username con el nombre de inicio de sesión real que desea otorgarle el rol.

Para demostrar si funciona, podemos agregar el rol DBCreator al usuario:

USE sampledb
CREATE USER  my_user FOR LOGIN testuser;
GO
EXEC sp_addrolemember 'db_datareader', 'my_user';
GO

Ver los roles agregados para el usuario;

1> EXEC sp_helpuser 'my_user';
2> GO
UserName       RoleName     LoginName        DefDBName    DefSchemaName UserID     SID                                                                                                                                                                         
-------------- ------------ ---------------- ------------ ------------- ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
my_user        public       testuser         master       dbo           7          0x138EB720C61D394F96CBD43E42A800E9                                                                                                                                          
1>

Una vez agregada, probaremos si somos capaces de crear y eliminar una base de datos:

d. Revocar permisos para iniciar sesión\Usuarios en MSSQL

Para revocar los permisos de los usuarios, utilice un comando con la siguiente sintaxis:

REVOKE [role] FROM [user_name];
GO

Por ejemplo:

REVOKE db_datawriter FROM [user_name];
GO

Para revocar los permisos de inicio de sesión, utilice:

ALTER SERVER ROLE [role] DROP MEMBER [login];

Por ejemplo:

ALTER SERVER ROLE dbcreator DROP MEMBER testuser;

Veredicto

Eso marca el final de esta guía sobre cómo administrar bases de datos, usuarios y permisos en MS SQL Server 2022. Espero que esto haya sido informativo.

Ver más en esta página:

Cómo configurar SSH y MySQL Bastion Server usando Warpgate

Instale Azure Data Studio en Rocky Linux 8|AlmaLinux 8

Ejecute Microsoft SQL Server 2022 en el contenedor Docker/Podman

Administrar bases de datos, usuarios y permisos en MSSQL Server 2022

Empezando

1. Instale las herramientas de línea de comandos de SQL Server

2. Administrar bases de datos en MSSQL Server 2022

a. Crear una base de datos en MSSQL

Crear tablas en MSSQL

Eliminar una base de datos|Tabla en MSSQL

3. Administrar usuarios y permisos en MSSQL Server 2022

a. Crear un usuario de inicio de sesión en MSSQL

b. Crear un usuario en MSSQL

C. Conceder permisos a un inicio de sesión|Usuario en MSSQL

d. Revocar permisos para iniciar sesión\Usuarios en MSSQL

Veredicto

Artículos relacionados