sp_helplogins in AWS/RDS

Background

Against AWS/RDS, reviewing SQL Server permissions, but unable to do via sp_helplogins.

sp_helplogins

Sample


declare @principal sysname

set @principal = 'dadeniji';

exec [dbo].[sp_helplogins]
     @LoginNamePattern = @principal

Error

Msg 15247, Level 16, State 1, Procedure sp_helplogins, Line 72 [Batch Start Line 8]
User does not have permission to perform this action.

Troubleshooting

Reviewed code and determined that it performs an explicit check to see if the running user has access to the securityadmin server role.


----------------  Only SA can run this  -------------------

if (not (is_srvrolemember('securityadmin') = 1))
begin

   raiserror(15247,-1,-1)

   select @RetCode = 1

   goto label_86

   return

<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>end

Remediation

Outline

  1. Schema/Name Change
  2. Comment out sysadmin check
  3. Comment out “set nocount on
  4. Place elsewhere as we can not use master

Code

Code – Schema/Name Change

The original module name is sys.sp_helplogins.

We are unable to use the sys schema and and also to avoid name collision with sp_helplogins, used dbo.sp_helplogins_Customized.

 

Code – Comment out sysadmin check


/*

--------------  Only SA can run this  -------------------

if (not (is_srvrolemember('securityadmin') = 1))
begin

    raiserror(15247,-1,-1)

    select @RetCode = 1
    goto label_86return

end

*/

Code – Comment out ‘set nocount on’

There are a couple of places where “set nocount on” is issued.

Commented both out, as well.

 

Code – Master

As we are unable to modify objects in the master db, please place else where.

 

Source Code Control

GitHub

DanielAdeniji/SQLServer.sp_helplogins
Link

References

  1. Microsoft
    • SQL Server
      • System Stored Procedures
        • sp_helplogins
          Link

 

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s