use msdb go if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pr_RoleInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[pr_RoleInsert] GO create PROCEDURE pr_RoleInsert WITH ENCRYPTION AS BEGIN -- 插入服务器角色 declare @Computername nvarchar(100),@C_ID int,@Create_date datetime set @Computername=convert(nvarchar(100),serverproperty(N'Servername') ) set @computername=replace(@computername,'\','\\') set @create_date=getdate()insert into OPENDATASOURCE( 'SQLOLEDB', 'Data Source=................' ).DataBaseManager.dbo.Tmp_Computer_Role (Computername,LandName,LandType,Is_disabled,LandRole,Create_date,Modify_date,use_flg,RecordCreate_date) select @computername as computername,p.name,p.type,p.is_disabled,isnull(r.name,'')as land_name,p.create_date,p.modify_date,0 as use_flg,@Create_date as createdate FROM sys.server_principals r INNER JOIN sys.server_role_members m ON r.principal_id = m.role_principal_id RIGHT JOIN sys.server_principals p ON p.principal_id = m.member_principal_id WHERE (p.type in ('U', 'G', 'S', 'C', 'K') AND p.principal_id not between 101 and 255 AND p.name <> N'##MS_AgentSigningCertificate##') order by p.name /* p.type 说明: S = SQL 登录名 U = Windows 登录名 G = Windows 组 R = 服务器角色 C = 映射到证书的登录名 K = 映射到非对称密钥的登录名 */ -- 插入用户角色 CREATE TABLE #POWER(db sysname,logins sysname,users sysname,type char(1),create_date datetime,modify_date datetime, roles sysname) DECLARE @DB VARCHAR(100),@SQL VARCHAR(1000) DECLARE o_cursor CURSOR FOR select name from sys.databases where state=0 order by name OPEN o_cursor WHILE (1=1) BEGIN FETCH NEXT FROM o_cursor INTO @DB IF @@FETCH_STATUS < 0 BREAK SET @SQL=' insert into #POWER' +' select '''+@DB+''',u.name,p.name,p.type,p.create_date,p.modify_date , r.name ' +' FROM '+QUOTENAME(@DB)+'.sys.database_principals r' +' INNER JOIN '+QUOTENAME(@DB)+'.sys.database_role_members m ON r.principal_id = m.role_principal_id' +' INNER JOIN '+QUOTENAME(@DB)+'.sys.database_principals p ON p.principal_id = m.member_principal_id' +' INNER JOIN sys.server_principals u ON p.sid=u.sid' EXEC(@SQL) IF @@ERROR<>0 BREAK END CLOSE o_cursor DEALLOCATE o_cursor insert into OPENDATASOURCE( 'SQLOLEDB', 'Data Source=................' ).DataBaseManager.dbo.TMP_Database_Role (Computername,Logins,users,[Db_name],Roles,Create_date,modify_date,Use_Flg,RecordCreate_date) select @computername as computername,logins,users,db,roles,create_date,modify_date,0,@Create_date from #POWER order by logins,users,db drop table #power---单独明细权限-----create table #right(DbName varchar(100),LandName varchar(100),objectName varchar(200),use_type varchar(15),SchemaName varchar(100),PerMission_name varchar(15),Ctrl_Object varchar(100),grantee_type varchar(100),grantor_type varchar(15),Create_date datetime) declare @DbName varchar(200),@string varchar(max)select @DbName='',@string=''declare cur cursor for select name as dbname from master.sys.databases where has_dbaccess(name)=1 open cur while(1=1) begin fetch next from cur into @DbName --fetch next from cur into @computername if @@fetch_status<>0 begin break; end select @string='SELECT '''+@dbName+''', grantee_principal.name as Landname, sp.name as objectName, state_desc as use_type, grantor_principal.name as SchemaName, --prmssn.type , prmssn.permission_name, --sp.type, sp.type_desc as Ctrl_Object, --grantee_principal.type, grantee_principal.type_desc as grantee_type, --grantor_principal.type, grantor_principal.type_desc as grantor_type, grantee_principal.Create_date--,*FROM ['+@DbName+'].sys.all_objects AS sp INNER JOIN ['+@DbName+'].sys.database_permissions AS prmssn ON prmssn.major_id=sp.object_id AND prmssn.minor_id=0 AND prmssn.class=1 INNER JOIN ['+@DbName+'].sys.database_principals AS grantor_principal ON grantor_principal.principal_id = prmssn.grantor_principal_id INNER JOIN ['+@DbName+'].sys.database_principals AS grantee_principal ON grantee_principal.principal_id = prmssn.grantee_principal_id where grantee_principal.name<>''public'' ' --print @string insert into #right exec(@string)end CLOSE cur DEALLOCATE curinsert into OPENDATASOURCE( 'SQLOLEDB', 'Data Source= ).DataBaseManager.dbo.[TMP_DbUserRight] ([Computername],[DbName],[LandName],[objectName],[use_type],[SchemaName],[PerMission_name],[Ctrl_Object],[grantee_type],[grantor_type],[Create_date],RecordCreate_date) select @computername as computername,[DbName],[LandName],[objectName],[use_type],[SchemaName],[PerMission_name],[Ctrl_Object],[grantee_type],[grantor_type],[Create_date],@Create_date from #right where grantee_type<>'DATABASE_ROLE'--select * from #right where grantee_type<>'DATABASE_ROLE' drop table #rightend go我自己以前的代码,你可以拿去改改。
你可以用manager studio 看到自己的权限吧?你看的的时候,同时开跟踪,你就知道啦,哈
select * from fn_my_permissions('tabname', 'object')
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pr_RoleInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[pr_RoleInsert]
GO
create PROCEDURE pr_RoleInsert WITH ENCRYPTION
AS
BEGIN
-- 插入服务器角色
declare @Computername nvarchar(100),@C_ID int,@Create_date datetime
set @Computername=convert(nvarchar(100),serverproperty(N'Servername') )
set @computername=replace(@computername,'\','\\')
set @create_date=getdate()insert into OPENDATASOURCE(
'SQLOLEDB',
'Data Source=................'
).DataBaseManager.dbo.Tmp_Computer_Role
(Computername,LandName,LandType,Is_disabled,LandRole,Create_date,Modify_date,use_flg,RecordCreate_date)
select @computername as computername,p.name,p.type,p.is_disabled,isnull(r.name,'')as land_name,p.create_date,p.modify_date,0 as use_flg,@Create_date as createdate
FROM sys.server_principals r
INNER JOIN sys.server_role_members m ON r.principal_id = m.role_principal_id
RIGHT JOIN sys.server_principals p ON p.principal_id = m.member_principal_id
WHERE
(p.type in ('U', 'G', 'S', 'C', 'K') AND p.principal_id not between 101 and 255 AND p.name <> N'##MS_AgentSigningCertificate##')
order by p.name
/*
p.type 说明:
S = SQL 登录名
U = Windows 登录名
G = Windows 组
R = 服务器角色
C = 映射到证书的登录名
K = 映射到非对称密钥的登录名
*/
-- 插入用户角色
CREATE TABLE #POWER(db sysname,logins sysname,users sysname,type char(1),create_date datetime,modify_date datetime, roles sysname)
DECLARE @DB VARCHAR(100),@SQL VARCHAR(1000)
DECLARE o_cursor CURSOR FOR
select name from sys.databases where state=0 order by name
OPEN o_cursor
WHILE (1=1)
BEGIN
FETCH NEXT FROM o_cursor INTO @DB
IF @@FETCH_STATUS < 0 BREAK
SET @SQL=' insert into #POWER'
+' select '''+@DB+''',u.name,p.name,p.type,p.create_date,p.modify_date , r.name '
+' FROM '+QUOTENAME(@DB)+'.sys.database_principals r'
+' INNER JOIN '+QUOTENAME(@DB)+'.sys.database_role_members m ON r.principal_id = m.role_principal_id'
+' INNER JOIN '+QUOTENAME(@DB)+'.sys.database_principals p ON p.principal_id = m.member_principal_id'
+' INNER JOIN sys.server_principals u ON p.sid=u.sid'
EXEC(@SQL)
IF @@ERROR<>0 BREAK
END
CLOSE o_cursor
DEALLOCATE o_cursor
insert into OPENDATASOURCE(
'SQLOLEDB',
'Data Source=................'
).DataBaseManager.dbo.TMP_Database_Role
(Computername,Logins,users,[Db_name],Roles,Create_date,modify_date,Use_Flg,RecordCreate_date)
select @computername as computername,logins,users,db,roles,create_date,modify_date,0,@Create_date
from #POWER
order by logins,users,db
drop table #power---单独明细权限-----create table #right(DbName varchar(100),LandName varchar(100),objectName varchar(200),use_type varchar(15),SchemaName varchar(100),PerMission_name varchar(15),Ctrl_Object varchar(100),grantee_type varchar(100),grantor_type varchar(15),Create_date datetime)
declare @DbName varchar(200),@string varchar(max)select @DbName='',@string=''declare cur cursor for
select name as dbname from master.sys.databases where has_dbaccess(name)=1 open cur
while(1=1)
begin
fetch next from cur into @DbName
--fetch next from cur into @computername if @@fetch_status<>0
begin
break;
end
select @string='SELECT '''+@dbName+''',
grantee_principal.name as Landname,
sp.name as objectName,
state_desc as use_type,
grantor_principal.name as SchemaName,
--prmssn.type ,
prmssn.permission_name,
--sp.type,
sp.type_desc as Ctrl_Object,
--grantee_principal.type,
grantee_principal.type_desc as grantee_type,
--grantor_principal.type,
grantor_principal.type_desc as grantor_type,
grantee_principal.Create_date--,*FROM
['+@DbName+'].sys.all_objects AS sp
INNER JOIN ['+@DbName+'].sys.database_permissions AS prmssn ON prmssn.major_id=sp.object_id AND prmssn.minor_id=0 AND prmssn.class=1
INNER JOIN ['+@DbName+'].sys.database_principals AS grantor_principal ON grantor_principal.principal_id = prmssn.grantor_principal_id
INNER JOIN ['+@DbName+'].sys.database_principals AS grantee_principal ON grantee_principal.principal_id = prmssn.grantee_principal_id
where grantee_principal.name<>''public''
'
--print @string
insert into #right exec(@string)end
CLOSE cur
DEALLOCATE curinsert into OPENDATASOURCE(
'SQLOLEDB',
'Data Source= ).DataBaseManager.dbo.[TMP_DbUserRight]
([Computername],[DbName],[LandName],[objectName],[use_type],[SchemaName],[PerMission_name],[Ctrl_Object],[grantee_type],[grantor_type],[Create_date],RecordCreate_date)
select @computername as computername,[DbName],[LandName],[objectName],[use_type],[SchemaName],[PerMission_name],[Ctrl_Object],[grantee_type],[grantor_type],[Create_date],@Create_date
from #right where grantee_type<>'DATABASE_ROLE'--select * from #right where grantee_type<>'DATABASE_ROLE'
drop table #rightend
go我自己以前的代码,你可以拿去改改。
select * from fn_my_permissions('tabname', 'object')