201609-SQL-Pull Permission

Fast to find out permissions grant to account in a MS SQL Server instance.

--USE master
--CREATE LOGIN [Domain\NTAccount] FROM WINDOWS WITH DEFAULT_DATABASE=[DBName], DEFAULT_LANGUAGE=[us_english]
--GO
--Use DBName
--CREATE USER [Domain\NTAccount] FOR LOGIN [Domain\NTAccount]

SELECT
  (
    dp.state_desc + ' ' +
    dp.permission_name collate latin1_general_cs_as + 
    ' ON ' + '[' + s.name + ']' + '.' + '[' + o.name + ']' +
    ' TO ' + '[' + dpr.name + ']'
  ) AS GRANT_Statement
FROM sys.database_permissions AS dp ----database permissions
  INNER JOIN sys.objects AS o ON dp.major_id=o.object_id --- Database name
  INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id --- Schema name
  INNER JOIN sys.database_principals AS dpr ON dp.grantee_principal_id=dpr.principal_id ---User logon
WHERE dpr.name NOT IN ('public','guest')
--  AND o.name IN ('My_Procedure')      -- Uncomment to filter to specific object(s)
--  AND dp.permission_name='EXECUTE'    -- Uncomment to filter to just the EXECUTEs

—View the permission on one database_permissions

--select name, class_desc, authentication_type,type_desc, Permission_name, State_desc, is_fixed_role
--from sys.database_permissions as a
--inner join sys.database_principals as b on a.grantee_principal_id=b.principal_id