MongoDB Permissions

We can grant permissions in mongodb using Robo 3T GUI depends on user request. Below are the permission levels exist in MongoDB.

Note: If you are granting access on admin database then user will get same access across all databases automatically so be careful while you are granting access at admin database level.

If user request access on specific database then you have to chose that database name using User Source :  and permission types are remains same.

Robo 3T MongoDB Management Tool

Robo 3T is free and open source GUI tool used to manage MongoDB DBA and Development activities and you can do below activities irrespective of platform such as Windows , Linux and Unix Flavours .

- Schedule MongoDB tasks

- Write SQL to query MongoDB

- Import multiple SQL tables to a single MongoDB collection

- Enjoy rich query autocompletion

- Build queries via drag-and-drop

- Import and export in various formats (CSV, JSON, SQL, BSON/mongodump)

- Break down aggregation queries into stages

- Generate driver code in five languages

- Compare collections and view differences side-by-side

- Explore data schema and find outliers 

You can down load Robo 3T from https://robomongo.org/download

How to find object level permissions of single user in SQL Server ?

DECLARE @Username VARCHAR(100)

SET @Username = 'UserName'

SELECT *
FROM (
 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
 INNER JOIN
  --Roles
  sys.database_principals roleprinc

 ON roleprinc.[principal_id] = members.[role_principal_id]
 INNER 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]
 INNER 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
 ) a
WHERE username = @Username

How to get MAXDOP setting recommendations using SQL Query

MAXDOP setting recommendations : 

DECLARE @hyperthreadingRatio BIT;
DECLARE @logicalCPUs INT;
DECLARE @HTEnabled INT;
DECLARE @physicalCPU INT;
DECLARE @SOCKET INT;
DECLARE @logicalCPUPerNuma INT;
DECLARE @NoOfNUMA INT;

SELECT @logicalCPUs = cpu_count -- [Logical CPU Count]
 ,@hyperthreadingRatio = hyperthread_ratio --  [Hyperthread Ratio]
 ,@physicalCPU = cpu_count / hyperthread_ratio -- [Physical CPU Count]
 ,@HTEnabled = CASE WHEN cpu_count > hyperthread_ratio THEN 1 ELSE 0 END -- HTEnabled
FROM sys.dm_os_sys_info
OPTION (RECOMPILE);

SELECT @logicalCPUPerNuma = Count(parent_node_id)
-- [NumberOfLogicalProcessorsPerNuma]
FROM sys.dm_os_schedulers
WHERE [status] = 'VISIBLE ONLINE'
 AND parent_node_id < 64
GROUP BY parent_node_id
OPTION (RECOMPILE);

SELECT @NoOfNUMA = Count(DISTINCT parent_node_id)
FROM sys.dm_os_schedulers -- find NO OF NUMA Nodes 
WHERE [status] = 'VISIBLE ONLINE'
 AND parent_node_id < 64

-- Report the recommendations ....
SELECT
 --- 8 or less processors and NO HT enabled
 CASE WHEN @logicalCPUs < 8
   AND @HTEnabled = 0 THEN 'MAXDOP setting should be : ' 

+ Cast(@logicalCPUs AS VARCHAR(3))
    --- 8 or more processors and NO HT enabled
  WHEN @logicalCPUs >= 8
   AND @HTEnabled = 0 THEN 'MAXDOP setting should be : 8'
    --- 8 or more processors and HT enabled and NO NUMA
  WHEN @logicalCPUs >= 8
   AND @HTEnabled = 1
   AND @NoOfNUMA = 1 THEN 'MaxDop setting should be : ' 

+ Cast(@logicalCPUPerNuma / @physicalCPU AS VARCHAR(3))
    --- 8 or more processors and HT enabled and NUMA
  WHEN @logicalCPUs >= 8
   AND @HTEnabled = 1
   AND @NoOfNUMA > 1 THEN 'MaxDop setting should be : ' 

+ Cast(@logicalCPUPerNuma / @physicalCPU AS VARCHAR(3)) ELSE '' END AS Recommendations;

Generate table definition to match query results

I used to do below code during code tuning like instead of loading data into temp table from select statement directly I used to create temp table structure from the table or query results by passing into @query variable and then use insert into temp table for select query results.

SET NOCOUNT ON;

DECLARE @Query VARCHAR(MAX) = 'select * from dbname.schemaname.tablename;';
DECLARE @TempTableName VARCHAR(128) = '#temptable'
DECLARE @ColumnList VARCHAR(MAX);

SELECT @ColumnList = STUFF((
   SELECT '    , ' + name + ' ' + system_type_name + ' ' 

+ CASE is_nullable WHEN 0 THEN 'NOT NULL' ELSE 'NULL' END + CHAR(10)
   FROM sys.dm_exec_describe_first_result_set(@Query, NULL, 0)
   FOR XML PATH('')
   ), 1, 1, '');

PRINT 'CREATE TABLE ' + @TempTableName + '('
PRINT @ColumnList;
PRINT (');');