我想在sql 2008上写一个查询，它将报告所有有权访问特定数据库的用户，或数据库中的对象，如表，视图和存储过程，直接或由于角色等。该报告将用于安全审计目的。不确定是否有人有一个查询，将完全满足我的需求，但希望能给我一个好的开始。无论是sql 2008, 2005或2000将做，我可以根据需要转换。
当前回答
如果你想检查访问数据库的特定登录已经使用这个简单的脚本如下所示:
sys。sp_helpogins @LoginNamePattern = '域\登录'——sysname
其他回答
一个简单的查询，只显示你是否是系统管理员:
IF IS_SRVROLEMEMBER ('sysadmin') = 1
print 'Current user''s login is a member of the sysadmin role'
ELSE IF IS_SRVROLEMEMBER ('sysadmin') = 0
print 'Current user''s login is NOT a member of the sysadmin role'
ELSE IF IS_SRVROLEMEMBER ('sysadmin') IS NULL
print 'ERROR: The server role specified is not valid.';
上面的GetPermissions存储过程很好，但是它使用Sp_msforeachdb，这意味着如果您的SQL实例有任何数据库名称，其中包括空格或虚线和其他非最佳实践字符，它将中断。我创建了一个避免使用Sp_msforeachdb的版本，它还包括两个列，分别表示1(如果登录是系统管理员登录(IsSysAdminLogin))和2(如果登录是孤儿用户(IsEmptyRow))。
USE [master] ;
GO
IF EXISTS
(
SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.uspGetPermissionsOfAllLogins_DBsOnColumns')
AND [type] in (N'P',N'PC')
)
BEGIN
DROP PROCEDURE dbo.uspGetPermissionsOfAllLogins_DBsOnColumns ;
END
GO
CREATE PROCEDURE dbo.uspGetPermissionsOfAllLogins_DBsOnColumns
AS
SET NOCOUNT ON
;
BEGIN TRY
IF EXISTS
(
SELECT * FROM tempdb.dbo.sysobjects
WHERE id = object_id(N'[tempdb].dbo.[#permission]')
)
DROP TABLE #permission
;
IF EXISTS
(
SELECT * FROM tempdb.dbo.sysobjects
WHERE id = object_id(N'[tempdb].dbo.[#userroles_kk]')
)
DROP TABLE #userroles_kk
;
IF EXISTS
(
SELECT * FROM tempdb.dbo.sysobjects
WHERE id = object_id(N'[tempdb].dbo.[#rolemember_kk]')
)
DROP TABLE #rolemember_kk
;
IF EXISTS
(
SELECT * FROM tempdb.dbo.sysobjects
WHERE id = object_id(N'[tempdb].dbo.[##db_name]')
)
DROP TABLE ##db_name
;
DECLARE
@db_name VARCHAR(255)
,@sql_text VARCHAR(MAX)
;
SET @sql_text =
'CREATE TABLE ##db_name
(
LoginUserName VARCHAR(MAX)
,'
;
DECLARE cursDBs CURSOR FOR
SELECT [name]
FROM sys.databases
ORDER BY [name]
;
OPEN cursDBs
;
FETCH NEXT FROM cursDBs INTO @db_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql_text =
@sql_text + QUOTENAME(@db_name) + ' VARCHAR(MAX)
,'
FETCH NEXT FROM cursDBs INTO @db_name
END
CLOSE cursDBs
;
SET @sql_text =
@sql_text + 'IsSysAdminLogin CHAR(1)
,IsEmptyRow CHAR(1)
)'
--PRINT @sql_text
EXEC (@sql_text)
;
DEALLOCATE cursDBs
;
DECLARE
@RoleName VARCHAR(255)
,@UserName VARCHAR(255)
;
CREATE TABLE #permission
(
LoginUserName VARCHAR(255)
,databasename VARCHAR(255)
,[role] VARCHAR(255)
)
;
DECLARE cursSysSrvPrinName CURSOR FOR
SELECT [name]
FROM sys.server_principals
WHERE
[type] IN ( 'S', 'U', 'G' )
AND principal_id > 4
AND [name] NOT LIKE '##%'
ORDER BY [name]
;
OPEN cursSysSrvPrinName
;
FETCH NEXT FROM cursSysSrvPrinName INTO @UserName
WHILE @@FETCH_STATUS = 0
BEGIN
CREATE TABLE #userroles_kk
(
databasename VARCHAR(255)
,[role] VARCHAR(255)
)
;
CREATE TABLE #rolemember_kk
(
dbrole VARCHAR(255)
,membername VARCHAR(255)
,membersid VARBINARY(2048)
)
;
DECLARE cursDatabases CURSOR FAST_FORWARD LOCAL FOR
SELECT [name]
FROM sys.databases
ORDER BY [name]
;
OPEN cursDatabases
;
DECLARE
@DBN VARCHAR(255)
,@sqlText NVARCHAR(4000)
;
FETCH NEXT FROM cursDatabases INTO @DBN
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlText =
N'USE ' + QUOTENAME(@DBN) + ';
TRUNCATE TABLE #RoleMember_kk
INSERT INTO #RoleMember_kk
EXEC sp_helprolemember
INSERT INTO #UserRoles_kk
(DatabaseName,[Role])
SELECT db_name(),dbRole
FROM #RoleMember_kk
WHERE MemberName = ''' + @UserName + '''
'
--PRINT @sqlText ;
EXEC sp_executesql @sqlText ;
FETCH NEXT FROM cursDatabases INTO @DBN
END
CLOSE cursDatabases
;
DEALLOCATE cursDatabases
;
INSERT INTO #permission
SELECT
@UserName 'user'
,b.name
,u.[role]
FROM
sys.sysdatabases b
LEFT JOIN
#userroles_kk u
ON QUOTENAME(u.databasename) = QUOTENAME(b.name)
ORDER BY 1
;
DROP TABLE #userroles_kk
;
DROP TABLE #rolemember_kk
;
FETCH NEXT FROM cursSysSrvPrinName INTO @UserName
END
CLOSE cursSysSrvPrinName
;
DEALLOCATE cursSysSrvPrinName
;
TRUNCATE TABLE ##db_name
;
DECLARE
@d1 VARCHAR(MAX)
,@d2 VARCHAR(MAX)
,@d3 VARCHAR(MAX)
,@ss VARCHAR(MAX)
;
DECLARE cursPermisTable CURSOR FOR
SELECT * FROM #permission
ORDER BY 2 DESC
;
OPEN cursPermisTable
;
FETCH NEXT FROM cursPermisTable INTO @d1,@d2,@d3
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT EXISTS
(
SELECT 1 FROM ##db_name WHERE LoginUserName = @d1
)
BEGIN
SET @ss =
'INSERT INTO ##db_name(LoginUserName) VALUES (''' + @d1 + ''')'
EXEC (@ss)
;
SET @ss =
'UPDATE ##db_name SET ' + @d2 + ' = ''' + @d3 + ''' WHERE LoginUserName = ''' + @d1 + ''''
EXEC (@ss)
;
END
ELSE
BEGIN
DECLARE
@var NVARCHAR(MAX)
,@ParmDefinition NVARCHAR(MAX)
,@var1 NVARCHAR(MAX)
;
SET @var =
N'SELECT @var1 = ' + QUOTENAME(@d2) + ' FROM ##db_name WHERE LoginUserName = ''' + @d1 + ''''
;
SET @ParmDefinition =
N'@var1 NVARCHAR(600) OUTPUT '
;
EXECUTE Sp_executesql @var,@ParmDefinition,@var1 = @var1 OUTPUT
;
SET @var1 =
ISNULL(@var1, ' ')
;
SET @var =
' UPDATE ##db_name SET ' + @d2 + '=''' + @var1 + ' ' + @d3 + ''' WHERE LoginUserName = ''' + @d1 + ''' '
;
EXEC (@var)
;
END
FETCH NEXT FROM cursPermisTable INTO @d1,@d2,@d3
END
CLOSE cursPermisTable
;
DEALLOCATE cursPermisTable
;
UPDATE ##db_name SET
IsSysAdminLogin = 'Y'
FROM
##db_name TT
INNER JOIN
dbo.syslogins SL
ON TT.LoginUserName = SL.[name]
WHERE
SL.sysadmin = 1
;
DECLARE cursDNamesAsColumns CURSOR FAST_FORWARD LOCAL FOR
SELECT [name]
FROM tempdb.sys.columns
WHERE
OBJECT_ID = OBJECT_ID('tempdb..##db_name')
AND [name] NOT IN ('LoginUserName','IsEmptyRow')
ORDER BY [name]
;
OPEN cursDNamesAsColumns
;
DECLARE
@ColN VARCHAR(255)
,@tSQLText NVARCHAR(4000)
;
FETCH NEXT FROM cursDNamesAsColumns INTO @ColN
WHILE @@FETCH_STATUS = 0
BEGIN
SET @tSQLText =
N'UPDATE ##db_name SET
IsEmptyRow = ''N''
WHERE IsEmptyRow IS NULL
AND ' + QUOTENAME(@ColN) + ' IS NOT NULL
;
'
--PRINT @tSQLText ;
EXEC sp_executesql @tSQLText ;
FETCH NEXT FROM cursDNamesAsColumns INTO @ColN
END
CLOSE cursDNamesAsColumns
;
DEALLOCATE cursDNamesAsColumns
;
UPDATE ##db_name SET
IsEmptyRow = 'Y'
WHERE IsEmptyRow IS NULL
;
UPDATE ##db_name SET
IsSysAdminLogin = 'N'
FROM
##db_name TT
INNER JOIN
dbo.syslogins SL
ON TT.LoginUserName = SL.[name]
WHERE
SL.sysadmin = 0
;
SELECT * FROM ##db_name
;
DROP TABLE ##db_name
;
DROP TABLE #permission
;
END TRY
BEGIN CATCH
DECLARE
@cursDBs_Status INT
,@cursSysSrvPrinName_Status INT
,@cursDatabases_Status INT
,@cursPermisTable_Status INT
,@cursDNamesAsColumns_Status INT
;
SELECT
@cursDBs_Status = CURSOR_STATUS('GLOBAL','cursDBs')
,@cursSysSrvPrinName_Status = CURSOR_STATUS('GLOBAL','cursSysSrvPrinName')
,@cursDatabases_Status = CURSOR_STATUS('GLOBAL','cursDatabases')
,@cursPermisTable_Status = CURSOR_STATUS('GLOBAL','cursPermisTable')
,@cursDNamesAsColumns_Status = CURSOR_STATUS('GLOBAL','cursPermisTable')
;
IF @cursDBs_Status > -2
BEGIN
CLOSE cursDBs ;
DEALLOCATE cursDBs ;
END
IF @cursSysSrvPrinName_Status > -2
BEGIN
CLOSE cursSysSrvPrinName ;
DEALLOCATE cursSysSrvPrinName ;
END
IF @cursDatabases_Status > -2
BEGIN
CLOSE cursDatabases ;
DEALLOCATE cursDatabases ;
END
IF @cursPermisTable_Status > -2
BEGIN
CLOSE cursPermisTable ;
DEALLOCATE cursPermisTable ;
END
IF @cursDNamesAsColumns_Status > -2
BEGIN
CLOSE cursDNamesAsColumns ;
DEALLOCATE cursDNamesAsColumns ;
END
SELECT ErrorNum = ERROR_NUMBER(),ErrorMsg = ERROR_MESSAGE() ;
END CATCH
GO
/*
EXEC [master].dbo.uspGetPermissionsOfAllLogins_DBsOnColumns ;
*/
我尝试了几乎所有这些功能，但很快就注意到缺少一些功能，尤其是系统管理员用户。在我们即将进行的审计中，有这样一个洞会很不好看，所以我想出了这个
USE master
GO
SELECT DISTINCT
p.name AS [loginname] ,
--p.type,
p.type_desc ,
p.is_disabled,
s.sysadmin,
sp.permission_name
FROM sys.server_principals p
INNER JOIN sys.syslogins s ON p.sid = s.sid
INNER JOIN sys.server_permissions sp ON p.principal_id = sp.grantee_principal_id
WHERE p.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP')
-- Logins that are not process logins
AND p.name NOT LIKE '##%'
ORDER BY p.name
GO
CREATE PROCEDURE Get_permission
AS
DECLARE @db_name VARCHAR(200),
@sql_text VARCHAR(max)
SET @sql_text='Create table ##db_name (user_name varchar(max),'
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
OPEN db_cursor
FETCH next FROM db_cursor INTO @db_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql_text=@sql_text + @db_name + ' varchar(max),'
FETCH next FROM db_cursor INTO @db_name
END
CLOSE db_cursor
SET @sql_text=@sql_text + 'Server_perm varchar(max))'
EXEC (@sql_text)
DEALLOCATE db_cursor
DECLARE @RoleName VARCHAR(50)
DECLARE @UserName VARCHAR(50)
DECLARE @CMD VARCHAR(1000)
CREATE TABLE #permission
(
user_name VARCHAR(50),
databasename VARCHAR(50),
role VARCHAR(50)
)
DECLARE longspcur CURSOR FOR
SELECT name
FROM sys.server_principals
WHERE type IN ( 'S', 'U', 'G' )
AND principal_id > 4
AND name NOT LIKE '##%'
AND name <> 'NT AUTHORITY\SYSTEM'
AND name <> 'ONDEMAND\Administrator'
AND name NOT LIKE 'steel%'
OPEN longspcur
FETCH next FROM longspcur INTO @UserName
WHILE @@FETCH_STATUS = 0
BEGIN
CREATE TABLE #userroles_kk
(
databasename VARCHAR(50),
role VARCHAR(50)
)
CREATE TABLE #rolemember_kk
(
dbrole VARCHAR(100),
membername VARCHAR(100),
membersid VARBINARY(2048)
)
SET @CMD = 'use ? truncate table #RoleMember_kk insert into #RoleMember_kk exec sp_helprolemember insert into #UserRoles_kk (DatabaseName, Role) select db_name(), dbRole from #RoleMember_kk where MemberName = ''' + @UserName + ''''
EXEC Sp_msforeachdb
@CMD
INSERT INTO #permission
SELECT @UserName 'user',
b.name,
u.role
FROM sys.sysdatabases b
LEFT OUTER JOIN #userroles_kk u
ON u.databasename = b.name --and u.Role='db_owner'
ORDER BY 1
DROP TABLE #userroles_kk;
DROP TABLE #rolemember_kk;
FETCH next FROM longspcur INTO @UserName
END
CLOSE longspcur
DEALLOCATE longspcur
TRUNCATE TABLE ##db_name
DECLARE @d1 VARCHAR(max),
@d2 VARCHAR(max),
@d3 VARCHAR(max),
@ss VARCHAR(max)
DECLARE perm_cur CURSOR FOR
SELECT *
FROM #permission
ORDER BY 2 DESC
OPEN perm_cur
FETCH next FROM perm_cur INTO @d1, @d2, @d3
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT EXISTS(SELECT 1
FROM ##db_name
WHERE user_name = @d1)
BEGIN
SET @ss='insert into ##db_name(user_name) values ('''
+ @d1 + ''')'
EXEC (@ss)
SET @ss='update ##db_name set ' + @d2 + '=''' + @d3
+ ''' where user_name=''' + @d1 + ''''
EXEC (@ss)
END
ELSE
BEGIN
DECLARE @var NVARCHAR(max),
@ParmDefinition NVARCHAR(max),
@var1 NVARCHAR(max)
SET @var = N'select @var1=' + @d2
+ ' from ##db_name where USER_NAME=''' + @d1
+ '''';
SET @ParmDefinition = N'@var1 nvarchar(300) OUTPUT';
EXECUTE Sp_executesql
@var,
@ParmDefinition,
@var1=@var1 output;
SET @var1=Isnull(@var1, ' ')
SET @var= ' update ##db_name set ' + @d2 + '=''' + @var1 + ' '
+ @d3 + ''' where user_name=''' + @d1 + ''' '
EXEC (@var)
END
FETCH next FROM perm_cur INTO @d1, @d2, @d3
END
CLOSE perm_cur
DEALLOCATE perm_cur
SELECT *
FROM ##db_name
DROP TABLE ##db_name
DROP TABLE #permission
非常感谢出色的审计脚本。
我强烈建议审计用户使用很棒的Kenneth Fisher (b | t)存储过程:
sp_DBPermissions sp_SrvPermissions
推荐文章
- 确定记录是否存在的最快方法
- 从现有模式生成表关系图(SQL Server)
- 我如何循环通过一组记录在SQL Server?
- 数据库和模式的区别
- 如何在SQL Server中一次更改多个列
- 外键约束可能导致循环或多条级联路径?
- 如何选择每一行的列值不是独特的
- nvarchar(max)非文本
- Android M权限:对shouldShowRequestPermissionRationale()函数的使用感到困惑
- 在SQL Server 2008 R2中重命名数据库时出错
- 将数据复制到另一个表中
- 如何在SQL中选择表的最后一条记录?
- 修改列，添加默认约束
- 在存储过程中使用“SET XACT_ABORT ON”有什么好处?
- 如何检查SQL Server文本列是否为空?
