Foros del Web » Programación para mayores de 30 ;) » Bases de Datos General » SQL Server »

[SOLUCIONADO] Administrar Usuarios SQL

Estas en el tema de Administrar Usuarios SQL en el foro de SQL Server en Foros del Web. 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 ...
  #1 (permalink)  
Antiguo 11/10/2017, 09:01
Avatar de senseeye3led  
Fecha de Ingreso: abril-2016
Ubicación: 127.0.0.1
Mensajes: 95
Antigüedad: 1 año, 8 meses
Puntos: 3
Pregunta Administrar Usuarios SQL

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
  #2 (permalink)  
Antiguo 11/10/2017, 09:29
Avatar de Libras
Colaborador
 
Fecha de Ingreso: agosto-2006
Ubicación: En la hermosa perla de occidente
Mensajes: 7.135
Antigüedad: 11 años, 3 meses
Puntos: 731
Respuesta: Administrar Usuarios SQL

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
__________________
What does an execution plan say to t-sql query? Go f**k yourself, if you are not happy with me
  #3 (permalink)  
Antiguo 11/10/2017, 15:49
Avatar de iislas
Colaborador
 
Fecha de Ingreso: julio-2007
Ubicación: Mexico, D.F.
Mensajes: 6.477
Antigüedad: 10 años, 5 meses
Puntos: 180
Respuesta: Administrar Usuarios SQL

Checa este query

Código SQL:
Ver original
  1. /*
  2. Security Audit Report
  3. 1) List all access provisioned to a sql user or windows user/group directly
  4. 2) List all access provisioned to a sql user or windows user/group through a database or application role
  5. 3) List all access provisioned to the public role
  6.  
  7. Columns Returned:
  8. UserName        : SQL or Windows/Active Directory user cccount.  This could also be an Active Directory group.
  9. UserType        : Value will be either 'SQL User' or 'Windows User'.  This reflects the type of user defined for the
  10.                   SQL Server user account.
  11. DatabaseUserName: Name of the associated user as defined in the database user account.  The database user may not be the
  12.                   same as the server user.
  13. Role            : The role name.  This will be null if the associated permissions to the object are defined at directly
  14.                   on the user account, otherwise this will be the name of the role that the user is a member of.
  15. PermissionType  : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT
  16.                   DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc.
  17.                   This value may not be populated for all roles.  Some built in roles have implicit permission
  18.                   definitions.
  19. PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc.
  20.                   This value may not be populated for all roles.  Some built in roles have implicit permission
  21.                   definitions.
  22. ObjectType      : Type of object the user/role is assigned permissions on.  Examples could include USER_TABLE,
  23.                   SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc.  
  24.                   This value may not be populated for all roles.  Some built in roles have implicit permission
  25.                   definitions.          
  26. ObjectName      : Name of the object that the user/role is assigned permissions on.  
  27.                   This value may not be populated for all roles.  Some built in roles have implicit permission
  28.                   definitions.
  29. ColumnName      : Name of the column of the object that the user/role is assigned permissions on. This value
  30.                   is only populated if the object is a table, view or a table value function.                
  31. */
  32.  
  33. --List all access provisioned to a sql user or windows user/group directly
  34. SELECT  
  35.     [UserName] = CASE princ.[TYPE]
  36.                     WHEN 'S' THEN princ.[name]
  37.                     WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
  38.                  END,
  39.     [UserType] = CASE princ.[TYPE]
  40.                     WHEN 'S' THEN 'SQL User'
  41.                     WHEN 'U' THEN 'Windows User'
  42.                  END,  
  43.     [DatabaseUserName] = princ.[name],      
  44.     [ROLE] = NULL,      
  45.     [PermissionType] = perm.[permission_name],      
  46.     [PermissionState] = perm.[state_desc],      
  47.     [ObjectType] = obj.type_desc,--perm.[class_desc],      
  48.     [ObjectName] = OBJECT_NAME(perm.major_id),
  49.     [ColumnName] = col.[name]
  50. FROM    
  51.     --database user
  52.     sys.database_principals princ  
  53. LEFT JOIN
  54.     --Login accounts
  55.     sys.login_token ulogin ON princ.[sid] = ulogin.[sid]
  56. LEFT JOIN        
  57.     --Permissions
  58.     sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
  59. LEFT JOIN
  60.     --Table columns
  61.     sys.COLUMNS col ON col.[object_id] = perm.major_id
  62.                     AND col.[column_id] = perm.[minor_id]
  63. LEFT JOIN
  64.     sys.objects obj ON perm.[major_id] = obj.[object_id]
  65. WHERE
  66.     princ.[TYPE] IN ('S','U')
  67. UNION
  68. --List all access provisioned to a sql user or windows user/group through a database or application role
  69. SELECT  
  70.     [UserName] = CASE memberprinc.[TYPE]
  71.                     WHEN 'S' THEN memberprinc.[name]
  72.                     WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
  73.                  END,
  74.     [UserType] = CASE memberprinc.[TYPE]
  75.                     WHEN 'S' THEN 'SQL User'
  76.                     WHEN 'U' THEN 'Windows User'
  77.                  END,
  78.     [DatabaseUserName] = memberprinc.[name],  
  79.     [ROLE] = roleprinc.[name],      
  80.     [PermissionType] = perm.[permission_name],      
  81.     [PermissionState] = perm.[state_desc],      
  82.     [ObjectType] = obj.type_desc,--perm.[class_desc],  
  83.     [ObjectName] = OBJECT_NAME(perm.major_id),
  84.     [ColumnName] = col.[name]
  85. FROM    
  86.     --Role/member associations
  87.     sys.database_role_members members
  88. JOIN
  89.     --Roles
  90.     sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
  91. JOIN
  92.     --Role members (database users)
  93.     sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
  94. LEFT JOIN
  95.     --Login accounts
  96.     sys.login_token ulogin ON memberprinc.[sid] = ulogin.[sid]
  97. LEFT JOIN        
  98.     --Permissions
  99.     sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
  100. LEFT JOIN
  101.     --Table columns
  102.     sys.COLUMNS col ON col.[object_id] = perm.major_id
  103.                     AND col.[column_id] = perm.[minor_id]
  104. LEFT JOIN
  105.     sys.objects obj ON perm.[major_id] = obj.[object_id]
  106. UNION
  107. --List all access provisioned to the public role, which everyone gets by default
  108. SELECT  
  109.     [UserName] = '{All Users}',
  110.     [UserType] = '{All Users}',
  111.     [DatabaseUserName] = '{All Users}',      
  112.     [ROLE] = roleprinc.[name],      
  113.     [PermissionType] = perm.[permission_name],      
  114.     [PermissionState] = perm.[state_desc],      
  115.     [ObjectType] = obj.type_desc,--perm.[class_desc],  
  116.     [ObjectName] = OBJECT_NAME(perm.major_id),
  117.     [ColumnName] = col.[name]
  118. FROM    
  119.     --Roles
  120.     sys.database_principals roleprinc
  121. LEFT JOIN        
  122.     --Role permissions
  123.     sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
  124. LEFT JOIN
  125.     --Table columns
  126.     sys.COLUMNS col ON col.[object_id] = perm.major_id
  127.                     AND col.[column_id] = perm.[minor_id]                  
  128. JOIN
  129.     --All objects  
  130.     sys.objects obj ON obj.[object_id] = perm.[major_id]
  131. WHERE
  132.     --Only roles
  133.     roleprinc.[TYPE] = 'R' AND
  134.     --Only public role
  135.     roleprinc.[name] = 'public' AND
  136.     --Only objects of ours, not the MS objects
  137.     obj.is_ms_shipped = 0
  138. ORDER BY
  139.     princ.[Name],
  140.     OBJECT_NAME(perm.major_id),
  141.     col.[name],
  142.     perm.[permission_name],
  143.     perm.[state_desc],
  144.     obj.type_desc--perm.[class_desc]
