Get permission information from a number of servers from central server?

• masmith-964869

  SSC Rookie

  Points: 28

  More actions

  January 22, 2015 at 10:38 am

  #145653

  I tried just about everything I can think of using linked server, stored procedures, dynamic SQL, views and functions and nothing works for the following requirements:

  1) Queries to show various permission granted for each SQL instance including database permissions and roles.

  2) Run and managed from a central server.

  3) Be able to insert the result into the table.

  If it uses a Insert <table> Exec ('...') and other similar methods you can't call the Store procedure with another Insert <table> Exec('...').

  4) No stored procedures, views or functions added to the managed SQL instances. Note many of the servers are vendor supported and managed so I can't always make changes on the SQL Server instances.

  5) If there's no way to make it work without adding a DB object like a stored procedure they need to be limited to a few in the master database or a utility DB added to the instance.

  6) I would like to have a parameter to optionally send a DB to see only one DB and default to all databases.

  Now I have gotten close using linked server and dynamic SQL where I could run the following:

  *****

  Declare @LinkedServer varchar(128) = '<linked server name>'

  Insert Into DBUsers

  exec sp_LinkDBUsers @LinkedServer, '<db name>'

  Select * From DBUsers

  *****

  And the following stored procedure is defined on the central server:

  *****

  USE [<Central DB with the SP>]

  GO

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  ALTER PROCEDURE [dbo].[sp_LinkDBUsers]

  @LinkServer Varchar(128),

  @DBVarchar(128) = Null

  AS

  Declare @Sel varchar(4000) --Used to hold TSQL during processing

  --TSQl to get Database Users

  --Replaced because it does not work with linked servers

  --OBJECT_NAME(dp.major_id)

  Declare @SelDBUsers varchar(4000) =

  'Exec (''-- Database users

  SELECT ''''##Link##'''' "Server",''''##DB##'''' "DB"

  ,pr.[name] "PrincipleName", pr.type_desc "PrincipleType"

  ,pr.create_date "CreateDate",pr.modify_date "ModifyDate"

  ,dp.state_desc "Permission",dp.permission_name "Action"

  ,CASE dp.class

  WHEN 0 THEN ''''Database::'''' + DB_NAME()

  WHEN 1 THEN ISNULL(s.[name] + ''''.'''', '''''''') + o.name -- This is where OBJECT_NAME was

  WHEN 3 THEN ''''Schema::'''' + SCHEMA_NAME(dp.major_id)

  END "Securable"

  ,pr2.[name] "Grantor"

  ,GetDate() "UpdatedDateTime"

  FROM [##Link##].[##DB##].sys.database_permissions AS dp

  INNER JOIN [##Link##].[##DB##].sys.database_principals AS pr ON dp.grantee_principal_id = pr.principal_id

  INNER JOIN [##Link##].[##DB##].sys.database_principals AS pr2 ON dp.grantor_principal_id = pr2.principal_id

  LEFT JOIN [##Link##].[##DB##].sys.objects AS o ON dp.major_id = o.[object_id]

  LEFT JOIN [##Link##].[##DB##].sys.schemas AS s ON o.[schema_id] = s.[schema_id]

  --LEFT JOIN [##Link##].[##DB##].sys.objects AS o2 ON dp.major_id = o2.[object_id] And o2.

  WHERE dp.class IN (0, 1, 3, 4)

  AND dp.minor_id = 0

  ORDER BY pr.[name], pr.type_desc'')'

  --Replace the ##Link## with the @LinkServer requested

  Set @Sel = replace(@SelDBUsers,'##Link##',@LinkServer)

  if(@DB is not null) begin

  --Process database passed in @DB

  Set @Sel = replace(@Sel,'##DB##',@DB)

  Exec (@Sel)

  End Else Begin

  --Process all databases in linked server

  --Don't want duplicates added

  Delete from DBUsers Where Server = @LinkServer

  --Get table of all the databases in the linked server

  Declare @TblDBs Table (RowID Int Identity(1,1), Name sysname)

  Insert @TblDBs Exec sp_GetLinkServerDBs @LinkServer

  --Process each database in @TblDBs into table DBRoleMembers

  Declare @imax int = (Select Count(*) From @TblDBs)

  Declare @i int = 1

  Declare @SelCurrDB varchar(4000)

  Declare @Name sysname

  While (@i <= @imax)

  Begin

  Select @Name = Name From @TblDBs Where RowID = @i

  Set @SelCurrDB = replace(@Sel,'##DB##',@Name)

  Insert into DBUsers Exec (@SelCurrDB)

  Set @i = @i + 1

  End

  --Return results

  Select * From DBUsers Order By DB

  End

  As I said this is close but I get stuck on all the function and such that use the current database context like: OBJECT_NAME(major_id), DB_NAME, and SCHEMA_NAME. OBJECT_NAME is the biggest problem where I tried to work around the problem by pulling object name form the sys.object table. The problem is that there are a number of database roles for instance that are not in the sys.objects table, have a negative value and don't display.

  I also tried the above using EXEC('...') AT <Dynamic link server name> that I don't believe had the above problem but I could not use the Insert <table> Exec('...').

  I also tried or looked at having a stored procedure, view or functions on the managed SQL Server but that presented it's own set of problems. The biggest is with the functions that use the current DB context, so the object would have to go into only every SQL Instance but also every database. You also would not be able to run it for all databases in a instance.

  At this point it looks like the only choice is to create an application outside SQL that logs into each SQL Instance and database and executes the dynamic code and inserts into a database table.

  Any creative ideas so I can keep this in SQL and don't have to create an application for this?