June 7, 2009 at 1:21 am
Is there any way to export the users with their assigned roles on a particular SQL Server for auditing purposes?
I tried to find any third party utilities but to no luck.
June 9, 2009 at 8:02 pm
I did this once with sql to check my permission and logins transferred correctly from one server to another.
SET @mySQL = 'select [name] from [' + @InstanceName + '].[master].[sys].[databases] where [name] not in (''master'', ''msdb'', ''model'', ''RA50'') and [name] not like ''websense%'' and [name] not like ''ReportServer$%'' '
insert into #dbname([TABLE_CATALOG])
exec (@mySQL)
DECLARE mydbs CURSOR FOR select * from #dbname
OPEN mydbs
FETCH NEXT FROM mydbs INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @mySQL =
'select ''' + @dbname +''', convert(char(45),sp.name) as srvLogin, convert(char(45),sp2.name) as srvRole,
convert(char(50),dbp.name) as dbUser, convert(char(25),dbp2.name) as dbRole from
['+ @instancename +'].[master].sys.server_principals as sp join ['+ @instancename +'].[' + @dbname +'].[sys].[database_principals] as dbp on sp.sid=dbp.sid join ['+ @instancename +'].[' + @dbname + '].[sys].[database_role_members] as dbrm on dbp.principal_Id=dbrm.member_principal_Id join ['+ @instancename +'].['
+ @dbname + '].[sys].[database_principals] as dbp2 on dbrm.role_principal_id=dbp2.principal_id left join ['+ @instancename +'].[master].sys.server_role_members as srm on sp.principal_id=srm.member_principal_id left join ['+ @instancename +'].[master].sys.server_principals as sp2 on srm.role_principal_id=sp2.principal_id'
insert into #perdata
exec (@mysql)
SET @mysql =
'select ''' + @dbname + ''', convert(char(45),sp.name) as srvLogin,
convert(char(45),sp2.name) as srvRole, convert(char(50),dbp.name) as dbUser, ''public''
from ['+ @instancename +'].[master].[sys].[server_principals] as sp join ['+ @instancename +'].[' + @dbname + '].[sys].[database_principals] as dbp on sp.sid=dbp.sid left join ['+ @instancename +'].[master].sys.server_role_members as srm on sp.principal_id=srm.member_principal_id left join ['+ @instancename +'].[master].sys.server_principals as sp2 on srm.role_principal_id=sp2.principal_id where not exists (select * from ['+ @instancename +'].[' + @dbname + '].[sys].[database_role_members] as dbrm join ['+ @instancename +'].[' + @dbname + '].[sys].[database_principals] as dbp2 on dbrm.role_principal_id=dbp2.principal_id where dbp.principal_Id=dbrm.member_principal_Id) '
insert into #perdata
exec (@mysql)
FETCH NEXT FROM mydbs INTO @dbname
END
CLOSE mydbs
DEALLOCATE mydbs
select * from #perdata
June 10, 2009 at 3:17 am
I do not want to transfer the access anywhere, i just want to report or probably export it to excel in a easy format;
June 10, 2009 at 10:48 pm
in management studio you should be able to save query results as a csv file by right clicking on the results. This should be accessible in excel.
June 11, 2009 at 1:01 pm
Idera has a free tool called SQL Permissions.
Tim White
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply