Reports for SQL help

Hi,
I need to generate a report that gives me a list of all users and
their access right on a sql server
Any ideas how i can do this
Thank in advance
ZachOn Oct 1, 11:35 am, zach <neopot...@.gmail.com> wrote:
> Hi,
> I need to generate a report that gives me a list of all users and
> their access right on a sql server
> Any ideas how i can do this
> Thank in advance
> Zach
Create a stored procedure/query based on the query below (or similar:
based on your needs) and have this as the datasource for a report.
SELECT LoginName, CASE WHEN SYSADMIN = 1 THEN 'SysAdmin' WHEN
SECURITYADMIN = 1 THEN 'SecurityAdmin' WHEN SERVERADMIN = 1 THEN
'ServerAdmin' WHEN SETUPADMIN = 1 THEN 'SetupAdmin' WHEN
PROCESSADMIN = 1 THEN 'ProcessAdmin' WHEN DISKADMIN = 1 THEN
'DiskAdmin' WHEN
DBCREATOR = 1 THEN 'DBCreator' WHEN BULKADMIN = 1 THEN 'BulkAdmin'
END AS DBRole
FROM MASTER.SYS.SYSLOGINS
WHERE HASACCESS = 1;
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant