November 11, 2011 at 8:23 am
I need a second pair of eyes. I'm missing something really simple in this code and it's driving me nuts. I'm trying to find database roles that don't have a login assigned.
--check if temp table exists and drop
IF (SELECT Object_ID('tempdb..#Databases') ) IS NOT NULL
DROP TABLE #Databases;
-- create table and load db names
IF (SELECT Object_ID('tempdb..#UnusedDatabaseRoles') ) IS NOT NULL
DROP TABLE #UnusedDatabaseRoles;
CREATE TABLE #Databases (DatabaseID INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED, DBName VARCHAR(100));
INSERT INTO #Databases (DBname)
SELECT [Name] AS DBname
FROM MASTER.sys.databases
WHERE [Name] NOT IN ('model', 'tempdb')
AND source_Database_ID IS NULL;
CREATE TABLE #UnusedDatabaseRoles (DatabaseRoleID INT IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED, DBName VARCHAR(100), RoleName VARCHAR(50));
-- declare/init vars
DECLARE @LoopCounter int, @DBName varCHAR(100),@sqlstmt varCHAR(5000);
SET @LoopCounter = 1;
WHILE @LoopCounter <= (SELECT count(*) FROM #Databases)
BEGIN
-- change to new db
SELECT @DBName = DBname FROM #Databases
WHERE DatabaseID = @LoopCounter;
SET @sqlstmt = 'USE ['+@DBName+'];' + CHAR(10);
--db role menbership
SET @sqlstmt = @sqlstmt + CHAR(10)
+ 'INSERT INTO #UnusedDatabaseRoles (DBName, RoleName)
SELECT DISTINCT RP.Name, R.member_principal_ID
FROM sys.database_principals RP
LEFT OUTER JOIN sys.database_role_members R
ON RP.principal_id = R.role_principal_id
WHERE RP.Type_Desc = ''DATABASE_ROLE'' AND R.role_principal_id IS NULL
AND ISNULL(RP.Owning_Principal_ID,0) <> 1';
--PRINT @sqlstmt; --debug syntax
EXECUTE @sqlstmt;
SET @LoopCounter = @LoopCounter + 1;
END;
When I run this with the PRINT statement, I get a printout of things like:
USE [MASTER];
INSERT INTO #UnusedDatabaseRoles (DBName, RoleName)
SELECT DISTINCT RP.Name, R.member_principal_ID
FROM sys.database_principals RP
LEFT OUTER JOIN sys.database_role_members R
ON RP.principal_id = R.role_principal_id
WHERE RP.Type_Desc = 'DATABASE_ROLE' AND R.role_principal_id IS NULL
AND ISNULL(RP.Owning_Principal_ID,0) <> 1
I can then run the above code with no errors. When I run with the PRINT commented out and using the EXEC statement, I get the following syntax error:
(44 row(s) affected)
Msg 203, Level 16, State 2, Line 44
The name 'USE [MASTER];
INSERT INTO #UnusedDatabaseRoles (DBName, RoleName)
SELECT DISTINCT RP.Name, R.member_principal_ID
FROM sys.database_principals RP
LEFT OUTER JOIN sys.database_role_members R
ON RP.principal_id = R.role_principal_id
WHERE RP.Type_Desc = 'DATABASE_ROLE' AND R.role_principal_id IS NULL
AND ISNULL(RP.Owning_Principal_ID,0) <> 1' is not a valid identifier.
I know I've missed something silly. Can someone help me spot the error please?
November 11, 2011 at 8:30 am
You're missing brackets Brandie: -
EXECUTE (@sqlstmt);
November 11, 2011 at 8:31 am
kick yourself:
EXECUTE @sqlstmt looks for a stored procedure.
EXECUTE (@sqlstmt) executed they dynamic query.
Lowell
November 11, 2011 at 8:33 am
GAH!
Thank you, guys. I knew it was something simple.
EDIT: I was also looking at the wrong bit of code in my error search.. DOH.
November 11, 2011 at 8:56 am
Ah. I made a couple of different errors on the above code (non-syntax). I'll post a corrected version up later for anyone wanting to use it.
EDIT:
Corrected code that prints the database and role names (instead of role and member_principal_id) and excludes non-fixed db roles that are system built (public, db_dtsadmin, etc.).
[Code]
--check if temp table exists and drop
IF (SELECT Object_ID('tempdb..#Databases') ) IS NOT NULL
DROP TABLE #Databases;
-- create table and load db names
IF (SELECT Object_ID('tempdb..##UnusedDatabaseRoles') ) IS NOT NULL
DROP TABLE ##UnusedDatabaseRoles;
CREATE TABLE #Databases (DatabaseID INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED, DBName VARCHAR(100));
INSERT INTO #Databases (DBname)
SELECT [Name] AS DBname
FROM MASTER.sys.databases
WHERE [Name] NOT IN ('model', 'tempdb')
AND source_Database_ID IS NULL;
CREATE TABLE ##UnusedDatabaseRoles (--DatabaseRoleID INT IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED,
DBName VARCHAR(100), RoleName VARCHAR(50));
-- declare/init vars
DECLARE @LoopCounter int, @DBName varCHAR(100),@sqlstmt varCHAR(5000);
SET @LoopCounter = 1;
WHILE @LoopCounter <= (SELECT COUNT(*) FROM #Databases)
BEGIN
-- change to new db
SELECT @DBName = DBname FROM #Databases
WHERE DatabaseID = @LoopCounter;
SET @sqlstmt = 'USE ['+@DBName+'];' + CHAR(10);
--db role menbership
SET @sqlstmt = @sqlstmt + CHAR(10)
+ 'INSERT INTO ##UnusedDatabaseRoles (DBName, RoleName)
SELECT DISTINCT ''' + @DBName + ''', RP.Name
FROM sys.database_principals RP
LEFT OUTER JOIN sys.database_role_members R
ON RP.principal_id = R.role_principal_id
WHERE RP.Type_Desc = ''DATABASE_ROLE'' AND R.role_principal_id IS NULL
AND RP.is_fixed_role = 0 AND RP.principal_id > 0
AND RP.Name NOT IN (''db_dtsadmin'',''db_dtsltduser'',''db_dtsoperator'')';
--PRINT @sqlstmt; --debug syntax
EXECUTE (@sqlstmt);
SET @LoopCounter = @LoopCounter + 1;
END;
[/code]
And yes, I did change from a local temp table to a global temp table on one of my things because I'm sending an email with the results after this.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply