一 操作实例
我自己电脑上Sql Server的安全函数如下;
返回调用方对服务器的有效权限的列表;
返回调用方对OA_DB 数据库的有效权限的列表;
返回调用方对OA_DB 数据库内存储过程usersUpdate的有效权限的列表;
列出另一个用户的有效权限
当前用户是sa,没有别的用户,以sa来示例;
返回数据库用户 sa 对 OA_DB 数据库内 bbs 表的有效权限的列表。 调用方需要对用户 sa 具有 IMPERSONATE 权限。
出错;改为对用户dbo则可;
可以是另一个用户;
检查当前用户是否为数据库角色或 Windows 域组的成员;
确定当前用户能否执行 CREATE TABLE 语句;
返回 用户 dbo 的标识号;
找到默认跟踪的文件路径;
二 实例代码
SELECT * FROM fn_my_permissions(NULL, 'SERVER');
GO
USE OA_DB
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
GO
USE OA_DB
SELECT * FROM fn_my_permissions('usersUpdate', 'OBJECT')
ORDER BY subentity_name, permission_name ;
GO
EXECUTE AS USER = 'sa';
SELECT * FROM fn_my_permissions('bbs', 'OBJECT')
ORDER BY subentity_name, permission_name ;
REVERT;
GO
SELECT * FROM fn_my_permissions('Shipping47', 'CERTIFICATE');
GO
USE AdventureWorks2012;
SELECT * FROM fn_my_permissions('ProductDescriptionSchemaCollection',
'XML SCHEMA COLLECTION');
GO
--返回调用方对当前数据库中用户 MalikAr 的有效权限的列表。
SELECT * FROM fn_my_permissions('MalikAr', 'USER');
GO
--列出另一个登录名的有效权限
EXECUTE AS LOGIN = 'WanidaBenshoof';
SELECT * FROM fn_my_permissions('AdventureWorks2012.HumanResources.Employee', 'OBJECT')
ORDER BY subentity_name, permission_name ;
REVERT;
GO
-- Test membership in db_owner and print appropriate message.
IF IS_MEMBER ('db_owner') = 1
PRINT 'Current user is a member of the db_owner role'
ELSE IF IS_MEMBER ('db_owner') = 0
PRINT 'Current user is NOT a member of the db_owner role'
ELSE IF IS_MEMBER ('db_owner') IS NULL
PRINT 'ERROR: Invalid group / role specified';
GO
-- Execute SELECT if user is a member of ADVWORKS\Shipping.
IF IS_MEMBER ('ADVWORKS\Shipping') = 1
SELECT 'User ' + USER + ' is a member of ADVWORKS\Shipping.';
GO
IF PERMISSIONS()&2=2
CREATE TABLE test_table (col1 INT)
ELSE
PRINT 'ERROR: The current user cannot create a table.';
IF PERMISSIONS(OBJECT_ID('AdventureWorks2012.Person.Address','U'))&8=8
PRINT 'The current user can insert data into Person.Address.'
ELSE
PRINT 'ERROR: The current user cannot insert data into Person.Address.';
--对可授予的权限使用 PERMISSIONS 函数
IF PERMISSIONS(OBJECT_ID('AdventureWorks2012.Person.Address','U'))&0x80000=0x80000
PRINT 'INSERT on Person.Address is grantable.'
ELSE
PRINT 'You may not GRANT INSERT permissions on Person.Address.';
USE OA_DB;
SELECT USER_ID('dbo');
GO
select * from ::fn_trace_getinfo(0)
三 参考资料
https://msdn.microsoft.com/zh-cn/LIBRARY/ms186236.aspx
安全函数 (Transact-SQL)
http://www.cnblogs.com/zhijianliutang/p/4113911.html
SQL Server中关于跟踪(Trace)那点事
https://msdn.microsoft.com/en-us/library/ms173875.aspx
sys.fn_trace_getinfo (Transact-SQL)
http://www.codes51.com/article/detail_94669.html
SQL Server中的跟踪(Trace)介绍