tailieunhanh - SQL Server Tacklebox- P37

SQL Server Tacklebox- P37: This book, as with almost all books, started out as an idea. I wanted to accumulate together those scripts and tools that I have built over the years so that DBAs could sort through them and perhaps adapt them for their own circumstances. | 7 Securing access to SQL Server WHEN is null then public ELSE END as Group_Name CASE WHEN 1 then Windows Domain Account WHEN 1 then Windows Group WHEN 1 then SQL Account WHEN 1 then SQL Role END as Account_Type as Def_DB FROM .sysusers usu LEFT OUTER JOIN .sysmembers mem INNER JOIN .sysusers usg ON ON LEFT OUTER JOIN lo on WHERE 1 and 0 and 1 and 1 or is null SELECT Server DB_Name User_Name Group_Name Account_Type Login_Name Def_DB FROM tempdb . dbo . SQL_DB_REP Listing Finding SQL users and roles. This particular query does not deal so much with sysadmin privileges but more with high database level privileges. For example it investigates membership of the db_owner database role which can perform all configuration and maintenance activities on a database. The DBA can also use it to investigate membership of other database roles that may have been created to serve a purpose such as the execution of stored procedures. The results of this query will instantly let the DBA know if any users have escalated privileges of which he or she was previously unaware. Table shows some sample results from executing this query due to space restrictions I omitted the Server column the value was Serverl in each case . 180 7 Securing access to SQL Server DB_Name User_Name Group_ Name Account _Type Login_ Name Def _DB DBA_Rep dbo db_owner Windows Domain Account RodDom rodney master ReportServer dbo db_owner Windows Domain Account RodDom rodney master ReportServer NT AUTHORITY SYSTEM RSExecRole Windows Domain Account NT AUTHORITY SYSTEM master Custom_HW dbo db_owner SQL Account sa master Custom_HW HWC Development db_owner Windows Group NULL NULL Custom_HW JimV db_owner Windows Domain Account NULL NULL Custom_HW jyoungblood public Windows Domain