SupportSQL

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

We have covered getting all security principles and their role memberships, and the role/group memberships of those, to the top of the membership tree. This procedure collects all the security granted to principles/roles/groups for all databases on the SQL Server instance.

In another installment we will tie these procedures together into a view that shows all security access for any principle/role/group, with all specifically granted permissions as well as all permission obtained through role/group memberships.

The procedure will create a table (if it doesn’t exist), truncate it and populate it with all the “protects” data collected from each database.

Here’s the proc:

CREATE PROCEDURE [gp_Security_Protects]
AS

SET NOCOUNT ON

DECLARE @lngCounter INTEGER
DECLARE @strDBName VARCHAR(50)
DECLARE @strSQL NVARCHAR(4000)

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[Security_Protects]’) AND type in (N’U’))
BEGIN
CREATE TABLE [dbo].[Security_Protects](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ServerName] [varchar](150) NOT NULL CONSTRAINT [DF_Security_Protects_ServerName]  DEFAULT (@@servername),
    [DBName] [varchar](150) NULL,
    [Owner] [varchar](150) NULL,
    [Object] [varchar](150) NULL,
    [Grantee] [varchar](150) NULL,
    [Grantor] [varchar](150) NULL,
    [PermType] [varchar](150) NULL,
    [Action] [varchar](50) NULL,
    [Columns] [varchar](150) NULL,
CONSTRAINT [PK_Security_Protects] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END

truncate table dbo.Security_Protects

CREATE TABLE #P
(
own VARCHAR(150)
,obj VARCHAR(150)
,grantee VARCHAR(150)
,grantor VARCHAR(150)
,typ VARCHAR(150)
,act VARCHAR(150)
,col VARCHAR(150)
)

CREATE TABLE #tDBNames
(lngID INTEGER IDENTITY(1,1)
,strDBName VARCHAR(150)
)

INSERT INTO #tDBNames (strDBName)
SELECT [name] FROM master.dbo.sysdatabases
SET @lngCounter = @@ROWCOUNT

WHILE @lngCounter > 0
BEGIN
    SET @strDBName = (SELECT strDBName FROM #tDBNames WHERE lngID = @lngCounter)

    SET @strSQL = ‘INSERT INTO #P (own, obj, grantee, grantor, typ, act, col)
    EXEC [’ + @strDBName + ‘].dbo.sp_helprotect’

    EXEC sp_executesql @strSQL

INSERT INTO [dbo].[Security_Protects]
           ([ServerName]
           ,[DBName]
           ,[Owner]
           ,[Object]
           ,[Grantee]
           ,[Grantor]
           ,[PermType]
           ,[Action]
           ,[Columns])
     select @@SERVERNAME, @strDBName, own, obj, grantee, grantor, typ, act, col from #P

    truncate table #P

    SET @lngCounter = @lngCounter - 1
END
drop table #P
drop table #tDBNames
select * from [dbo].[Security_Protects]

—by Alek Kirstein – SupportSQL.com