[SOLUCIONADO] Administrar Usuarios SQL

[senseeye3led]

Tengo distintos usuarios que conectan a un motor MS SQL.

Un usuario es a nivel de sistema Windows y otro es parecido a 'sa'; supongo que este último es un usuario SQL.

¿Cómo puedo ver qué permisos tienen los usuarios SQL?

Me gustaría poder limitar los permisos que tiene ese usuario SQL y el acceso a las DB. Pese intentar que el usuario SQL sólo pueda acceder a 1 DB concreta puede acceder al resto

[Libras]

Dpende del nivel de permisos que tenga el usuario, si es un usuario 'sa' este esta como sysdmin, aunque le digas que no puede entrar a una base va a poder entrar a todas las demas jejejeje, aqui tendrias que ver cuantos "logins" tienes y cuantos "usuarios" de base de datos tienes y como estan las relaciones de estos.

Un login es un usuario que se puede conectar al servidor, y un usuario es como se le conoce al login dentro de una base de datos.

Para saber los roles, tienes las vistas de sistema

Vistas aqui

cualquier pregunta por aqui estamos

[iislas]

Checa este query

Código SQL:

/*
Security Audit Report
1) List all access provisioned to a sql user or windows user/group directly 
2) List all access provisioned to a sql user or windows user/group through a database or application role
3) List all access provisioned to the public role
 
Columns Returned:
UserName        : SQL or Windows/Active Directory user cccount.  This could also be an Active Directory group.
UserType        : Value will be either 'SQL User' or 'Windows User'.  This reflects the type of user defined for the 
                  SQL Server user account.
DatabaseUserName: Name of the associated user as defined in the database user account.  The database user may not be the
                  same as the server user.
Role            : The role name.  This will be null if the associated permissions to the object are defined at directly
                  on the user account, otherwise this will be the name of the role that the user is a member of.
PermissionType  : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT
                  DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc.
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc.
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
ObjectType      : Type of object the user/role is assigned permissions on.  Examples could include USER_TABLE, 
                  SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc.   
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.          
ObjectName      : Name of the object that the user/role is assigned permissions on.  
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
ColumnName      : Name of the column of the object that the user/role is assigned permissions on. This value
                  is only populated if the object is a table, view or a table value function.                 
*/
 
--List all access provisioned to a sql user or windows user/group directly 
SELECT  
    [UserName] = CASE princ.[TYPE] 
                    WHEN 'S' THEN princ.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE princ.[TYPE]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END,  
    [DatabaseUserName] = princ.[name],       
    [ROLE] = NULL,      
    [PermissionType] = perm.[permission_name],       
    [PermissionState] = perm.[state_desc],       
    [ObjectType] = obj.type_desc,--perm.[class_desc],       
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM    
    --database user
    sys.database_principals princ  
LEFT JOIN
    --Login accounts
    sys.login_token ulogin ON princ.[sid] = ulogin.[sid]
LEFT JOIN        
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN
    --Table columns
    sys.COLUMNS col ON col.[object_id] = perm.major_id 
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
WHERE 
    princ.[TYPE] IN ('S','U')
UNION
--List all access provisioned to a sql user or windows user/group through a database or application role
SELECT  
    [UserName] = CASE memberprinc.[TYPE] 
                    WHEN 'S' THEN memberprinc.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE memberprinc.[TYPE]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END, 
    [DatabaseUserName] = memberprinc.[name],   
    [ROLE] = roleprinc.[name],      
    [PermissionType] = perm.[permission_name],       
    [PermissionState] = perm.[state_desc],       
    [ObjectType] = obj.type_desc,--perm.[class_desc],   
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM    
    --Role/member associations
    sys.database_role_members members
JOIN
    --Roles
    sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
JOIN
    --Role members (database users)
    sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
LEFT JOIN
    --Login accounts
    sys.login_token ulogin ON memberprinc.[sid] = ulogin.[sid]
LEFT JOIN        
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
    --Table columns
    sys.COLUMNS col ON col.[object_id] = perm.major_id 
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
UNION
--List all access provisioned to the public role, which everyone gets by default
SELECT  
    [UserName] = '{All Users}',
    [UserType] = '{All Users}', 
    [DatabaseUserName] = '{All Users}',       
    [ROLE] = roleprinc.[name],      
    [PermissionType] = perm.[permission_name],       
    [PermissionState] = perm.[state_desc],       
    [ObjectType] = obj.type_desc,--perm.[class_desc],  
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM    
    --Roles
    sys.database_principals roleprinc
LEFT JOIN        
    --Role permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
    --Table columns
    sys.COLUMNS col ON col.[object_id] = perm.major_id 
                    AND col.[column_id] = perm.[minor_id]                   
JOIN 
    --All objects   
    sys.objects obj ON obj.[object_id] = perm.[major_id]
WHERE
    --Only roles
    roleprinc.[TYPE] = 'R' AND
    --Only public role
    roleprinc.[name] = 'public' AND
    --Only objects of ours, not the MS objects
    obj.is_ms_shipped = 0
ORDER BY
    princ.[Name],
    OBJECT_NAME(perm.major_id),
    col.[name],
    perm.[permission_name],
    perm.[state_desc],
    obj.type_desc--perm.[class_desc]
 

[senseeye3led]

@iislas gracias por el script puedo ver la información bastante más ordenada :)

@Libras una duda que tengo es si realmente un "login de conexión" y "un usuario de sql" es lo mismo. Entiendo que realizas una conexión con un usuario y accedes con éste a los datos, por lo que entiendo que viene a ser lo mismo.

Una consulta que tengo es, mediante la ejecución del script para distintas bases de datos, cómo es normal consigo distintos resultados.

Por ejemplo en base_de_datos_numeros consigo usuarios que están formados por números. En base_de_datos_letras consigo usuarios que están formados por letras.
Pero si entro con un usuario formado por letras en la base_de_datos_numeros puedo ver la información. Eso es lo que me extraña, puesto que si ejecuto el script en la base_de_datos_numeros no me muestra esos usuarios.

Por otro lado, a un usuario sólo le he dejado los siguientes roles:

db_datareader
db_denydatareader
db_denydatawriter

Por lo que entiendo, este usuario no debería de ser capaz de escribir en la base de datos, únicamente quiero que pueda consultar la información. Me conecto con este usuario a la base de datos y soy capaz de modificar la información. ¿Qué se me esta escapando?

[Libras]

que rol tiene ese usuario? aqui recuerda que tambien hay roles

[senseeye3led]

Iniciado por Libras

que rol tiene ese usuario? aqui recuerda que tambien hay roles

Veo que db_datareader,db_denydatareader y db_denydatawriter son roles de la base de datos en el cual metes usuarios.

Cómo puedo ver el rol que tiene el usuario? (A fin de cuentas ¿cual puede ser la diferencia entre roles de bases de datos y roles de usuarios?)

Por otro lado, añado un enlace http://go.microsoft.com/fwlink/?LinkId=229142 con un PDF bastante explicativo sobre permisos en MS SQL SERVER 2017 y AZURE SQL. (Mi casuistica en es un motor bastante más antiguo).

Saludos y muchísimas gracias por las explicaciones.

[Libras]

Existen roles de servidor y roles de bases de datos, en los roles de servidor tenemos el sysadmin(que tiene permisos para ver todo), el diskadmin(para operaciones de discos), el bkpadmin(para la creacion de bkps) etc etc, como ves estos roles son mas "poderosos" a nivel servidor ya que te dan permisos para hacer mas cosas, los roles de bases de datos son mas a nivel base de datos, no te dejan crear objetos en el servidor. ahora si tu usuario tiene un sysadmin podra entrar a todas las bases de datos a menos que les pongas deny a todas

[senseeye3led]

Iniciado por Libras

Existen roles de servidor y roles de bases de datos, en los roles de servidor tenemos el sysadmin(que tiene permisos para ver todo), el diskadmin(para operaciones de discos), el bkpadmin(para la creacion de bkps) etc etc, como ves estos roles son mas "poderosos" a nivel servidor ya que te dan permisos para hacer mas cosas, los roles de bases de datos son mas a nivel base de datos, no te dejan crear objetos en el servidor. ahora si tu usuario tiene un sysadmin podra entrar a todas las bases de datos a menos que les pongas deny a todas

Entonces en el rol de servidor únicamente debería dejarle "public"?

[Libras]

asi es :) y los accesos a las bases de datos los administras desde cada base