April 13, 2012 at 8:38 am
I'm an "accidental DBA" attending to 8 production SQL Server 2005 (2) and 2008 (6) instances for a non-profit organization.
I've recently been asked to provide a security audit listing of all users who have access to the databases, including what databases they can access, and what permissions they have on those databases *in simple terms* of read, write (data), or modify (structures).
The simplicity is the rub. I have found dozens of queries that will tell me who has what server and database roles, right down to object level assignments, but it's all too technical (the server and database roles mean nothing to the folks who will review the audit) and too detailed (I don't need a list of who is granted permission to select against what view, function, sp, etc.... the organization (fortunately) does not assign rights at that granular a level).
The other challenge in developing this query is in the nature of how SQL Server assigns rights: a fixed sysadmin right at the server login level, for instance, will get inherited by every database, while a public-level role assignment to a login can get escalated if the associated database user is assigned, for instance, "db_owner" of a particular database.
Bottom line: I am looking for a T-SQL query that will return the following columns, which I will run against each of the 8 SQL Server instances (so it will need to be runnable against both 2005 and 2008 versions):
(1) Login Name;
(2) Databases this Login can Access (sep. row per database);
(3) Database User Name(s) associated with the Login for each Accessible Database;
(4) Specific Permissions this User has in each Database translated to these three terms: READ, WRITE (data only), MODIFY (structural changes): so, three columns R, W, M with either an X or NULL for each;
(5) Is this User's Login currently Disabled? (X or NULL)
I should mention that our organization has not defined any custom roles, and the only standard role that gets assigned at the server level is sysadmin; at the database level, any of the standard roles may be assigned.
Any help would be greatly appreciated. Obviously, the LoginName and UserName columns will contain redundant data to cover the different Databases to which the user has access.
Many thanks,
Daniel
April 13, 2012 at 5:34 pm
To answer my own challenge, I used two separate tsql queries: one to list all users who have sysadmin rights at the server level, and the second to list per-database rights. That's the best I could do (for now).
The serveradmins query is a slight modification of Eric Russell's excellent article[/url]:
set xact_abort off;
set nocount on;
declare @principals table
(
primary key ( LoginType, LoginName, UserName ),
LoginType varchar(180) not null,
LoginName varchar(180) not null,
UserName varchar(180) not null,
principal_id int null,
sid varbinary(85) null,
Disabled tinyint null,
Created datetime null,
Modified datetime null,
-- admin_role_desc varchar(180) null,
SysAdmin tinyint null,
LoginInfo varchar(8000) null
);
-- insert all accounts and groups into result:
insert into @principals
select
type_desc as LoginType,
name as LoginName,
'-' as UserName,
principal_id,
sid,
is_disabled as Disabled,
create_date as Created,
modify_date as Modified,
(
case is_srvrolemember('sysadmin',name) when 1 then 1 else 0 end
/*
case is_srvrolemember('sysadmin',name) when 1 then 'sysadmin|' else null end
+ case is_srvrolemember('securityadmin',name) when 1 then 'securityadmin|' else null end
+ case is_srvrolemember('serveradmin',name) when 1 then 'serveradmin|' else null end
+ case is_srvrolemember('setupadmin',name) when 1 then 'setupadmin|' else null end
+ case is_srvrolemember('processadmin',name) when 1 then 'processadmin|' else null end
+ case is_srvrolemember('diskadmin',name) when 1 then 'diskadmin|' else null end
+ case is_srvrolemember('dbcreator',name) when 1 then 'dbcreator|' else null end
+ case is_srvrolemember('bulkadmin',name) when 1 then 'bulkadmin|' else null end
*/
) as admin_role_desc,
'' as LoginInfo
from sys.server_principals
;
declare @admin_groups table
(
primary key ( group_type, group_name ),
group_type varchar(180) not null,
group_name varchar(180) not null
);
declare @logininfo table
(
primary key ( account_name, permission_path ),
account_name varchar(180) not null,
type varchar(180) null,
privilege varchar(180) null,
mapped_login_name varchar(180) null,
permission_path varchar(180) not null
-- principal_id int null,
-- sid varbinary(85) null
);
-- For each domain group with admin privileges,
-- insert one record for each of it's member accounts into the result:
declare @group_type varchar(180), @group_name varchar(180);
select @group_type = '*', @group_name = '*';
while @group_name is not null
begin
select @group_type = null, @group_name = null;
select top 1 @group_type = LoginType, @group_name = LoginName
from @principals
where LoginType in ('WINDOWS_GROUP')
and UserName = '-'
-- and admin_role_desc is not null
and SysAdmin = 1
and LoginName not in (select group_name from @admin_groups);
if @group_name is not null
begin
-- Call xp_logininfo to return all domain accounts belonging to group:
insert @admin_groups values (@group_type, @group_name);
begin try
delete from @logininfo;
insert into @logininfo
exec master..xp_logininfo @group_name,'members';
-- Update number of members for group to LoginInfo:
update @principals
set LoginInfo = case @@rowcount when 1 then 'There is 1 member of this group.' when 0 then 'There are no members of this group.' else 'There are '+cast(@@rowcount as varchar(9))+' members of this group.' end
where LoginType in ('WINDOWS_GROUP')
and LoginName = @group_name
and UserName = '-';
end try
begin catch
-- If an error occurred, then update it to LoginInfo, and then continue:
/*
update @principals
set LoginInfo = 'xp_logininfo returned error '+cast(error_number() as varchar(9))
where LoginType in ('WINDOWS_GROUP')
and LoginName = @group_name
and UserName = '-';
*/
end catch
-- For each group member, insert a record into the result:
insert into @principals
select
@group_type as LoginType,
@group_name as LoginName,
account_name as UserName,
null as principal_id,
null as sid,
'' as Disabled,
null as Created,
null as Modified,
-- (select admin_role_desc
(select SysAdmin
from @principals
where LoginType = @group_type
and LoginName = @group_name
and UserName = '-'
-- ) as admin_role_desc,
) as SysAdmin,
'' as LoginInfo
from @logininfo;
-- For each group member that is a group,
-- insert a record of type 'WINDOWS_GROUP' into the result:
insert into @principals
select
'WINDOWS_GROUP' as LoginType,
account_name as LoginName,
'-' as UserName,
null as principal_id,
null as sid,
'' as Disabled,
null as Created,
null as Modified,
-- (select admin_role_desc
(select SysAdmin
from @principals
where LoginType = @group_type
and LoginName = @group_name
and UserName = '-'
-- ) as admin_role_desc,
) as SysAdmin,
'' as LoginInfo
from @logininfo
where type = 'group'
and not exists
(select 1
from @principals
where LoginType = 'WINDOWS_GROUP' and LoginName = account_name -- and UserName = '-'
);
end;
end;
-- Return result of only those accounts, groups, and members who have an admin role:
select LoginType
,LoginName
,LoginInfo
,UserName
-- ,SysAdmin
,case Disabled when 1 then 'Y' else '' end as Disabled
-- ,Created
-- ,Modified
-- ,principal_id, sid
-- ,admin_role_desc
from @principals
-- where admin_role_desc is not null
where SysAdmin = 1
order by LoginType, LoginName;
The second query, which lists all databases with user access and permissions, I created myself:
set NOCOUNT on;
declare @dbname varchar(500);
declare @dbstate int;
declare @sql varchar(5000);
declare @permissions table
(
[Database] varchar(250) null,
[LoginName] varchar(250) null,
[UserName] varchar(250) null,
[LoginType] varchar(250) null,
[Disabled] char(1) null,
[Read] char(1) null,
[Write] char(1) null,
[Modify] char(1) null
);
declare dbs cursor
for select name, state from master.sys.databases order by name
open dbs
fetch next from dbs into @dbname, @dbstate;
while @@FETCH_STATUS = 0
begin
if @dbstate = 0
begin
SELECT @sql = 'select ' + '''' + @dbname + '''' + ' as [Database]
,sp.name AS LoginName
,p.name AS UserName
,p.type_desc AS LoginType
,case sp.is_disabled when 1 then ''X'' else '''' end AS Disabled
,case pp.name when ''db_denydatareader'' then '''' when ''db_datareader'' then ''X'' when ''db_owner'' then ''X'' else '''' end as [Read]
,case pp.name when ''db_denydatawriter'' then '''' when ''db_datawriter'' then ''X'' when ''db_owner'' then ''X'' else '''' end as [Write]
,case pp.name when ''db_owner'' then ''X'' when ''db_ddladmin'' then ''X'' else '''' end as [Modify]
FROM [' + @dbname + '].sys.database_role_members roles
JOIN [' + @dbname + '].sys.database_principals p ON roles.member_principal_id = p.principal_id
JOIN [' + @dbname + '].sys.database_principals pp ON roles.role_principal_id = pp.principal_id
JOIN [' + @dbname + '].sys.server_principals sp ON p.sid = sp.sid
where pp.name in (''db_denydatareader'',''db_datareader'',''db_owner'',''db_denydatawriter'',''db_datawriter'',''db_ddladmin'')
order by sp.name';
insert into @permissions exec(@sql);
end -- test if database is online
fetch next from dbs into @dbname, @dbstate;
end -- while loop
select * from @permissions
close dbs;
deallocate dbs;
Hope these prove useful to folks.
Daniel
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply