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 (');');

Find how large replicated transactions are ?.

USE distribution
GO

SELECT Getdate() AS CaptureTime
 ,Object_name(t.object_id) AS TableName
 ,st.row_count
 ,s.NAME
FROM sys.dm_db_partition_stats st WITH (NOLOCK)
INNER JOIN sys.tables t WITH (NOLOCK) ON st.object_id = t.object_id
INNER JOIN sys.schemas s WITH (NOLOCK) ON t.schema_id = s.schema_id
WHERE index_id < 2
 AND Object_name(t.object_id) IN (
  'MSsubscriptions'
  ,'MSdistribution_history'
  ,'MSrepl_commands'
  ,'MSrepl_transactions'
  )
ORDER BY st.row_count DESC

Script to fine row count of each table available in a database

The below script will useful to find row count of tables along with creation date.

SELECT SCHEMA_NAME(st.schema_id) AS [SchemaName]
 ,[Tables].name AS [TableName]
 ,st.create_date
 ,SUM([Partitions].[rows]) AS [TotalRowCount]
FROM sys.tables AS [Tables]
INNER JOIN sys.partitions AS [Partitions] ON [Tables].[object_id] = [Partitions].[object_id]
 AND [Partitions].index_id IN (
  0
  ,1
  )
INNER JOIN sys.tables st ON st.name = tables.name
-- WHERE [Tables].name = N'name of the table'
GROUP BY SCHEMA_NAME(st.schema_id)
 ,[Tables].name
 ,st.create_date
ORDER BY 4 DESC

Script to find whats going in Distribution or Replication is getting moving or not ?

USE distribution
GO

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT ma2.publisher_db
 ,mh1.delivery_latency / (1000 * 60) AS delivery_latency_Minutes
 ,mh1.agent_id
 ,mh1.TIME
 ,CAST(mh1.comments AS XML) AS comments
 ,CASE mh1.runstatus WHEN 1 THEN 'Start' 

WHEN 2 THEN 'Succeed.' 

WHEN 3 THEN 'In progress.' 

WHEN 4 THEN 'Idle.' 

WHEN 5 THEN 'Retry.' 

WHEN 6 THEN 'Fail' END AS STATUS
 ,mh1.duration
 ,mh1.xact_seqno
 ,mh1.delivered_transactions
 ,mh1.delivered_commands
 ,mh1.average_commands
 ,mh1.delivery_time
 ,mh1.delivery_rate
 ,ma2.name AS jobname
FROM mslogreader_history mh1
INNER JOIN (
 SELECT mh1.agent_id
  ,MAX(mh1.TIME) AS maxtime
 FROM mslogreader_history mh1
 INNER JOIN MSlogreader_agents ma ON ma.id = mh1.agent_id
 GROUP BY mh1.agent_id
 ) AS mh2 ON mh1.agent_id = mh2.agent_id
 AND mh1.TIME = mh2.maxtime
INNER JOIN MSlogreader_agents ma2 ON ma2.id = mh2.agent_id
ORDER BY mh1.delivery_latency DESC

Find Publisher and Subscriber details along with distribution agent job details in Replication

USE Distribution
GO

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- Get the publication name based on article 
SELECT DISTINCT srv.srvname publication_server
 ,a.publisher_db
 ,p.publication publication_name
 ,a.article
 ,a.destination_object
 ,ss.srvname subscription_server
 ,s.subscriber_db
 ,da.name AS distribution_agent_job_name
FROM MSArticles a
INNER JOIN MSpublications p ON a.publication_id = p.publication_id
INNER JOIN MSsubscriptions s ON p.publication_id = s.publication_id
INNER JOIN master..sysservers ss ON s.subscriber_id = ss.srvid
INNER JOIN master..sysservers srv ON srv.srvid = p.publisher_id
INNER JOIN MSdistribution_agents da ON da.publisher_id = p.publisher_id
 AND da.subscriber_id = s.subscriber_id
ORDER BY 1
 ,2
 ,3

Script to find Primary Key and Foreign Key table names

I used to do the below script to find Foreign Key tables associated with Primary Key table.

DECLARE @TableName VARCHAR(32) = 'Your Primary Key Table Name' -- Pass your table name here

