Usuarios de base de datos en SQL Server

Usuarios de Base de datos

Conceptos

El usuario de la base de datos es la identidad del inicio de sesión cuando está conectado a una base de datos.
El usuario de la base de datos puede utilizar el mismo nombre que el inicio de sesión, pero no es necesario.
El usuario es la entidad de seguridad a la que se les puede asignar permisos sobre los objetos de la base de datos.
Un inicio de sesión se puede asignar a bases de datos diferentes como usuarios diferentes pero solo se puede asignar como un usuario en cada base de datos.
Se pueden crear usuarios de base de datos sin inicios de sesión, estos no podrán iniciar sesión pero si se pueden asignar permisos.

Crear Usuarios

Instrucción: Create user
Permite crear un usuario de base de datos.
Sintaxis:

Para crear un usuario en base a un inicio de sesión de SQL Server

Create user NombreUsuario from Login InicioSesion

Para crear un usuario en base a una cuenta de Windows

Create user NombreUsuario for Login UsuarioWindows

Para especificar el usuario de Windows se escribe el nombre del equipo, el backslash y el nombre del usuario. El siguiente código crea un usuario de base de datos Fernando en base al usuarios de Windows FernandoLuque del servidor LondonPRO

CREATE USER [Fernando] FOR LOGIN [LondonPRO\FernandoLuque]
GO

Procedimiento almacenado sp_adduser

Permite añadir un usuario a la base de datos
Sintaxis:

sp_adduser [ @loginame = ] ‘login’
[ , [ @name_in_db = ] ‘user’ ]
[ , [ @grpname = ] ‘rol_de_BaseDatos’ ]

Donde:
@loginame = ‘login’ especifica el nombre del inicio de sesión.
@name_in_db = ‘user’ especifica el nombre del usuario a crear.
@grpname = ‘rol_de_BaseDatos’ especifica el nombre del rol de base de datos del que se hace miembro.

Modificar el usuario

Instrucción: Alter user
Permite la modificación de un usuario, donde es posible cambiar el nombre, asignarle un login o inicio de sesión y cambiarle el password.
Sintaxis:

ALTER USER userName
WITH NAME = NuevoNombre
| LOGIN = NombreLogin
| PASSWORD = ‘password’ [ OLD_PASSWORD = ‘oldpassword’ ]

Donde:
WITH NAME = NuevoNombre permite especificar el nuevo nombre del usuario.
LOGIN = NombreLogin permite asignar un login al usuario.
PASSWORD = ‘password’ [ OLD_PASSWORD = ‘oldpassword’ ] permite cambiar el password al usuario.

Eliminar usuarios

Instrucción: Drop user
Permite eliminar un usuario de base de datos.
Sintaxis:

Drop user NombreUsuario

Consideraciones:

El usuario no se puede eliminar si se encuentra iniciada su sesión.
El usuario no se puede eliminar si es propietario de alguna base de datos.
El usuario no se puede eliminar si la cuenta está deshabilitada.

Ejemplos

Ejercicio 1
Crear un usuario para Northwind llamado Capataz en base al mismo login
Asignar permisos de Lectura, Inserción y Actualización en toda la BD

use master
go
Create login Capataz with password = ‘123’
go
use Northwind
go
Create user Capataz from login Capataz
go
grant select, Insert, Update to Capataz
Deny delete to Capataz
go

Ejercicio 2
Crear un usuario en base a la cuenta de Windows, el servidor es OneServer y la cuenta de inicio de Windows es TeamServer, asignarle el esquema db_ddladmin

use Northwind
go
Create user TrainerSQL FOR LOGIN [OneServer\TeamServer] WITH DEFAULT_SCHEMA=[db_ddladmin]
GO

Ejercicio 3
Crear en AdventureWorks un usuario AsistenteRH en base a un login del mismo nombre que tenga permisos de lectura y escritura en el esquema Person

use master
go
create login AsistenteRH with password = ‘123’
go
use AdventureWorks
go
create user AsistenteRH from login AsistenteRH
go
— Permisos
Grant Select, Update on Schema::Person to AsistenteRH
Deny Insert, Delete on Schema::Person to AsistenteRH
go

Importante: Note que el login se utiliza para conectarse a SQL Server y el usuarios de
base de datos es utilizado para asignar los permisos sobre los asegurables.

Ejercicio 4
Crear un usuario en AdventureWorks llamado Vendedor en base al mismo login y asignar permisos de lectura, escritura y modificación en el esquema Sales.

use master
go
sp_addlogin Vendedor, 123
go
use AdventureWorks
go
sp_adduser Vendedor, Vendedor
go
— Los permisos
Grant select, Insert, Update on schema::Sales to Vendedor
Deny Delete on schema::Sales to Vendedor
go

Ejercicio 5
Listar los usuarios de la base de datos AdventureWorks

use AdventureWorks
go
select name, principal_id, type, type_desc,
default_schema_name from sys.database_principals where type = ‘S’
go

Ejercicio 6
Crear un usuario JefeRecursos en AdventureWorks
en base al mismo login y asignar permisos en el esquema HumanResources.
Permisos: Select, Insert, Update, Denegar: Delete

use master
go
Create login JefeRecursos with password = ‘123’
go
use AdventureWorks
go
create user JefeRecursos from login JefeRecursos
go
— Permisos
Grant Select, Insert, Update on schema::HumanResources to JefeRecursos
Deny Delete on schema::HumanResources to JefeRecursos
go

Ejercicio 7
Crear un usuario UserCopias en base al login AltaDisponibilidad y hacerlo miembro de db_backupoperator

use master
go
Create login AltaDisponibilidad with password = ‘123’
go
use AdventureWorks
go
create user UserCopias from login AltaDisponibilidad
go
Alter role db_backupoperator add member UserCopias
go

Ejercicio 8
Usuario en base al de Windows – TrainerSQLWin. Se supone que el usuario de Windows existe. El equipo donde se creará es ServerLondres

USE [Northwind]
GO
CREATE USER [Jose] FOR LOGIN [ServerLondres\TrainerSQLWin]
WITH DEFAULT_SCHEMA=[dbo]
GO