Let NonAdmins Get Logins

I saw a question recently about how to allow some users to see the logins on a server without being a sysadmin or securityadmin. This was in support of a migration effort, so users needed read rights without being able to change anything.

This appears to be a good place to use the WITH EXECUTE AS option for a stored procedure. I decided to try. I have a normal, non privileged user, JoeDBA, with rights to connect to my Sandbox database. I decided to create a proc as a sysadmin user.

Here is my code. Notice the WITH EXECUTE AS option. Since I created this with my sjones, sysadmin, account. This should be able to call into master and get the data.

CREATE PROCEDURE GetLogins_NonSA
WITH EXECUTE AS OWNER
/*
Description:

Changes:
Date       Who         Notes
———- —         —————————————————
1/24/2017  PLATO\Steve Initial proc to get server logins
*/
AS
BEGIN
SELECT name
     , principal_id
     , sid
     , type
     , type_desc
     , is_disabled
     , default_database_name
FROM master.sys.server_principals

RETURN
END
GO

GRANT EXECUTE ON GetLogins_NonSA TO MigrationRole
go
ALTER ROLE MigrationRole ADD MEMBER JoeDBA

Now, I can log in with a low privileged user. By default, a query against sys.server_principals should only return my login. If I query the DMV, I get this:

2017-01-24 10_08_56-SQLQuery2.sql - (local)_SQL2016.sandbox (JoeDev (71))_ - Microsoft SQL Server Ma

Now, I can execute the procedure. I’ve scrolled the results a bit, but you can see I view other users.

2017-01-24 10_09_30-SQLQuery2.sql - (local)_SQL2016.sandbox (JoeDev (71))_ - Microsoft SQL Server Ma

Certainly I could limit the columns returned, or transform them to provide more data, but this is a good way to give read-only access to a login about other logins.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.