Cambiar contraseña de usuario SQL Azure

 Cambiar contraseña

Veamos cómo cambiar una contraseña usando la instrucción ALTER LOGIN en SQL Server, AZURE (Transact-SQL).

Por ejemplo:

ALTER LOGIN TUUSUARIO

WITH PASSWORD = 'CONTRASEÑA_NUEVA';

 

Cambiar contraseña y forzar cambio

Veamos cómo cambiar una contraseña y forzar el cambio de contraseña después del primer inicio de sesión mediante la instrucción ALTER LOGIN en SQL Server (Transact-SQL).

Por ejemplo:

ALTER LOGIN TUUSUARIO

WITH PASSWORD = 'CONTRASEÑA_NUEVA' MUST_CHANGE,

CHECK_EXPIRATION = ON;

 

Este ejemplo de ALTER LOGIN alteraría el inicio de sesión llamado TUUSUARIO y cambiaría la contraseña de este inicio de sesión a ‘CONTRASEÑA_NUEVA’. Pero debido a que hemos especificado la opción MUST CHANGE y establecido CHECK_EXPIRATION en ON, la contraseña deberá cambiarse nuevamente en SQL Server después del primer inicio de sesión (siguiendo la instrucción ALTER LOGIN). Entonces, en efecto, es como restablecer una contraseña a una contraseña temporal para un inicio de sesión.

 

Deshabilitar un inicio de sesión

A continuación, veamos cómo deshabilitar un inicio de sesión mediante la declaración ALTER LOGIN en SQL Server (Transact-SQL).

Por ejemplo:

ALTER LOGIN TUUSUARIO DISABLE

Este ejemplo de ALTER LOGIN deshabilitaría el inicio de sesión llamado TUUSUARIO.

 

Habilitar un inicio de sesión

A continuación, veamos cómo habilitar un inicio de sesión mediante la instrucción ALTER LOGIN en SQL Server (Transact-SQL).

 

Por ejemplo:

ALTER LOGIN TUUSUARIO ENABLE

Este ejemplo de ALTER LOGIN habilitaría el inicio de sesión llamado TUUSUARIO.

Cannot truncate table 'XXXX' because it is being referenced by a FOREIGN KEY constraint.

 Cannot truncate table 'esquema.tutabla' because it is being referenced by a FOREIGN KEY constraint

Error:

Msg 4712, Level 16, State 1, Line 1

Cannot truncate table 'esquema.tutabla' because it is being referenced by a FOREIGN KEY constraint.

Solucion, primero tienes que eliminar las dependecias hijas de la tabla, despues ejecuta el siguiente script:

unicamente tienes que llenar el esquema y el nombre de tu tabla a truncar

DECLARE @Debug bit = 0;

-- List of tables to truncate

select

    SchemaName, Name

into #tables

from (values 

    ('esquema', 'tutabla')

) as X(SchemaName, Name)

BEGIN TRANSACTION TruncateTrans;

with foreignKeys AS (

     SELECT 

        SCHEMA_NAME(fk.schema_id) as SchemaName

        ,fk.Name as ConstraintName

        ,OBJECT_NAME(fk.parent_object_id) as TableName

        ,SCHEMA_NAME(t.SCHEMA_ID) as ReferencedSchemaName

        ,OBJECT_NAME(fk.referenced_object_id) as ReferencedTableName

        ,fc.constraint_column_id

        ,COL_NAME(fk.parent_object_id, fc.parent_column_id) AS ColumnName

        ,COL_NAME(fk.referenced_object_id, fc.referenced_column_id) as ReferencedColumnName

        ,fk.delete_referential_action_desc

        ,fk.update_referential_action_desc

    FROM sys.foreign_keys AS fk

        JOIN sys.foreign_key_columns AS fc

            ON fk.object_id = fc.constraint_object_id

        JOIN #tables tbl 

            ON OBJECT_NAME(fc.referenced_object_id) = tbl.Name

        JOIN sys.tables t on OBJECT_NAME(t.object_id) = tbl.Name 

            and SCHEMA_NAME(t.schema_id) = tbl.SchemaName

            and t.OBJECT_ID = fc.referenced_object_id

)

