I am accountable for some test database servers. In the past, a lot of other poeple get access to them. They operate on SQL Server 2005. I have been writing queries and wrapping them in scripts in order to operate a regular audit of privileges. Discovering which customers had Administrator privileges around the server itself was fine, as was discovering who had the 'sysadmin' role on their own login - it had been just one line query for that latter.

But exactly how to discover which logins possess a User Mapping to particular (or any) database? I'm able to discover the sys.database_principals and sys.server_principals tables. I've situated the sys.databases table. I've not exercised how you can discover which customers have privileges on the database, and when so, what. Every Search raises people by hand while using User Mapping pane from the Login dialog, instead of utilizing a query to do this. Any ideas?


select * from Master.dbo.syslogins l inner join sys.sysusers u on l.sid = u.sid

This can enable you to get what customers are planned that logins inside a single database.

Read this msdn reference article on Has_Perms_By_Name. I believe you are really thinking about good examples D, F and G


Also try this... I thrilled SQL profiler and clicked on around the ObjectExplorer->Security->Users. This led to (approximately) the next query being released.

SELECT *
FROM
  sys.database_principals AS u
  LEFT OUTER JOIN sys.database_permissions AS dp
  ON dp.grantee_principal_id = u.principal_id and dp.type = N'CO'
WHERE (u.type in ('U', 'S', 'G', 'C', 'K'))
ORDER BY [Name] ASC

Here's how to get this done. I wound up finding mention of the a sproc within the MSDN paperwork. I drawn this in the sproc and wrapped it inside a loop of all of the databases recognized to the instance.

select DbRole = g.name, MemberName = u.name
  from @NAME.sys.database_principals u, @NAME.sys.database_principals g, @NAME.sys.database_role_members m
  where   g.principal_id = m.role_principal_id
    and u.principal_id = m.member_principal_id
    and g.name in (''db_ddladmin'', ''db_owner'', ''db_securityadmin'') 
    and u.name not in (''dbo'')
  order by 1, 2

This then reviews the customers which have DBO who possibly should not. I have already suspended some admin access from some customers they did not need. Thanks everybody!