__________________
MCTS Isaias Islas
  #4 (permalink)  
Antiguo 13/10/2017, 02:06
Avatar de senseeye3led  
Fecha de Ingreso: abril-2016
Ubicación: 127.0.0.1
Mensajes: 95
Antigüedad: 1 año, 8 meses
Puntos: 3
Pregunta Respuesta: Administrar Usuarios SQL

@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?

Última edición por senseeye3led; 13/10/2017 a las 07:01 Razón: añadir informacion
  #5 (permalink)  
Antiguo 13/10/2017, 08:37
Avatar de Libras
Colaborador
 
Fecha de Ingreso: agosto-2006
Ubicación: En la hermosa perla de occidente
Mensajes: 7.135
Antigüedad: 11 años, 3 meses
Puntos: 731
Respuesta: Administrar Usuarios SQL

que rol tiene ese usuario? aqui recuerda que tambien hay roles
__________________
What does an execution plan say to t-sql query? Go f**k yourself, if you are not happy with me
  #6 (permalink)  
Antiguo 16/10/2017, 05:14
Avatar de senseeye3led  
Fecha de Ingreso: abril-2016
Ubicación: 127.0.0.1
Mensajes: 95
Antigüedad: 1 año, 8 meses
Puntos: 3
Respuesta: Administrar Usuarios SQL

Cita:
Iniciado por Libras Ver Mensaje
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.
  #7 (permalink)  
Antiguo 16/10/2017, 08:40
Avatar de Libras
Colaborador
 
Fecha de Ingreso: agosto-2006
Ubicación: En la hermosa perla de occidente
Mensajes: 7.135
Antigüedad: 11 años, 3 meses
Puntos: 731
Respuesta: Administrar Usuarios SQL

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
__________________
What does an execution plan say to t-sql query? Go f**k yourself, if you are not happy with me
  #8 (permalink)  
Antiguo 16/10/2017, 10:24
Avatar de senseeye3led  
Fecha de Ingreso: abril-2016
Ubicación: 127.0.0.1
Mensajes: 95
Antigüedad: 1 año, 8 meses
Puntos: 3
Respuesta: Administrar Usuarios SQL

Cita:
Iniciado por Libras Ver Mensaje
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"?
  #9 (permalink)  
Antiguo 16/10/2017, 11:19
Avatar de Libras
Colaborador
 
Fecha de Ingreso: agosto-2006
Ubicación: En la hermosa perla de occidente
Mensajes: 7.135
Antigüedad: 11 años, 3 meses
Puntos: 731
Respuesta: Administrar Usuarios SQL

asi es :) y los accesos a las bases de datos los administras desde cada base
__________________
What does an execution plan say to t-sql query? Go f**k yourself, if you are not happy with me



La zona horaria es GMT -6. Ahora son las 09:36.