22,297
社区成员
发帖
与我相关
我的任务
分享SQL2005中的权限查询问题
SQL2005中怎么用SQL语句或者存储过程查询到某个用户名对某数据库中的某表中的某字段的增删改查及其他的所有权限？
...全文
请发表友善的回复…
发表回复
LongRui888 2014-01-20
- 打赏
- 举报
多谢大虾，你给的东西很有启发，只是我想根据不同的用户名来查询该用户名下的权限，用户名这个变量该怎么加进去呢[/quote]
可以这样：
declare @login nvarchar(100)
set @login = 'wcc'
--2.模拟登陆wcc
execute as login=@login
lxyspy 2014-01-20
- 打赏
- 举报
多谢大虾，你给的东西很有启发，只是我想根据不同的用户名来查询该用户名下的权限，用户名这个变量该怎么加进去呢
LongRui888 2014-01-17
- 打赏
- 举报
给你一个例子：
--1.创建登陆
create login wcc
with password = '123'
--2.模拟登陆wcc
execute as login='wcc'
--3.查询权限,发现没有任何权限
select *
from sys.fn_my_permissions('spgj_yjm',--安全对象的名称
'object') --要列出权限的安全对象类
where subentity_name <>''
go
--4.回到当前的登录名,也就是sa用户
revert
go
--5.返回了对表spgj_yjm的各个列的权限
select *
from sys.fn_my_permissions('spgj_yjm',--安全对象的名称
'object') --要列出权限的安全对象类
where subentity_name <>''
/*
entity_name subentity_name permission_name
spgj_yjm ydh SELECT
spgj_yjm yjm SELECT
spgj_yjm flag SELECT
spgj_yjm ydh UPDATE
spgj_yjm yjm UPDATE
spgj_yjm flag UPDATE
spgj_yjm ydh REFERENCES
spgj_yjm yjm REFERENCES
spgj_yjm flag REFERENCES
*/LongRui888 2014-01-17
- 打赏
- 举报
请教大虾这个怎么用啊，object这个用什么来代替？我想得到某个SQL用户名是否对表里某个字段拥有增删改查的权限，谢谢您的回复[/quote]
这个代码是显示了spgj_yjm表中，对列的权限：
select *
from sys.fn_my_permissions('spgj_yjm',--安全对象的名称
'object') --要列出权限的安全对象类
where subentity_name <>''
/*
entity_name subentity_name permission_name
spgj_yjm ydh SELECT
spgj_yjm yjm SELECT
spgj_yjm flag SELECT
spgj_yjm ydh UPDATE
spgj_yjm yjm UPDATE
spgj_yjm flag UPDATE
spgj_yjm ydh REFERENCES
spgj_yjm yjm REFERENCES
spgj_yjm flag REFERENCES
*/
發糞塗牆 2014-01-17
- 打赏
- 举报
SELECT * FROM fn_my_permissions('表名', 'OBJECT');
这个查出来的可用不？可用的话可以考虑用游标遍历全库
lxyspy 2014-01-17
- 打赏
- 举报
我也执行了一下，出现三个查询结果集，但是没看到字段的权限在哪里，哎，我太弱了
lxyspy 2014-01-17
- 打赏
- 举报
请教大虾这个怎么用啊，object这个用什么来代替？我想得到某个SQL用户名是否对表里某个字段拥有增删改查的权限，谢谢您的回复
發糞塗牆 2014-01-17
- 打赏
- 举报
那是国外大牛的代码，执行了一下可用
lxyspy 2014-01-17
- 打赏
- 举报
哇，这个这么复杂么，看来是我太差劲了，多谢斑竹提点，我研究一下你上面给出的这么长的代码段，谢谢
LongRui888 2014-01-17
- 打赏
- 举报
用这个试试：
select *
from sys.fn_my_permissions('表的名称',--安全对象的名称
'object') --要列出权限的安全对象类
發糞塗牆 2014-01-17
- 打赏
- 举报
你这个要是能做出来，都差不多可以拿去卖 了
發糞塗牆 2014-01-17
- 打赏
- 举报
你这个是再做一个SSMS的级别了，有需要搞那么复杂？要是给客户用的，不建议让客户有DDL的权限，如果是给内部人员用的，直接用SSMS就可以了，或者下个第三方软件
lxyspy 2014-01-17
- 打赏
- 举报
背景是这样，本来打算做一个给数据库授权的功能模块，要求精确到对数据库中某个表中的某个或多个字段进行增删改查的授权，现在授权功能已经实现，但是想查询某个用户具体对哪些数据库中的哪些表中的哪些字段拥有增删改查中的一项或者多项权限，这个该怎么处理呢？谢谢斑竹的回复。
發糞塗牆 2014-01-17
- 打赏
- 举报
/**************************************
Name: The Complete SQL Server 2005 Permissions Report
Author: Richard Lu
Date Created: 05/04/2009
Description:
The script provides detailed access permission report on all levels, i.e. server, database,
object and column, of SQL Server 2005 for all logins. Users would also be able to customize
the report result by specifying two parameters: @loginName and @dbName at the beginning of
the script to retrieve permission assignments on particular logins and databases.
Reports of permissions possessed by System fixed roles, e.g. public and SQLAgentOperatorRole,
can be excluded from the report by simply removing the comment marks on all the "type NOT IN ('R')"
condition in the Where-clause in the script.
****************************************/
USE master
GO
SET NOCOUNT ON
DECLARE @loginName sysname, @dbName sysname
/* Set the two Parameters here. By defaul All logins and databases will be reported */
SET @loginName = '%' -- e.g. 'NorthAmerica\JSmith1'
SET @dbName = '%' -- e.g. 'ReportServer'
-- Get Server Role Level Info
SELECT
suser_sname(p.sid) AS Login_Name,
p.type_desc AS [Login_Type],
r.is_disabled,
r.name AS Permission_Name,
r.type_desc AS Permission_Type,
p.create_date, p.modify_date
FROM
master.sys.server_principals r
LEFT OUTER JOIN master.sys.server_role_members m ON r.principal_id = m.role_principal_id
LEFT OUTER JOIN master.sys.server_principals p ON p.principal_id = m.member_principal_id
WHERE p.name LIKE @loginName
--AND p.type NOT IN ('R')
UNION
SELECT
suser_sname(prin.sid) AS Login_Name,
prin.type_desc AS [Login_Type],
prin.is_disabled,
perm.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS AS Permission_Name,
perm.class_desc AS Permission_Type,
prin.create_date, prin.modify_date
FROM master.sys.server_permissions perm
LEFT OUTER JOIN master.sys.server_principals prin ON perm.grantee_principal_id = prin.principal_id
LEFT OUTER JOIN master.sys.server_principals grantor ON perm.grantor_principal_id = grantor.principal_id
WHERE prin.name LIKE @loginName
--AND prin.type NOT IN ('R')
ORDER BY Login_Name, r.name
-- Retrieve DB Role Level Info
DECLARE @DBRolePermissions TABLE(
DatabaseName varchar(300),
Principal_Name sysname,
Login_Name sysname NULL,
DB_RoleMember varchar(300),
Permission_Type sysname)
INSERT INTO @DBRolePermissions
EXEC sp_MSforeachdb '
SELECT DISTINCT ''?'' AS DatabaseName, users.Name AS UserName, suser_sname(users.sid) AS Login_Name,
roles.Name AS Role_Member_Name, roles.type_desc
FROM [?].sys.database_role_members r
LEFT OUTER JOIN [?].sys.database_principals users on r.member_principal_id = users.principal_id
LEFT OUTER JOIN [?].sys.database_principals roles on r.role_principal_id = roles.principal_id
--WHERE users.type not in (''R'')'
-- Capture permissions generated FROM sys.database_permissions
INSERT INTO @DBRolePermissions
EXEC sp_msforeachdb '
SELECT DISTINCT ''?'' AS DatabaseName, users.Name AS UserName, suser_sname(users.sid) AS Login_Name,
r.Permission_Name AS DB_RoleMember, r.class_desc
FROM [?].sys.database_permissions r
LEFT OUTER JOIN [?].sys.database_principals users on r.Grantee_principal_id = users.principal_id
WHERE r.class_desc = ''DATABASE'''
SELECT DISTINCT Principal_Name, Login_Name, DatabaseName, DB_RoleMember AS Permission_Name, Permission_Type
FROM @DBRolePermissions
WHERE (ISNULL(Login_Name, '') LIKE @loginName OR ISNULL(Principal_Name, '') LIKE @loginName)
AND DatabaseName LIKE @dbName
ORDER BY Principal_Name, DatabaseName, DB_RoleMember
-- Get Object/Column Level Permissions
DECLARE @ObjectPermissions TABLE (
DatabaseName varchar(300),
Principal_Name sysname,
Login_Name sysname NULL,
ObjectType sysname,
ObjectName varchar(500),
PermissionName varchar(300),
state_desc varchar(300),
Grantor varchar(300))
DECLARE @CurrentDB sysname, @SQLCmd varchar(4000)
DECLARE DBCursor CURSOR FOR
SELECT [name]
FROM master.sys.databases
WHERE state = 0 AND [name] LIKE @dbName
OPEN DBCursor
FETCH NEXT FROM DBCursor INTO @CurrentDB
WHILE (@@fetch_status = 0)
BEGIN
-- Capture permissions generated FROM sys.database_permissions
SET @SQLCmd = 'USE [' + @CurrentDB + ']
SELECT ''' + @CurrentDB + ''' AS DatabaseName,
grantee.name AS Principal_Name,
suser_sname(grantee.sid) AS Login_Name,
p.class_desc AS ObjectType,
CASE p.class_desc
WHEN ''SCHEMA'' THEN CONVERT(sysname, (SELECT objects.name FROM sys.schemas objects WHERE objects.schema_id = p.major_id)) COLLATE Latin1_General_CI_AS
WHEN ''DATABASE'' THEN CONVERT(sysname, p.class_desc) COLLATE Latin1_General_CI_AS
WHEN ''OBJECT_OR_COLUMN'' THEN
CONVERT(sysname, ISNULL((SELECT o.name + ''.'' + objects.name FROM sys.columns objects WHERE objects.[object_id] = p.major_id and objects.column_id = p.minor_id), o.name)) COLLATE Latin1_General_CI_AS
WHEN ''DATABASE_PRINCIPAL'' THEN CONVERT(sysname, (SELECT objects.name FROM sys.database_principals objects WHERE objects.principal_id = p.major_id)) COLLATE Latin1_General_CI_AS
WHEN ''ASSEMBLY'' THEN CONVERT(sysname, (SELECT objects.name FROM sys.assemblies objects WHERE objects.assembly_id = p.major_id)) COLLATE Latin1_General_CI_AS
WHEN ''TYPE'' THEN CONVERT(sysname, (SELECT objects.name FROM sys.types objects WHERE objects.user_type_id = p.major_id)) COLLATE Latin1_General_CI_AS
WHEN ''XML_SCHEMA_COLLECTION'' THEN CONVERT(sysname, (SELECT objects.name FROM sys.xml_schema_collections objects WHERE objects.xml_collection_id = p.major_id)) COLLATE Latin1_General_CI_AS
WHEN ''MESSAGE_TYPE'' THEN CONVERT(sysname, (SELECT objects.name FROM sys.service_message_types objects WHERE objects.message_type_id = p.major_id)) COLLATE Latin1_General_CI_AS
WHEN ''SERVICE_CONTRACT'' THEN CONVERT(sysname, (SELECT objects.name FROM sys.service_contracts objects WHERE objects.service_contract_id = p.major_id)) COLLATE Latin1_General_CI_AS
WHEN ''REMOTE_SERVICE_BINDING'' THEN CONVERT(sysname, (SELECT distinct objects.name FROM sys.remote_service_bindings objects WHERE objects.remote_service_binding_id = p.major_id)) COLLATE Latin1_General_CI_AS
WHEN ''ROUTE'' THEN CONVERT(sysname, (SELECT objects.name FROM sys.routes objects WHERE objects.route_id = p.major_id)) COLLATE Latin1_General_CI_AS
WHEN ''FULLTEXT_CATALOG'' THEN CONVERT(sysname, (SELECT objects.name FROM sys.fulltext_catalogs objects WHERE objects.fulltext_catalog_id = p.major_id)) COLLATE Latin1_General_CI_AS
WHEN ''SYMMETRIC_KEY'' THEN CONVERT(sysname, (SELECT distinct objects.name FROM sys.symmetric_keys objects WHERE objects.symmetric_key_id = p.major_id)) COLLATE Latin1_General_CI_AS
WHEN ''CERTIFICATE'' THEN CONVERT(sysname, (SELECT distinct objects.name FROM sys.certificates objects WHERE objects.certificate_id = p.major_id)) COLLATE Latin1_General_CI_AS
WHEN ''ASYMMETRIC_KEY'' THEN CONVERT(sysname, (SELECT objects.name FROM sys.asymmetric_keys objects WHERE objects.asymmetric_key_id = p.major_id)) COLLATE Latin1_General_CI_AS
ELSE CONVERT(sysname, ''n\a'') COLLATE Latin1_General_CI_AS
END AS ObjectName,
Permission_name, state_desc, grantor.name AS Grantor
FROM [' + @CurrentDB + '].sys.database_permissions p
LEFT OUTER JOIN [' + @CurrentDB + '].sys.database_principals grantee on p.grantee_principal_id = grantee.principal_id
LEFT OUTER JOIN [' + @CurrentDB + '].sys.all_objects o on p.major_id = o.[object_id]
LEFT OUTER JOIN [' + @CurrentDB + '].sys.database_principals grantor on p.grantor_principal_id = grantor.principal_id
WHERE p.class_desc not in (''DATABASE'')
--AND grantee.type not in (''R'') '
INSERT INTO @ObjectPermissions
EXEC (@SQLCmd)
FETCH NEXT FROM DBCursor into @CurrentDB
END
CLOSE DBCursor
DEALLOCATE DBCursor
SELECT DISTINCT Principal_Name, Login_Name, DatabaseName, ObjectName, ObjectType,
PermissionName, state_desc, Grantor
FROM @ObjectPermissions
WHERE ISNULL(Login_Name, '') like @loginName OR ISNULL(Principal_Name, '') like @loginName
ORDER BY DatabaseName, Principal_Name, ObjectName, PermissionName
lxyspy 2014-01-17
- 打赏
- 举报
查不到具体对哪个字段拥有权限么？大虾帮帮忙,好么
發糞塗牆 2014-01-17
- 打赏
- 举报
我只有这个程度的
SELECT dp.class_desc ,
dp.permission_name ,
dp.state_desc ,
ObjectName = OBJECT_NAME(major_id) ,
GranteeName = grantee.name ,
GrantorName = grantor.name
FROM sys.database_permissions dp
JOIN sys.database_principals grantee ON dp.grantee_principal_id = grantee.principal_id
JOIN sys.database_principals grantor ON dp.grantor_principal_id = grantor.principal_id