select

    quotename(fk.ConstraintName) AS ConstraintName

    ,quotename(fk.SchemaName) + '.' + quotename(fk.TableName) AS TableName

    ,quotename(fk.ReferencedSchemaName) + '.' + quotename(fk.ReferencedTableName) AS ReferencedTableName

    ,replace(fk.delete_referential_action_desc, '_', ' ') AS DeleteAction

    ,replace(fk.update_referential_action_desc, '_', ' ') AS UpdateAction

    ,STUFF((

        SELECT ',' + quotename(fk2.ColumnName)

        FROM foreignKeys fk2 

        WHERE fk2.ConstraintName = fk.ConstraintName and fk2.SchemaName = fk.SchemaName

        ORDER BY fk2.constraint_column_id

        FOR XML PATH('')

    ),1,1,'') AS ColumnNames

    ,STUFF((

        SELECT ',' + quotename(fk2.ReferencedColumnName)

        FROM foreignKeys fk2 

        WHERE fk2.ConstraintName = fk.ConstraintName and fk2.SchemaName = fk.SchemaName

        ORDER BY fk2.constraint_column_id

        FOR XML PATH('')

    ),1,1,'') AS ReferencedColumnNames

into #FKs

from foreignKeys fk

GROUP BY fk.SchemaName, fk.ConstraintName, fk.TableName, fk.ReferencedSchemaName, fk.ReferencedTableName, fk.delete_referential_action_desc, fk.update_referential_action_desc

-- Drop FKs

select 

    identity(int,1,1) as ID,

    'ALTER TABLE ' + fk.TableName + ' DROP CONSTRAINT ' + fk.ConstraintName AS script

into #scripts

from #FKs fk

-- Truncate 

insert into #scripts

select distinct 

    'TRUNCATE TABLE ' + quotename(tbl.SchemaName) + '.' + quotename(tbl.Name) AS script

from #tables tbl

-- Recreate

insert into #scripts

select 

    'ALTER TABLE ' + fk.TableName + 

    ' WITH CHECK ADD CONSTRAINT ' + fk.ConstraintName + 

    ' FOREIGN KEY ('+ fk.ColumnNames +')' + 

    ' REFERENCES ' + fk.ReferencedTableName +' ('+ fk.ReferencedColumnNames +')' +

    ' ON DELETE ' + fk.DeleteAction COLLATE Latin1_General_CI_AS_KS_WS + ' ON UPDATE ' + fk.UpdateAction COLLATE Latin1_General_CI_AS_KS_WS AS script

from #FKs fk

DECLARE @script nvarchar(MAX);

DECLARE curScripts CURSOR FOR 

    select script

    from #scripts

    order by ID

OPEN curScripts

WHILE 1=1 BEGIN

    FETCH NEXT FROM curScripts INTO @script

    IF @@FETCH_STATUS != 0 BREAK;

    print @script;

    IF @Debug = 0

        EXEC (@script);

END

CLOSE curScripts

DEALLOCATE curScripts

drop table #scripts

drop table #FKs

drop table #tables

COMMIT TRANSACTION TruncateTrans;

Cambiar contraseña de usuario SQL Azure

El siguiente ejemplo cambia la contraseña de inicio de sesión UsuarioSQL una contraseña segura.

 ALTER LOGIN UsuarioSQL WITH PASSWORD = '<enterStrongPasswordHere>';

Cambiar la contraseña de un inicio de sesión al iniciar sesión como inicio de sesión

Si está intentando cambiar la contraseña del inicio de sesión con el que está conectado actualmente y no tiene el ALTER ANY LOGIN permiso, debe especificar la OLD_PASSWORD opción.

 ALTER LOGIN UsuarioSQL WITH PASSWORD = '<enterStrongPasswordHere>' OLD_PASSWORD = '<oldWeakPasswordHere>';

