select dp.NAME AS principal_name,
dp.type_desc AS principal_type_desc,
o.NAME AS object_name,
p.permission_name,
p.state_desc AS permission_state_desc
from sys.database_permissions p
left OUTER JOIN sys.all_objects o
on p.major_id = o.OBJECT_ID
inner JOIN sys.database_principals dp
on p.grantee_principal_id = dp.principal_id
with mycte as
(
select cast('1900-01-01' as datetime) DateValue
union all
select DateValue + 1
from mycte
where DateValue + 1 < '2050-12-31'
)
select DateValue
from mycte
OPTION (MAXRECURSION 0)[@more@]
dp.type_desc AS principal_type_desc,
o.NAME AS object_name,
p.permission_name,
p.state_desc AS permission_state_desc
from sys.database_permissions p
left OUTER JOIN sys.all_objects o
on p.major_id = o.OBJECT_ID
inner JOIN sys.database_principals dp
on p.grantee_principal_id = dp.principal_id
with mycte as
(
select cast('1900-01-01' as datetime) DateValue
union all
select DateValue + 1
from mycte
where DateValue + 1 < '2050-12-31'
)
select DateValue
from mycte
OPTION (MAXRECURSION 0)[@more@]
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/7667023/viewspace-1007428/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
-
博文量25
-
访问量170885
最新文章
- Scheduling Jobs in SQL Server Express
- Create database sample
- how to decrypt an encrypted stored proc in 2005
- The Curse and Blessings of Dynamic SQL
- 日记传送
- Transact-SQL Query Performance Tuning Tips
- Concatenating row values in Transact-SQL
- Ranking data in Transact SQL
- More with SQL Server 2005 : Top n Per Group, Paging, and Common Table Expressions
- Dynamic SQL CrossTab Stored Procedure ---it very useful
