Hello all!
I have strange proble which I'm trying to fix.
I have a job, which calls stored procedure.
Use msdb
go
SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
go
EXECUTE [dbo].[Job Bevis]
go
[dbo].[Job Bevis] listing:
USE [msdb]
GO
/****** Object: StoredProcedure [dbo].[Job Bevis] Script Date: 05/12/2010 12:00:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Job Bevis] AS select @@SERVERNAME 'Servername'
-- 1. Account
select 'Returns information about the accounts of MS SQL Server ' '1. Account'
exec master..sp_helplogins
-- 2. Server Role
select 'Returns information about the members per Server role' '2. Server Role'
exec master..sp_helpsrvrolemember
-- 3. Users per Database
select 'List of special users per database' '3. Users per Database'
declare
@name sysname
,@SQL nvarchar(600)
if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#tmpTable'))
drop table #tmpTable
CREATE TABLE #tmpTable (
[DATABASE_NAME] sysname NOT NULL
, [USER_NAME] sysname NOT NULL
, [ROLE_NAME] sysname NOT NULL)
declare c1 cursor for
select name from master.dbo.sysdatabases open c1 fetch c1 into @name
while @@fetch_status >= 0
begin
select @SQL = 'insert into #tmpTable
select N'''+ @name + ''', a.name, c.name from ' + QuoteName(@name) + '.dbo.sysusers a
join ' + QuoteName(@name) + '.dbo.sysmembers b on b.memberuid = a.uid
join ' + QuoteName(@name) + '.dbo.sysusers c on c.uid = b.groupuid
where a.name != ''dbo'''
/* Insert row for each database */
execute (@SQL)
fetch c1 into @name
end
close c1
deallocate c1
select * from #tmpTable
drop table #tmpTable
-- 4. Permissions Tables
select 'Returns information about user permission for tables in the database' '4. Permissions Tables'
select '"4. Permissions Tables" Doesnt function after SQL Server 2005 upgrade 2009-06-01. ITAM has knowledge about this.'
-- exec tplvpv.dbo.SP_Job_Bevis
-- 5. Permissions Views
select 'Returns information about user permission for views in the database' '5. Permissions Views'
if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#tmp_helprotect'))
drop table #tmp_helprotect
create table #tmp_helprotect
(Owner varchar(25)
,Object varchar(50)
,Grantee varchar(25)
,Grantor varchar(25)
,ProtectType varchar(25)
,[Action] varchar(25)
,[Column] varchar(25))
insert into #tmp_helprotect exec sp_helprotect
select Grantee, Object, ProtectType, [Action],[Column]
from #tmp_helprotect h JOIN sysobjects so ON substring(so.name,1,30)=h.Object and so.xtype='v' and h.[Action]!='Select'
order by 1
drop table #tmp_helprotect
Job fails with next error:
(41 row(s) affected)
(247 row(s) affected)
Msg 8152, Level 16, State 2, Line 3
String or binary data would be truncated.
Anyone have ideas?
Thanks in advance.
I have strange proble which I'm trying to fix.
I have a job, which calls stored procedure.
Use msdb
go
SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
go
EXECUTE [dbo].[Job Bevis]
go
[dbo].[Job Bevis] listing:
USE [msdb]
GO
/****** Object: StoredProcedure [dbo].[Job Bevis] Script Date: 05/12/2010 12:00:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Job Bevis] AS select @@SERVERNAME 'Servername'
-- 1. Account
select 'Returns information about the accounts of MS SQL Server ' '1. Account'
exec master..sp_helplogins
-- 2. Server Role
select 'Returns information about the members per Server role' '2. Server Role'
exec master..sp_helpsrvrolemember
-- 3. Users per Database
select 'List of special users per database' '3. Users per Database'
declare
@name sysname
,@SQL nvarchar(600)
if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#tmpTable'))
drop table #tmpTable
CREATE TABLE #tmpTable (
[DATABASE_NAME] sysname NOT NULL
, [USER_NAME] sysname NOT NULL
, [ROLE_NAME] sysname NOT NULL)
declare c1 cursor for
select name from master.dbo.sysdatabases open c1 fetch c1 into @name
while @@fetch_status >= 0
begin
select @SQL = 'insert into #tmpTable
select N'''+ @name + ''', a.name, c.name from ' + QuoteName(@name) + '.dbo.sysusers a
join ' + QuoteName(@name) + '.dbo.sysmembers b on b.memberuid = a.uid
join ' + QuoteName(@name) + '.dbo.sysusers c on c.uid = b.groupuid
where a.name != ''dbo'''
/* Insert row for each database */
execute (@SQL)
fetch c1 into @name
end
close c1
deallocate c1
select * from #tmpTable
drop table #tmpTable
-- 4. Permissions Tables
select 'Returns information about user permission for tables in the database' '4. Permissions Tables'
select '"4. Permissions Tables" Doesnt function after SQL Server 2005 upgrade 2009-06-01. ITAM has knowledge about this.'
-- exec tplvpv.dbo.SP_Job_Bevis
-- 5. Permissions Views
select 'Returns information about user permission for views in the database' '5. Permissions Views'
if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#tmp_helprotect'))
drop table #tmp_helprotect
create table #tmp_helprotect
(Owner varchar(25)
,Object varchar(50)
,Grantee varchar(25)
,Grantor varchar(25)
,ProtectType varchar(25)
,[Action] varchar(25)
,[Column] varchar(25))
insert into #tmp_helprotect exec sp_helprotect
select Grantee, Object, ProtectType, [Action],[Column]
from #tmp_helprotect h JOIN sysobjects so ON substring(so.name,1,30)=h.Object and so.xtype='v' and h.[Action]!='Select'
order by 1
drop table #tmp_helprotect
Job fails with next error:
(41 row(s) affected)
(247 row(s) affected)
Msg 8152, Level 16, State 2, Line 3
String or binary data would be truncated.
Anyone have ideas?
Thanks in advance.