Uso de WITH (NOLOCK) SQL SERVER

 La sugerencia WITH (nolock) es un comando explícito dirigido a una tabla o vista específica que se usa para establecer el nivel de aislamiento de la transacción con respecto a la tabla o tablas dentro de una vista para una consulta. Una vez emitidos, los bloqueos no se utilizarán contra los datos dentro de la tabla. La ventaja de esto es que no hay posibilidad de que se produzca un punto muerto con cualquier otra consulta que se ejecute en la mesa. La otra ventaja indirecta es que se utilizará menos memoria para mantener bloqueos contra esos datos.

SELECT

first_name,

last_name,

FROM dbo.person p WITH (NOLOCK)

JOIN dbo.employee e WITH (NOLOCK)

ON e.person_id = p.person_id

WHERE p.person_id = 1;

La configuración de nolock anterior es explícita para la tabla contra la que se establece. Para establecer el valor globalmente para el alcance de la conexión, consulte ESTABLECER EL NIVEL DE AISLAMIENTO DE LA TRANSACCIÓN

Si bien la palabra clave "WITH" puede no ser necesaria en las versiones actuales de SQL Server, se recomienda encarecidamente su uso para la compatibilidad con versiones futuras.

Ventajas:

• Los bloqueos mutuos no ocurrirán frente a otras consultas que se ejecutan contra los mismos datos
• Se utiliza menos memoria debido a la falta de bloqueo de nivel de fila, página o rango
• Por lo general, permite una simultaneidad mucho mayor debido a una menor huella

Desventajas:

• Los datos no comprometidos se pueden leer dando lugar a lecturas sucias
• Las sugerencias explícitas contra una tabla son generalmente una mala práctica

Uso

En la mayoría de los lugares en los que he trabajado, con (nolock) ha sido una práctica generalmente aceptada en las áreas específicas del sistema que no son sensibles a la falta de sincronización de los datos. Sin embargo, es importante saber dónde pueden salir mal las cosas. La señal de alerta más grande que se me ocurre para no usar NOLOCK sería un sistema que usa transacciones explícitas (BEGIN TRAN ..END TRAN) o un uso intensivo de activadores. Varias declaraciones ejecutadas dentro de una transacción experimentan un retraso de tiempo en sus operaciones INSERT / UPDATE / DELETE, sin embargo, los cambios se confirman de una vez en COMMIT. Las declaraciones que consultan los datos modificados utilizando el nivel de aislamiento READ COMMITTED no podrán ver estos cambios hasta que se confirmen, mientras que READ UNCOMMITTED (NOLOCK) verá los cambios inmediatamente independientemente de cuándo se produzca la confirmación.

No use WITH (NOLOCK) sin comprender completamente las ramificaciones de una lectura sucia

Crear sp_who3 para mejor búsqueda de bloqueos, un mejor sp_who3

 El siguiente sp salio del blog 

https://sqlserverplanet.com/dba/a-better-sp_who2-using-dmvs-sp_who3

El siguiente código genera la misma información que se encuentra en sp_who2, junto con información adicional de solución de problemas. También contiene la instrucción SQL que se está ejecutando, por lo que en lugar de tener que ejecutar un DBCC INPUTBUFFER por separado, la instrucción que se está ejecutando se muestra en los resultados.

A diferencia de sp_who2, este sp_who3 personalizado solo muestra las sesiones que tienen una solicitud en ejecución actual.

Lo que también se muestra son las lecturas y escrituras del comando actual, junto con el número de lecturas y escrituras de todo el SPID. También muestra el protocolo que se está utilizando (TCP, NamedPipes o Shared Memory).

El bloqueador de clientes potenciales a continuación se mostrará en la columna BlkBy como -1.

create PROCEDURE [dbo].[Sp_who3]

AS

BEGIN

SET TRANSACTION isolation level READ uncommitted

SELECT SPID = er.session_id,

--,BlkBy = CASE WHEN lead_blocker = 1 THEN -1 ELSE er.blocking_session_id END,

ElapsedMS = er.total_elapsed_time,

CPU = er.cpu_time,

IOReads = er.logical_reads + er.reads,

IOWrites = er.writes,

Executions = ec.execution_count,

CommandType = er.command,

LastWaitType = er.last_wait_type,

ObjectName = Object_schema_name(qt.objectid, dbid) + '.'

+ Object_name(qt.objectid, qt.dbid),

