Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Thursday, 28 November 2013

To know SQL Permissions and IDENTITY Tables with row count in SQL

To know SQL Permissions and IDENTITY Tables with row count in SQL

Script:

To know SQL Permissions

SELECT USER_NAME(P.GRANTEE_PRINCIPAL_ID) AS PRINCIPAL_NAME,

DP.PRINCIPAL_ID,

DP.TYPE_DESC AS PRINCIPAL_TYPE_DESC,

P.CLASS_DESC,

OBJECT_NAME(P.MAJOR_ID) AS OBJECT_NAME,

P.PERMISSION_NAME,

P.STATE_DESC AS PERMISSION_STATE_DESC

FROM SYS.DATABASE_PERMISSIONS P

INNER JOIN SYS.DATABASE_PRINCIPALS DP

ON P.GRANTEE_PRINCIPAL_ID = DP.PRINCIPAL_ID

--WHERE P.STATE_DESC = 'DENY'

where OBJECT_NAME(P.MAJOR_ID) like '%SPname%'

To know IDENTITY Tables with row count in SQL


SELECT b.TableName,b.ColumnName,b.OriginalSeed,b.Step,b.LastValue,a.[RowCount] from 

(
SELECT
      QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName],sOBJ.object_id
      , SUM(sPTN.Rows) AS [RowCount]
FROM 
      sys.objects AS sOBJ
      INNER JOIN sys.partitions AS sPTN
            ON sOBJ.object_id = sPTN.object_id
WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
      AND index_id < 2 -- 0:Heap, 1:Clustered
GROUP BY 
      sOBJ.schema_id
      , sOBJ.name,sOBJ.object_id

)as a
join
(
SELECT TableName = OBJECT_NAME(OBJECT_ID) ,
       ColumnName = name ,
       OriginalSeed = seed_value ,
       Step = increment_value ,
       LastValue = last_value ,
       IsNotForReplication = is_not_for_replication,object_id
FROM sys.identity_columns
)as b