Đang chuẩn bị nút TẢI XUỐNG, xin hãy chờ
Tải xuống
SQL Server Tacklebox- P9: 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. | 2 The SQL Server landscape Security For security reporting we essentially want to know who has access to which databases and with which permissions. A sample query of the kind of information that can be gathered is in shown in Listing 2.5. IF EXISTS SELECT FROM tempdb.dbo.sysobjects WHERE id OBJECT_ID N tempdb . dbo . SQL_DB_REP DROP TABLE tempdb . dbo . SQL_DB_REP GO CREATE TABLE tempdb . dbo . SQL_DB_REP Server varchar 100 NOT NULL DB_Name varchar 70 NOT NULL User_Name nvarchar 90 NULL Group_Name varchar 100 NULL Account_Type varchar 22 NULL Login_Name varchar 80 NULL Def_DB varchar 100 NULL ON PRIMARY INSERT INTO tempdb . dbo . SQL_DB_REP Exec sp_MSForEachDB SELECT CONVERT varchar 100 SERVERPROPERTY Servername AS Server AS DB_Name usu.name u_name CASE WHEN usg.uid is null THEN public ELSE usg.name END as Group_Name CASE WHEN usu.isntuser 1 THEN Windows Domain Account WHEN usu.isntgroup 1 THEN Windows Group WHEN usu.issqluser 1 THEN SQL Account WHEN usu.issqlrole 1 THEN SQL Role END as Account_Type lo.loginname lo.dbname AS Def_DB FROM .sysusers usu LEFT OUTER JOIN .sysmembers mem INNER JOIN .sysusers usg ON mem.groupuid usg.uid ON usu.uid mem.memberuid LEFT OUTER JOIN master.dbo.syslogins lo ON usu.sid lo.sid WHERE 40 2 The SQL Server landscape usu.islogin 1 AND usu.isaliased 0 AND usu.hasdbaccess 1 AND usg.issqlrole 1 OR usg.uid is null Listing 2.5 Query to return security information about database access. As for the database management query a temp table is populated again using sp_msforeachdb. Ultimately our SSIS package Populate_DBA_Repository will read the data from this temp table and then store it in our central repository DBA_Rep . A simple Select from tempdb . dbo . SQL_DB_REP the output of which is shown in Figure 2.4 delivers a lot of information about security some of which may be surprising. You might be interested to know for example that MyDomain BadUser has DBO access to several user databases. Figure 2.4 Database user access levels. Over time .