SQLStatement = Substring (qt.text, er.statement_start_offset / 2, (

CASE

WHEN

er.statement_end_offset = -1 THEN

Len

(

CONVERT(NVARCHAR(max), qt.text)) *

2

ELSE

er.statement_end_offset

END -

er.statement_start_offset ) / 2),

STATUS = ses.status,

[Login] = ses.login_name,

Host = ses.host_name,

DBName = Db_name(er.database_id),

StartTime = er.start_time,

Protocol = con.net_transport,

transaction_isolation = CASE ses.transaction_isolation_level

WHEN 0 THEN 'Unspecified'

WHEN 1 THEN 'Read Uncommitted'

WHEN 2 THEN 'Read Committed'

WHEN 3 THEN 'Repeatable'

WHEN 4 THEN 'Serializable'

WHEN 5 THEN 'Snapshot'

END,

ConnectionWrites = con.num_writes,

ConnectionReads = con.num_reads,

ClientAddress = con.client_net_address,

Authentication = con.auth_scheme,

DatetimeSnapshot = Getdate(),

plan_handle = er.plan_handle

FROM sys.dm_exec_requests er

LEFT JOIN sys.dm_exec_sessions ses

ON ses.session_id = er.session_id

LEFT JOIN sys.dm_exec_connections con

ON con.session_id = ses.session_id

OUTER apply sys.Dm_exec_sql_text(er.sql_handle) AS qt

OUTER APPLY

(

SELECT execution_count = MAX(cp.usecounts)

FROM sys.dm_exec_cached_plans cp

WHERE cp.plan_handle = er.plan_handle

) ec

/*OUTER APPLY

(

SELECT

lead_blocker = 1

FROM master.dbo.sysprocesses sp

WHERE sp.spid IN (SELECT blocked FROM master.dbo.sysprocesses)

AND sp.blocked = 0

AND sp.spid = er.session_id

) lb*/

WHERE er.sql_handle IS NOT NULL

AND er.session_id != @@SPID

ORDER BY

--CASE WHEN lead_blocker = 1 THEN -1 * 1000 ELSE -er.blocking_session_id END,

er.blocking_session_id DESC,

er.logical_reads + er.reads DESC,

er.session_id

END

Se ejecuta:

SP_WHO3

Uso de sp_who2 SQL Azure

 sp_who2 SQL Azure

Una de las primeras líneas de defensa para determinar las causas de la ralentización de la base de datos es utilizar sp_who2. sp_who2 muestra todas las sesiones que están establecidas actualmente en la base de datos. Estos se indican como SPID o Id. De proceso del servidor. Ejecutar sp_who2 es fácil, todo lo que se requiere es escribir sp_who2 y ejecutarlo, sin embargo es una buena idea llamarlo con EXEC.

EXEC sp_who2

Los primeros 50 resultados son SPID del sistema . Generalmente, estos no afectan la ralentización del sistema. Estos eventos del sistema incluyen el escritor de Checkpoint, los escritores de registros y los programadores de tareas. Los procesos de usuario son números SPID 50 y superiores. Al diagnosticar ralentizaciones, son estos SPID los que acaparan los recursos potenciales.

sp_who2 también toma un parámetro opcional de un SPID. Si se pasa un spid, los resultados de sp_who2 solo muestran la fila o filas del SPID en ejecución.

Hay cuatro cosas principales a tener en cuenta al diagnosticar ralentizaciones.

1. Bloqueo
2. Alto uso de CPU
3. Uso elevado de IO
4. Varias entradas para el mismo SPID que representan el paralelismo.

Creación de usuarios e inicios de sesión de Azure SQL Database

 Este consejo rápido habla sobre la creación de usuarios e inicios de sesión de Azure SQL Database después de haber realizado una configuración inicial de su servidor de Azure SQL Database y haber definido el usuario administrador.

Entonces, si, por ejemplo, desea crear un nuevo inicio de sesión / usuario SQL con permisos db_owner en una base de datos, puede hacerlo conectándose al servidor de Azure SQL Database a través de SQL Server Management Studio o Azure Data Studio y crear el inicio de sesión y usuario con sentencias T-SQL.

