Listing server and database roles for all logins and users in SQL Server – Part 2 – Remote Servers

Building upon the code in my previous post, I will be retrieving permissions information from remote servers that I have access to. This was a little more difficult to accomplish because security is different on different servers and I did not want to create Linked Servers with escalated permissions. Read only SQL users was an option, however there is no good way to make sure that these users will have read permissions on all current and future databases. It took a while to over come these challenges but I got it working; obviously since I have this blog post.

You can read Part 1 here if you haven’t yet.

First, let us create a “wrapper” stored procedure that will call one of the other two procedures we created in part one.


CREATE Procedure pr_GetPermissionsOnServers
@RequestLevel varchar(20) = 'SERVER'
---CAN USE SERVER OR DATABASE HERE

as
BEGIN

If @RequestLevel = 'DATABASE'
BEGIN
Truncate table DatabasePermissions
END
ELSE IF @RequestLevel = 'SERVER'
BEGIN
Truncate table ServerPermissions
END

DECLARE @servername as varchar(100)

DECLARE cr_getpermissions cursor
FOR select name
from master.sys.servers
where name NOT in
('SOMESERVERNAME')
order by name

DECLARE @SQLCursor varchar(max)

OPEN cr_getpermissions
FETCH NEXT FROM cr_getpermissions INTO @servername
WHILE (@@Fetch_Status -1)
BEGIN
If @RequestLevel = 'DATABASE'
BEGIN
exec pr_GetAllDatabasePermissions @servername
END
ELSE IF @RequestLevel = 'SERVER'
BEGIN
exec pr_GetAllServerPermissions @servername
END

FETCH NEXT FROM cr_getpermissions INTO @servername
END

CLOSE cr_getpermissions
DEALLOCATE cr_getpermissions

END


Alright, now we’re set for the fun stuff. As you can plainly see from the stored procedure above, we will either be calling it to get Server Level Permissions or Database Level Permissions. The important piece to keep an eye on is the cursor cr_getpermissions which will pull the names of your Linked Servers. If you would rather not get information for a specific linked server then add it to the where clause. Also, this means that if you do not have a linked server created for a specific server then you will not be able to get information about permissions on it.

The biggest challenge for me in getting the security information I was looking for from my remote servers was the permissions on the remote servers.  I tried to use the EXECUTE AS command within my stored procedures but got errors.  I was stuck with two different approaches. Obviously it is not a good idea to have the security context of a linked server using a sys admin account on your remote sever; otherwise someone could modify your remote sever through the linked server. The first approach was to give access to a read only account on every single database on my remote server. Problem with that is when new databases are added then there is some necessary maintenance to be done otherwise the procedure will fail bringing back the necessary data due to permissions problems.

The second approach was for me to use the login mappings. The local login for the linked server would be set to the SQL Agent account of the local server.  The remote use would be set to a user with more permissions on the remote server. I actually used a pretty strong account and the argument I made with myself was that if someone knew the password for the sys admin account, or the SQL Agent account then they are someone that  already has a strong set of permissions on the servers.

But why the SQL Agent account of all accounts? Because I was going to use a job to run my stored procedures to truncate and fill my tables with security information. That whenever the job ran it would impersonate the stronger permissions when it would use the linked server but anyone else using the linked server would be forced to use it with read only permissions. Also, I doubt that someone would need real time information about security so the job could run every week, day, or even hour without any problem.

Here is the way I have the linked server security set up:

Finally, to put this all together, I created a job with two steps, one for server permissions and one for database permissions with the following code:


---Step 1

USE SSRSReportingDB
EXEC pr_GetPermissionsOnServers 'Server'

--Step 2

USE SSRSReportingDB
EXEC pr_GetPermissionsOnServers 'Database'


Or for those who are in a bit more of a hurry, here is the code to create the entire job.


USE [msdb]
GO

/****** Object: Job [Refresh Server and Database Permissions report] Script Date: 10/15/2012 07:49:00 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 10/15/2012 07:49:00 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Refresh Server and Database Permissions report',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Refresh Server Level Permissiosn] Script Date: 10/15/2012 07:49:00 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Refresh Server Level Permissions',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'USE SSRSReportingDB
EXEC pr_GetPermissionsOnServers ''Server''',
@database_name=N'SSRSReportingDB',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Refresh Database Permissions Table] Script Date: 10/15/2012 07:49:00 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Refresh Database Permissions Table',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'USE SSRSReportingDB
EXEC pr_GetPermissionsOnServers ''Database''',
@database_name=N'SSRSReportingDB',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Weekly',
@enabled=1,
@freq_type=8,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20120928,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO