Sunday, 16 December 2012

Monitoring Solution to Identify SQL Server Login Details




DBAs or Application owner should periodically monitor the Database login access details. It is one of the maintenance activities for the SQL Server DBAs. Unauthorized users access have to be revoked, if DBAs or Application owners finds any. It is recommended to have this process be executed on monthly once.

It is really helpful for DBAs, if any automated monitoring solution is running on Server to identify SQL Server login details and trigger email to target members.

I have provided monitoring solution which gives detailed login access information in Server level and in database object permission levels.

Monitoring Script to identify Server level Login Details

SET NOCOUNT ON
create table #server_Login_Roles_Temp
(
ServerRole varchar(150),
Login_Name varchar(150),
MemberSID varchar(500)
)
insert into #server_Login_Roles_Temp
exec msdb..sp_helpsrvrolemember

create table #server_Login_Roles
(
Server_Name varchar(150),
Login_Name varchar(150),
ServerRole varchar(150)

)
insert into #server_Login_Roles
select @@servername,Login_Name,ServerRole from #server_Login_Roles_Temp ORDER BY 1,2,3

select Server_Name, Login_Name, ServerRole from #server_Login_Roles
drop table #server_Login_Roles
drop table #server_Login_Roles_Temp

Please find below screen shots for the output results.

































Monitoring Script to Identify Database Object Permission Details


IF OBJECT_ID(N'tempdb..#Database_User_Mapping', 'U') IS NOT NULL

            DROP TABLE #Database_User_Mapping;
GO
CREATE TABLE #Database_User_Mapping
            (
            Database_Name VARCHAR(100) DEFAULT DB_NAME(),
            User_Name VARCHAR(100),
            User_Type VARCHAR(50),
            Object_Type VARCHAR(50),
            Object_Name VARCHAR(100),
            Permission_Name VARCHAR(50),
            Permission_Type VARCHAR(50),
            Role_Name VARCHAR(100)
            )
GO
EXEC sp_MSforeachdb
'USE [?];
WITH    perms_cte as
(
        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
)
INSERT INTO #Database_User_Mapping
SELECT  DB_NAME(), p.principal_name,  p.principal_type_desc, p.class_desc, p.[object_name], p.permission_name, p.permission_state_desc, cast(NULL as sysname) as role_name
FROM    perms_cte p
WHERE   principal_type_desc <> ''DATABASE_ROLE''
UNION
--role members
SELECT  DB_NAME(), rm.member_principal_name, rm.principal_type_desc, p.class_desc, p.object_name, p.permission_name, p.permission_state_desc,rm.role_name
FROM    perms_cte p
right outer JOIN (
    select role_principal_id, dp.type_desc as principal_type_desc, member_principal_id,user_name(member_principal_id) as member_principal_name,user_name(role_principal_id) as role_name--,*
    from    sys.database_role_members rm
    INNER   JOIN sys.database_principals dp
    ON     rm.member_principal_id = dp.principal_id
) rm
ON     rm.role_principal_id = p.principal_id
order by 1'
--GO
--SELECT * FROM #Database_User_Mapping
GO
SELECT
            DISTINCT
            @@SERVERNAME AS Server_Name,
            Database_Name,
            User_Name,
            User_Type,
            CASE WHEN Role_Name IN ( 'db_datareader','db_datawriter','db_ddladmin','db_owner','db_accessadmin','db_backupoperator','db_securityadmin','db_denydatareader','db_denydatawriter')
                        THEN 'DATABASE'
                        ELSE Object_Type
            END Object_Type,
            CASE WHEN Role_Name IN ( 'db_datareader','db_datawriter','db_ddladmin','db_owner','db_accessadmin','db_backupoperator','db_securityadmin','db_denydatareader','db_denydatawriter')
                        THEN Database_Name
                        ELSE Object_Name
            END Object_Name,
            CASE WHEN Role_Name IN ( 'db_datareader','db_datawriter','db_ddladmin','db_owner','db_accessadmin','db_backupoperator','db_securityadmin','db_denydatareader','db_denydatawriter')
                        THEN Role_Name
                        ELSE Permission_Name
            END Permission_Type  
FROM
            #Database_User_Mapping
WHERE
            Database_Name NOT IN ('master','model','msdb','tempdb')
            AND User_Name NOT IN ('dbo')
            AND ( Object_Type <> 'DATABASE' OR Object_Type IS NULL )
ORDER BY
            Database_Name
GO
DROP TABLE #Database_User_Mapping
GO

Please find below screen shots for the output results.



  


















If we have multiple SQL Server instances, we create a SQL Table to store the name of the all SQL Server instances. Above mentioned SQL script can be used through OSQL Statement inside the batch script and loop through each SQL instances.