Por ejemplo, si desea crear un nuevo inicio de sesión SQL en Azure SQL Database (es decir, llámelo " testLogin1 ") y desea convertir este inicio de sesión en un db_owner en la base de datos " TestDB1 ", entonces el proceso sería el siguiente:

Paso 1: Conéctese como administrador a su servidor de base de datos SQL de Azure

Conéctese a su servidor de Azure SQL Database como administrador a través de SQL Server Management Studio o Azure Data Studio desde las instalaciones.

 

Paso 2: cambie a la base de datos "maestra"

Cambie el contexto de la ventana de consulta a la base de datos "maestra" cambiando a la base de datos "maestra" desde el cuadro de selección de la base de datos en SQL Server Management Studio o Azure Data Studio.

 

Paso 3: Cree el inicio de sesión del servidor

Cree el inicio de sesión con la siguiente declaración T-SQL (puede cambiar, por supuesto, el nombre de inicio de sesión y la contraseña en consecuencia para usar una contraseña segura):

- crear inicio de sesión SQL en la base de datos maestra

CREAR INICIO DE SESIÓN testLogin1

WITH PASSWORD = '<Strong_Password_Goes_ Here>' ;

 

Paso 4: crear el usuario de la base de datos

En otra ventana de consulta en SSMS o Azure Data Studio que está conectado a su servidor de base de datos Azure SQL, seleccione la base de datos para la que desea crear el usuario (es decir, en este ejemplo "TestDB1") y conceda permisos (es decir, db_owner) y ejecute el debajo de las declaraciones de T-SQL (puede cambiar, por supuesto, el siguiente T-SQL para otorgar los permisos / roles de base de datos que desee):

- agregar usuario de base de datos para la prueba de inicio de sesión

CREAR USUARIO [testLogin1]

DESDE INICIO DE SESIÓN [testLogin1]

WITH DEFAULT_SCHEMA = dbo;

 

Paso 5: Otorgue permisos de base de datos al inicio de sesión / usuario

El último paso es otorgar el inicio de sesión con los permisos de base de datos deseados. En el siguiente ejemplo, otorgaré permisos de db_owner para testLogin1:

- agregar usuario a la (s) función (es) de la base de datos (es decir, db_owner)

ALTER ROLE db_owner AÑADIR MIEMBRO [testLogin1];

 

Detectar consultas que producen bloqueos SQL SERVER

Los procesos bloqueados causados por bloqueos en los objetos de la base de datos son un problema común. El bloqueo asegura la integridad de los datos almacenados dentro de las bases de datos forzando cada transacción SQL Server ejecutada a pasar la prueba ACID que considera que cada transacción debe cumplir con los siguientes requerimientos:

• Atomicidad –una transacción debe ser totalmente completada cuando se ejecuta, o no se ejecutará.
• Consistencia – las transacciones deben crear un nuevo estado de datos que sea válido, o si cualquier fallo ocurre, debe retornar todos los datos al estado que estaba antes de que la transacción sea ejecutada/iniciada.
• Aislamiento – una transacción que está en proceso de ejecución y no ha sido enviada aún, debe ser aislada de todas las otras transacciones.
• Durabilidad  – los datos enviados deben ser grabados en una forma que mantendrá los datos en un estado correcto si la transacción no se completa, en el evento de una falla o un reinicio del sistema.

Como parte de la prueba ACID, el aislamiento es realizado bloqueando objetos y, como tal, el aislamiento puede afectar una o más filas de datos hasta que la transacción SQL Server que es ejecutada contra ese objeto sea enviada. Esto significa, en la práctica que, bloqueando el objeto, SQL Server evitará que cualquier otro proceso haga algún cambio contra los datos en ese objeto hasta que el bloqueo sea removido, ya sea enviando el cambio o retrotrayéndolo.

Script 1

SELECT blocking_session_id, wait_duration_ms, session_id
FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id IS NOT NULL


SELECT t.text,*
FROM sys.dm_exec_connections c
CROSS APPLY sys.dm_exec_sql_text (c.most_recent_sql_handle) t
WHERE c.session_id in
(
SELECT  session_id
FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id IS NOT NULL
)