SQL Server Performance Forum – Threads Archive

Script error in 2005

Hi Group, i am running this code for extracting login permissions and role, more kind of auditing. This scripts work fine in SQL 2000 database, however in SQL 2005 it comes up with error messages. Code is given in "green" and script which comes up with error message is given in "red". Any ideas how I can modify and execute this in SQL 2005. Error message is
Msg 8152, Level 16, State 2, Procedure sp_helpsrvrolemember, Line 23
String or binary data would be truncated.
–Use the master database
USE master
go IF OBJECT_ID(‘dbo.spRoleMembers’) IS NOT NULL
DROP PROCEDURE dbo.spRoleMembers
GO CREATE PROCEDURE dbo.spRoleMembers
AS SET NOCOUNT ON –Variables
DECLARE @lngCounter INTEGER
DECLARE @strDBName VARCHAR(50)
DECLARE @strSQL NVARCHAR(4000) –Temp table to hold database and user-define role user names
CREATE TABLE #tRolemember
(
strServerName VARCHAR(50) DEFAULT @@SERVERNAME
,strDBName VARCHAR(50)
,strRoleName VARCHAR(50)
,strUserName VARCHAR(50)
,strUserID VARCHAR(100)
) –Temp table to hold database names
CREATE TABLE #tDBNames
(lngID INTEGER IDENTITY(1,1)
,strDBName VARCHAR(50)
) –Create permanent table
IF OBJECT_ID (‘dbo.tRolemember’) IS NULL
BEGIN
CREATE TABLE dbo.tRolemember
(
strServerName VARCHAR(50)
,strDBName VARCHAR(50)
,strRoleName VARCHAR(50)
,strUserName VARCHAR(50)
,strUserID VARCHAR(100)
)
END –Obtain members of each server role
INSERT INTO #tRolemember (strRoleName, strUserName, strUserID)
EXEC dbo.sp_helpsrvrolemember –Obtain database names
INSERT INTO #tDBNames (strDBName)
SELECT name FROM master.dbo.sysdatabases
SET @lngCounter = @@ROWCOUNT –Loop through databases to obtain members of database roles and user-defined roles
WHILE @lngCounter > 0
BEGIN –Get database name from temp table
SET @strDBName = (SELECT strDBName FROM #tDBNames WHERE lngID = @lngCounter) –Obtain members of each database and user-defined role
SET @strSQL = ‘INSERT INTO #tRolemember (strRoleName, strUserName, strUserID)
EXEC ‘ + @strDBName + ‘.dbo.sp_helprolemember’ EXEC sp_executesql @strSQL –Update database name in temp table
UPDATE #tRolemember
SET strDBName = @strDBName
WHERE strDBName IS NULL SET @lngCounter = @lngCounter – 1 END –Place data into permanent table
INSERT INTO tRolemember
SELECT trm.* FROM #tRolemember trm
LEFT JOIN tRoleMember prm
ON trm.strUserName = prm.strUserName
AND trm.strDBName = prm.strDBName
AND trm.strRoleName = prm.strRoleName
AND trm.strServerName = prm.strServerName
WHERE prm.strServerName IS NULL GO
/**–Test Stored Procedure
EXEC dbo.spRoleMembers PRINT ‘Display by User’
SELECT strUserName, strDBName, strRoleName, strServerName FROM tRolemember
WHERE strUserName <> ‘dbo’
ORDER BY strUserName PRINT ‘Display by Role’
SELECT strRoleName, strDBName, strUserName,strServerName FROM tRolemember
WHERE strUserName <> ‘dbo’
ORDER BY strRoleName PRINT ‘Display by Database’
SELECT strDBName, strRoleName,strUserName, strServerName FROM tRolemember
WHERE strUserName <> ‘dbo’
ORDER BY strDBName **/

change size to following :
strRoleName to 70
strUserName to 256
strUserID to 85
KH

obviously, your data length are not enough.
Reconsider changing those lengths —————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/

I have increased data size to be 250 and even then its not working, looks like its data type instead of data length, HOWEVER NOT SURE WHERE i SHOULD CHANGE THE DATA TYPE. Any ideas ??

I have tested your script by increasing the size to 256 to all the columns and it was successful… Error what you are getting is size related… CREATE TABLE #tRolemember
(
strServerName VARCHAR(256) DEFAULT @@SERVERNAME
,strDBName VARCHAR(256)
,strRoleName VARCHAR(256)
,strUserName VARCHAR(256)
,strUserID VARCHAR(256)
)
CREATE TABLE dbo.tRolemember
(
strServerName VARCHAR(256)
,strDBName VARCHAR(256)
,strRoleName VARCHAR(256)
,strUserName VARCHAR(256)
,strUserID VARCHAR(256)
) MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

It was not working bbefore, but now with this chnge its working.
Thanks group for your support.

That happened because in previous versions SYSNAME minimum length can be less than 30 or 50 and now with SQL 2005 sysname is used for table columns, variables, and stored procedure parameters that store object names and its functionally equivalent to nvarchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />. SQL Server version 6.5 or earlier only supports only smaller identifiers; thus, in earlier versions, sysname is defined as varchar(30). Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of knowledge sharing. <hr noshade size="1">The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.</center>

]]>