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.