SELECT o1.name AS FK_table
 ,c1.name AS FK_column
 ,fk.name AS FK_name
 ,o2.name AS PK_table
 ,c2.name AS PK_column
 ,pk.name AS PK_name
 ,fk.delete_referential_action_desc AS Delete_Action
 ,fk.update_referential_action_desc AS Update_Action
FROM sys.objects o1
INNER JOIN sys.foreign_keys fk ON o1.object_id = fk.parent_object_id
INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.columns c1 ON fkc.parent_object_id = c1.object_id
 AND fkc.parent_column_id = c1.column_id
INNER JOIN sys.columns c2 ON fkc.referenced_object_id = c2.object_id
 AND fkc.referenced_column_id = c2.column_id
INNER JOIN sys.objects o2 ON fk.referenced_object_id = o2.object_id
INNER JOIN sys.key_constraints pk ON fk.referenced_object_id = pk.parent_object_id
 AND fk.key_index_id = pk.unique_index_id
WHERE o2.name = @TableName
ORDER BY o1.name
 ,o2.name
 ,fkc.constraint_column_id

This script will helpful in below scenarios

1.Data archival from one table to other table 

- We need to disable FK constraints to move only

Primary key data to archival tables.

2.To delete only parent table data and keeping data in child table

In order to achieve the above scenarios we need to find PK and FK relation ships with help

of this scripts.

Script to share lead blocker details to mail and store lead blocker details in physical table.

The below script will help to send both blocking and blocked sessions along with query in 

single mail .

It will take automatically default mail profiler if it configured and send you mail

for the required list of recipients.

DECLARE @waittime AS INT = 180000
DECLARE @count VARCHAR(100)
DECLARE @dbname VARCHAR(100)
DECLARE @vcsubject VARCHAR(1000)

SELECT @count = count(*)
FROM master..sysprocesses
WHERE (blocked != 0)
 AND spid != blocked
 AND cmd != 'DB MIRROR'
 AND waittime > @waittime

IF (@count) >= 1
BEGIN
 DECLARE @tableHTML NVARCHAR(MAX);
 DECLARE @Servername VARCHAR(100);

 SELECT @Servername = convert(VARCHAR(100), SERVERPROPERTY('servername'));

 DECLARE @MAILPROFILE VARCHAR(100);

 IF OBJECT_ID('tempdb..#MAILDEFAULPROFILE') IS NOT NULL
  DROP TABLE #MAILDEFAULPROFILE

 CREATE TABLE #MAILDEFAULPROFILE (
  PRINCIPALID INT
  ,PRINCIPALNAME VARCHAR(200)
  ,PROFILEID INT
  ,PROFILENAME VARCHAR(200)
  ,ISDEFAULT BIT
  );

 INSERT INTO #MAILDEFAULPROFILE
 EXEC MSDB.DBO.SYSMAIL_HELP_PRINCIPALPROFILE_SP;

 SELECT @MAILPROFILE = PROFILENAME
 FROM #MAILDEFAULPROFILE
 WHERE ISDEFAULT = 1;

 SELECT *
 FROM #MAILDEFAULPROFILE

 IF OBJECT_ID('tempdb..##temptab') IS NOT NULL
  DROP TABLE ##temptab

 IF OBJECT_ID('tempdb..##Emailoutput') IS NOT NULL
  DROP TABLE ##Emailoutput

 CREATE TABLE ##temptab (blk INT)

 INSERT INTO ##temptab (blk)
 SELECT blocked
 FROM sys.sysprocesses
 WHERE blocked NOT IN (
   SELECT spid
   FROM sys.sysprocesses
   WHERE blocked <> 0
   )
  AND blocked <> 0

 CREATE TABLE ##Emailoutput (
  [Root Blocking SPID] VARCHAR(50)
  ,[Blocking Host] VARCHAR(100)
  ,[Blocking Login] VARCHAR(100)
  ,ProgramName VARCHAR(1000)
  ,[Blocked SPID] VARCHAR(50)
  ,[Blocking SPID] VARCHAR(50)
  ,[Blocked Status] VARCHAR(50)
  ,[Blocked Login] VARCHAR(100)
  ,[Blocked Host] VARCHAR(100)
  ,[Wait Time in Mins] VARCHAR(50)
  ,[blocked text] VARCHAR(max)
  ,[Root Blocking SPID Text] VARCHAR(max)
  )

 DECLARE @blc INT

 DECLARE cur CURSOR
 FOR
 SELECT DISTINCT blk
 FROM ##temptab

 OPEN cur

 FETCH NEXT
 FROM cur
 INTO @blc

 WHILE @@FETCH_STATUS = 0
 BEGIN
  INSERT INTO ##Emailoutput
  SELECT x.SESsION_ID AS [Root Blocking SPID]
   ,x.host_name [Blocking Host]
   ,x.login_name [Blocking Login]
   ,ProgramName
   ,y.*
   ,cast(x.SESsION_ID AS VARCHAR(10)) + '  - ' + x.TEXT [Root Blocking SPID Text]
  FROM (
   SELECT es.*
    ,program_name ProgramName
    ,st.TEXT
   FROM sys.dm_exec_sessions es
   INNER JOIN sys.dm_exec_connections ec ON ec.session_id = es.session_id
   CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) st
   WHERE es.session_id = @blc
   ) x
   ,(
    SELECT spid [Blocked SPID]
     ,blocked [Blocking SPID]
     ,STATUS [Blocked Status]
     ,loginame [Blocked Login]
     ,hostname [Blocked Host]
     ,waittime / 60000 [Wait Time in Mins]
     ,cast(spid AS VARCHAR(10)) + ' - ' + TEXT [blocked text]
    FROM sys.sysprocesses r
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
    WHERE r.blocked = @blc
    ) y

  FETCH NEXT
  FROM cur
  INTO @blc
 END

 CLOSE cur

 DEALLOCATE cur

 INSERT INTO Test..LeadBlockersHistory
 SELECT *
 FROM ##Emailoutput

 SET @tableHTML = N'<H1>Blocking on  ' + N' Servername : '

 + @Servername + '</H1>' + N'<table border="1">' 

