1.1 For only one database
SELECT
case prin.name when ‘dbo’ then prin.name + ‘ (‘+ (select SUSER_SNAME(owner_sid) from master.sys.databases where name =‘?’) + ‘)’ else prin.name end AS UserName,
prin.type_desc AS LoginType,
isnull(USER_NAME(mem.role_principal_id),”) AS AssociatedRole ,create_date,modify_date
FROM sys.database_principals prin
LEFT OUTER JOIN sys.database_role_members mem ON prin.principal_id=mem.member_principal_id
WHERE prin.sid IS NOT NULL and prin.sid NOT IN (0x00) and
prin.is_fixed_role <> 1 AND prin.name NOT LIKE ‘##%’
1.2 The following code runs against ALL the databases using SP_MSForeachdb and all roles for one principal is concatenated in one row
Ref:
http://www.pythian.com/blog/httpconsultingblogs-emc-comjamiethomsonarchive20070209sql-server-2005_3a00_-view-all-permissions-_2800_2_2900_-aspx/
DECLARE @DB_USers TABLE
(DBName sysname, UserName sysname, LoginType sysname, AssociatedRole varchar(max),create_date datetime,modify_date datetime)
INSERT @DB_USers
EXEC sp_MSforeachdb
‘
use [?]
SELECT ”?” AS DB_Name,
case prin.name when ”dbo” then prin.name + ” (”+ (select SUSER_SNAME(owner_sid) from master.sys.databases where name =”?”) + ”)” else prin.name end AS UserName,
prin.type_desc AS LoginType,
isnull(USER_NAME(mem.role_principal_id),””) AS AssociatedRole ,create_date,modify_date
FROM sys.database_principals prin
LEFT OUTER JOIN sys.database_role_members mem ON prin.principal_id=mem.member_principal_id
WHERE prin.sid IS NOT NULL and prin.sid NOT IN (0x00) and
prin.is_fixed_role <> 1 AND prin.name NOT LIKE ”##%”’
SELECT
dbname,username ,logintype ,create_date ,modify_date ,
STUFF(
(
SELECT ‘,’ + CONVERT(VARCHAR(500),associatedrole)
FROM @DB_USers user2
WHERE
user1.DBName=user2.DBName AND user1.UserName=user2.UserName
FOR XML PATH(”)
)
,1,1,”) AS Permissions_user
FROM @DB_USers user1
GROUP BY
dbname,username ,logintype ,create_date ,modify_date
ORDER BY DBName,username
2. 1 You can get list of SYSADMIN users by running following code
SELECT p.name AS [Name] ,
p.type_desc,
p.is_disabled ,
p.create_date ,
p.modify_date ,
p.default_database_name
FROM sys.server_principals r
INNER JOIN sys.server_role_members m ON r.principal_id = m.role_principal_id
INNER JOIN sys.server_principals p ON p.principal_id = m.member_principal_id
AND p.is_disabled = 0
WHERE r.type = ‘R’
AND r.name = N’sysadmin’
2.2 Get sysadmin serverroles except the service account
BEGIN
DECLARE @Date SMALLDATETIME
SET @Date = CAST(CAST(GETDATE() AS DATE) AS SMALLDATETIME)
SELECT CONVERT(VARCHAR(250), SERVERPROPERTY(‘ServerName’)) AS ServerName, SP1.[name] AS [Login], SP2.[name] AS ServerRole, SP1.is_disabled AS IsDisabled, @Date AS ExtractDate
FROM sys.server_principals SP1
INNER JOIN sys.server_role_members SRM ON SP1.principal_id = SRM.member_principal_id
INNER JOIN sys.server_principals SP2 ON SRM.role_principal_id = SP2.principal_id
WHERE SP1.[name] NOT IN (‘NT Service\MSSQLSERVER’,‘NT SERVICE\SQLSERVERAGENT’,‘NT SERVICE\SQLWriter’,
‘NT SERVICE\Winmgmt’,‘NT AUTHORITY\SYSTEM’,‘NT AUTHORITY\NETWORK SERVICE’)
;
END
3.
– If a windows login is part of a windows group then you may need to look at the members of this Windows group to identify who can access this database.
– A user may still be revoked access to database indvidual objects even the role allows access normally.
Following blog has a query to list permissions on indvidual objects
http://consultingblogs.emc.com/jamiethomson/archive/2007/02/09/SQL-Server-2005_3A00_-View-all-permissions–_2800_2_2900_.aspx
