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:

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

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.

Filed under: Blog Tagged: security, sql server, syndicated