+ N'<tr><th>Root Blocking SPID</th><th>Blocking Host</th>

<th>Blocking Login</th><th>ProgramName</th>' 

+ N'<th>Blocked SPID</th><th>Blocking SPID</th>

<th>Blocked Status</th><th>Blocked Login</th><th>Blocked Host</th>'

 + N'<th>Wait Time in Mins</th><th>Blocked Text</th>

<th>Root Blocking SPID Text</th>' + CAST((
    SELECT td = CAST([Root Blocking SPID] AS VARCHAR(50))
     ,''
     ,td = CAST([Blocking Host] AS VARCHAR(100))
     ,''
     ,td = CAST([Blocking Login] AS VARCHAR(100))
     ,''
     ,td = CAST(ProgramName AS VARCHAR(1000))
     ,''
     ,td = CAST([Blocked SPID] AS VARCHAR(100))
     ,''
     ,td = CAST([Blocking SPID] AS VARCHAR(100))
     ,''
     ,td = CAST([Blocked Status] AS VARCHAR(100))
     ,''
     ,td = CAST([Blocked Login] AS VARCHAR(100))
     ,''
     ,td = CAST([Blocked Host] AS VARCHAR(100))
     ,''
     ,td = CAST([Wait Time in Mins] AS VARCHAR(100))
     ,''
     ,td = CAST([blocked text] AS VARCHAR(max))
     ,''
     ,td = CAST([Root Blocking SPID Text] AS VARCHAR(max))
     ,''
    FROM ##Emailoutput
    FOR XML PATH('tr')
     ,TYPE
    ) AS NVARCHAR(MAX)) + N'</table>';
 SET @vcsubject = 'Blocking occuring in ' 

+ convert(VARCHAR(100), SERVERPROPERTY('servername')) + ', Please start monitoring'

 EXEC msdb.dbo.sp_send_dbmail @recipients = 'XYZ@MAIL.com'
  ,@profile_name = @MAILPROFILE
  ,@subject = @vcsubject
  ,@body = @tableHTML
  ,@body_format = 'HTML';
END