2

I have a script for scripting out user permissions I use on SQL 2014 that works fine but when I try on SQL 2008, I am getting the following error:

Msg 515, Level 16, State 2, Line 4 Cannot insert the value NULL into column 'UserName', table '@DB_USers'; column does not allow nulls. INSERT fails. The statement has been terminated.

(0 row(s) affected)

DECLARE @DB_USers TABLE
(DBName sysname, UserName sysname, LoginType sysname, AssociatedRole varchar(max),create_date datetime,modify_date datetime)

INSERT @DB_USers
EXEC sp_MSforeachdb

'
use [?]
SELECT ''?'' AS DB_Name,
case prin.name when ''dbo'' then prin.name + '' (''+ (select SUSER_SNAME(owner_sid) from master.sys.databases where name =''?'') + '')'' else prin.name end AS UserName,
prin.type_desc AS LoginType,
isnull(USER_NAME(mem.role_principal_id),'''') AS AssociatedRole ,create_date,modify_date
FROM sys.database_principals prin
LEFT OUTER JOIN sys.database_role_members mem ON prin.principal_id=mem.member_principal_id
WHERE prin.sid IS NOT NULL and prin.sid NOT IN (0x00) and
prin.is_fixed_role <> 1 AND prin.name NOT LIKE ''##%'''

SELECT

dbname,username ,logintype ,create_date ,modify_date ,

STUFF(

(

SELECT ',' + CONVERT(VARCHAR(500),associatedrole)

FROM @DB_USers user2

WHERE

user1.DBName=user2.DBName AND user1.UserName=user2.UserName

FOR XML PATH('')

)

,1,1,'') AS Permissions_user

FROM @DB_USers user1

GROUP BY

dbname,username ,logintype ,create_date ,modify_date

ORDER BY DBName,username
  • Did you compare the output on both queries to make sure the columns are the same in the DMVs? – Randolph West Nov 14 '16 at 20:16
7

I didn't take a really hard look at this but you're defining the DBName, UserName, and LoginType columns in @DB_USers as sysname. The type sysname is defined as NOT NULL.

What is SYSNAME data type in SQL Server?

As to why you're getting a NULL value you're going to have to check the dataset you're running against.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.