Assign permissions to view all logins

Question

Post reply

Assign permissions to view all logins

pehlebhiayatha

Old Hand

Points: 371
More actions
March 1, 2016 at 1:40 pm

#305220

Hi Gurus,
as a adhoc DBA I have a unique request to assign non-admin user the ability to only view all the logins in SQL Server. I've reviewed securityadmin server role but that allows far too many permissions to a user. The user just requires capability to only view the logins in SQL Server. Is there any best way we can assign \ full fil such request in SQL Server without giving too many permissions? Thanks in advance.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

write a stored procedure and assign him execute permissions on it?

pehlebhiayatha Old Hand Points: 371 More actions · Answer 2

Thanks for you reply.

I tried below but it only show sa and he's login but not all logins in SQL Server.

create procedure uspLogins

as

select name

from sys.syslogins

order by name asc

go

any thoughts what I need to do? thanks

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

You need to grant the VIEW DEFINITION permission on the server principal. See https://msdn.microsoft.com/en-us/library/ms178640(v=sql.100).aspx

Example:

GRANT VIEW DEFINITION ON LOGIN::[test1] TO [test2];

Repeat this for all logins and you should be fine. Don't grant VIEW ANY DEFINITION: it's a high privileged permission that should not be granted lightly.

-- Gianluca Sartori

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

pehlebhiayatha (3/1/2016)
Thanks for you reply.
I tried below but it only show sa and he's login but not all logins in SQL Server.
create procedure uspLogins
as
select name
from sys.syslogins
order by name asc
go
any thoughts what I need to do? thanks

When you create the procedure, do it WITH EXECUTE AS OWNER and then grant the user permission to execute the procedure. They'll run it, but it'll run as you. See https://msdn.microsoft.com/en-us/library/ms188354%28v=sql.100%29.aspx.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

ScottPletcher SSC Guru Points: 101098 More actions · Answer 5

Best would be to create the proc so that it runs as "OWNER", if SQL allows you to do that. Then the auditors wouldn't need separate permissions of their own.

Otherwise, try this command:

GRANT VIEW ANY DEFINITION TO [auditor_syslogins];

and see if it helps. Yes, it lets them see any definition, but that's all, they still can't change anything.

In a real audit, the DBA doesn't control the auditors. Just think about it: if the DBA can control what the auditors see and do, then couldn't they cover up what it was auditors are trying to find!!? For example, when I was at International Paper, when the SQL auditors came in, I was aware they were there but I didn't directly control what they did. I just made sure they could not change anything on the system.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".