SupportSQL

This installment is part of the “Security Management” series of articles for SQL Server.

Find all group memberships, and their group memberships, all the way to the highest group/role/principle membership. This will be correlated and joined to another procedure (in an article to be published soon) together returning all granted and inherited protects on the SQL Server instance for every principle and every role/group.

Used in several areas e.g. compliance, general security auditing, baseline comparisons, security inquiries, deployment cleanups, etc.

Find all principles with memberships to groups/roles and memberships of those groups/roles all the way to the top principle:

Just a few example uses:

• Find all sysadmin members
• Find all db_datawriter memberships
• Find all memberships for a principle/group/role, etc.
• Audit all principle memberships against the approved baseline.

Once all the security data is joined together (in a future article) we will be able to answer these types of security questions:

• what roles and groups is this user associated with
• how does this user have permission on this object
• who has sysadmin rights
• who is in the builtin\administrators nt group
• who has update permission on this object
• what permissions does this user have for this object
• who has deny on this object?
• what deny perms for this user? inherited/granted
• who has update rights
• who can impersonate
• who can manage users/roles/grants or any security
• what is the full scope delta of permissions if granting this user this permission (on this object)
• who has execute permissions on system/extended procedures/functions
• what security changes by whom have occurred in past x days
• what non-sysadmin user made security changes?
• what baseline changes have been made by who in past x day?
• what does not match baseline
• what non-admin owned objects exist
• which developers with update permissions in prod

Step 1: Run the proc gp_Security_AllRoleMembers (creates base data table and populates/refreshes)

Step 2: Use the view vw_SecurityPrinciplesMembershipTree to query all principles on the server.

CREATE PROCEDURE [dbo].[gp_Security_AllRoleMembers]
AS
—getsa all members of all database roles, server roles and nt groups in any group or role.

SET NOCOUNT ON

—Variables
DECLARE @cntr INTEGER
DECLARE @dbnm VARCHAR(50)
DECLARE @sqlstr NVARCHAR(4000)

—Temp table to hold database and user-define role user names
CREATE TABLE #tbl_rmembers
(
srvrnm VARCHAR(150) DEFAULT @@SERVERNAME
,dbnm VARCHAR(150)
,rolenm VARCHAR(150)
,usernm VARCHAR(150)
,userid VARCHAR(150)
)

—Temp table to hold database names
CREATE TABLE #tbldbs
(lngID INTEGER IDENTITY(1,1)
,dbnm VARCHAR(150)
)

—Create permanent table
IF OBJECT_ID (‘dbo.Security_RoleMembers’) IS NULL
BEGIN
CREATE TABLE dbo.Security_RoleMembers
(
    srvrnm VARCHAR(150)
    ,dbnm VARCHAR(150)
    ,rolenm VARCHAR(150)
    ,usernm VARCHAR(150)
    ,userid VARCHAR(150)
)
END

—Obtain members of each server role
INSERT INTO #tbl_rmembers (rolenm, usernm, userid)
EXEC dbo.sp_helpsrvrolemember

update #tbl_rmembers
set dbnm = ‘SERVER_ROLE’

—get db names
INSERT INTO #tbldbs (dbnm)
SELECT [name] FROM master.dbo.sysdatabases
SET @cntr = @@ROWCOUNT

—databases loop
WHILE @cntr > 0
BEGIN

    SET @dbnm = (SELECT dbnm FROM #tbldbs WHERE lngID = @cntr)

    —Obtain members of each database and user-defined role
    SET @sqlstr = ‘INSERT INTO #tbl_rmembers (rolenm, usernm, userid)
    EXEC [’ + @dbnm + ‘].dbo.sp_helprolemember’

    EXEC sp_executesql @sqlstr

    UPDATE #tbl_rmembers
    SET dbnm = @dbnm
    WHERE dbnm IS NULL

    SET @cntr = @cntr - 1
END

—Place role members data into permanent table
truncate table [DBA].[dbo].[Security_RoleMembers]
INSERT INTO [DBA].[dbo].[Security_RoleMembers]
           ([srvrnm]
           ,[dbnm]
           ,[rolenm]
           ,[usernm]
           ,[userid])
SELECT     srvrnm
        ,dbnm
        ,rolenm
        ,usernm
        ,userid
FROM #tbl_rmembers

—get all nt group members and add them also to the table
DECLARE @loginnm sysname
DECLARE @sql NVARCHAR (2000)

CREATE TABLE #tbls (acct nvarchar(150), typ nvarchar(50), priv nvarchar(50), mapped nvarchar(150), permpath nvarchar(150))
BEGIN
   DECLARE c1 CURSOR FOR
   SELECT [name] FROM master.sys.server_principals WHERE TYPE = ‘G’
   OPEN c1
   FETCH NEXT FROM c1 INTO @loginnm
   WHILE @@FETCH_STATUS = 0
       BEGIN
       truncate table #tbls
begin  try
        INSERT INTO #tbls
           EXEC xp_logininfo @loginnm , ‘members’ —‘all’—
        insert into dbo.Security_RoleMembers (srvrnm, dbnm, rolenm, usernm, userid)
           select @@SERVERNAME, DB_NAME(DB_ID()), @loginnm, acct, mapped from #tbls
end try
begin catch
print ‘error’
PRINT @loginnm

end catch

           FETCH NEXT FROM c1 INTO @loginnm
       END
   CLOSE c1
   DEALLOCATE c1
   RETURN
END

CREATE VIEW dbo.vw_SecurityPrinciplesMembershipTree
AS
SELECT DISTINCT
    [Members_table].srvrnm AS ServerName
   ,[Members_table].dbnm AS DBName
   ,[Members_table].rolenm AS Level1_Principal
   ,[Members_table].usernm AS Level2_Principal
   ,[Members_table_1].usernm AS Level3_Principal
   ,[Members_table_2].usernm AS Level4_Principal
   ,isnull(isnull(isnull([Members_table_2].usernm, [Members_table_1].usernm), [Members_table].usernm), [Members_table].rolenm) AS Principle
FROM
    Security_RoleMembers AS Members_table WITH (NOLOCK)
LEFT JOIN Security_RoleMembers AS Members_table_1 WITH (NOLOCK)
          ON Members_table.UserNm = Members_table_1.rolenm
LEFT JOIN Security_RoleMembers AS Members_table_2 WITH (NOLOCK) 
          ON Members_table_1.usernm = Members_table_2.rolenm
ORDER BY
    Members_table.rolenm
   ,Members_table.usernm
   ,Members_table_1.usernm
   ,Members_table_2.usernm

—by Alek Kirstein – SupportSQL.com