troubleshooting Question

Dynamic SQL Statement not working properly

Avatar of VitaminD
VitaminDFlag for United States of America asked on
Microsoft SQL ServerMicrosoft Development
3 Comments1 Solution380 ViewsLast Modified:
This is the original code which is working correctly
when I run it adhoc it works okay

I need to run it dynamically so I can change Database contexts



Dynamic Code not working Correctly




DECLARE @SQL NVARCHAR(2000),
        @DBName VARCHAR(100),
        @dbuse NVARCHAR(50),
        @SQLFinal NVARCHAR(4000)
DECLARE @Users VARCHAR(100)
DECLARE @AllUsers varchar(100)
SET @AllUsers = 1
SET @Users = ''
SET @DBName = 'AW'
SET @dbuse ='USE ' + @DBName;


SET @SQL =  ';with AllRoles      -- Recursively find all Roles the Users are members of
as      (
select principal_id, name, sid, cast(Null as sysname) MemberName, name BaseName
      from sys.database_principals
      where type in (''S'', ''U'', ''G'')
      or name = ''public''      -- Public is special since it is not in sys.database_role_members

      union all

      select r.principal_id, r.name, r.sid, rm.Name, ar.BaseName
      from
            AllRoles ar
      inner join
            sys.database_role_members drm
                  on      drm.member_principal_id = ar.principal_id
      inner join
            sys.database_principals r
                  on      r.principal_id = drm.role_principal_id
      inner join
            sys.database_principals rm
                  on      rm.principal_id = drm.member_principal_id
)
select a.*
from (
      -------------------------------------------
      --- Object Level Rights: Explicit Grants
      ---      (Based on User and Role Memberships)
      -------------------------------------------

      SELECT
            coalesce(so.name, '''') AS ''[Object Name(Permissions)]'',

            sp.permission_name,
            state_desc,

            u.Name Grantee,
            ar.BaseName
      FROM
            sys.database_permissions sp                              -- Rights Granted
      inner join
            sys.database_principals u                              -- Grantee
                  on      sp.grantee_principal_id = u.principal_id

      left outer join
            sys.objects so                                                -- Object
                  on      so.object_id = sp.major_id

      inner join
            AllRoles ar
                  on      u.sid = ar.sid

      WHERE
            (
            so.name is Null
      or
            LEFT(so.name,3) NOT IN (''sp_'', ''fn_'', ''dt_'', ''dtp'', ''sys'')
            --AND
            --so.type IN (''U'',''V'',''TR'',''P'',''FN'',''IF'',''TF'')
            )
      and      not (
                  sp.class_desc = ''DATABASE''
            and sp.permission_name = ''CONNECT''
            )
      and      sp.major_id >= 0                              -- Negative => System Object
      and (
            ar.BaseName = ''public''
            or
            ' + @AllUsers + '  = 1
            or
            ar.BaseName in (
                   ''' + @Users + '''
                  )
            )

      union all

      -------------------------------------------
      --- Fixed Database Role Membership
      -------------------------------------------

      select
            ar.Name,
            '''',
            '''',
            ar.MemberName,
            ar.BaseName
      from
            AllRoles ar
      inner join
            sys.database_principals r
                  on      r.principal_id = ar.principal_id
                  and      r.is_fixed_role = 1
      where
      ' + @AllUsers + '  = 1
            or
            ar.BaseName in (
                  
                   ''' + @Users + '''
                  )

      union all

      -------------------------------------------
      --- Fixed Server Role Membership
      --- (Assumes: Login Name == User Name)
      -------------------------------------------

      select
            sr.Name,
            '''',
            '''',
            ''Server Role'',
            l.Name
      from sys.server_principals l
      inner join
            sys.server_role_members r
      on
            r.member_principal_id = l.principal_id
      inner join
            sys.server_principals sr
      on
            sr.principal_id = r.role_principal_id
      and      sr.type = ''R''
      where
      ' + @AllUsers + ' = 1
            or
            l.name in (
                        
                         ''' + @Users + '''
                        )
      ) a
order by
      BaseName,
      case
            when permission_name = '''' then 1 else 2 end,
      ''[Object Name(Permissions)]'',
      permission_name,
      state_desc,
      Grantee'
      
      
      
SET @SQLFINAL = @dbuse + '; ' + @SQL

EXEC (@SQLFinal)
PRINT        @SQLFinal
      
      ----------------------------------------------------------------


AdHoc Code which runs



Declare @Users varchar(100)
declare @AllUsers varchar(100)
set @AllUsers = 0
set @Users = 'temp\James'
 ;with AllRoles      -- Recursively find all Roles the Users are members of
as      (
select principal_id, name, sid, cast(Null as sysname) MemberName, name BaseName
      from sys.database_principals
      where type in ('S', 'U', 'G')
      or name = 'public'      -- Public is special since it is not in sys.database_role_members

      union all

      select r.principal_id, r.name, r.sid, rm.Name, ar.BaseName
      from
            AllRoles ar
      inner join
            sys.database_role_members drm
                  on      drm.member_principal_id = ar.principal_id
      inner join
            sys.database_principals r
                  on      r.principal_id = drm.role_principal_id
      inner join
            sys.database_principals rm
                  on      rm.principal_id = drm.member_principal_id
)
select a.*
from (
      -------------------------------------------
      --- Object Level Rights: Explicit Grants
      ---      (Based on User and Role Memberships)
      -------------------------------------------

      SELECT
            coalesce(so.name, '') AS '[Object Name(Permissions)]',

            sp.permission_name,
            state_desc,

            u.Name Grantee,
            ar.BaseName
      FROM
            sys.database_permissions sp                              -- Rights Granted
      inner join
            sys.database_principals u                              -- Grantee
                  on      sp.grantee_principal_id = u.principal_id

      left outer join
            sys.objects so                                                -- Object
                  on      so.object_id = sp.major_id

      inner join
            AllRoles ar
                  on      u.sid = ar.sid

      WHERE
            (
            so.name is Null
      or
            LEFT(so.name,3) NOT IN ('sp_', 'fn_', 'dt_', 'dtp', 'sys')
            --AND
            --so.type IN ('U','V','TR','P','FN','IF','TF')
            )
      and      not (
                  sp.class_desc = 'DATABASE'
            and sp.permission_name = 'CONNECT'
            )
      and      sp.major_id >= 0                              -- Negative => System Object
      and (
            ar.BaseName = 'public'
            or
              @AllUsers   = 1
            or
            ar.BaseName in (
                   @Users
                  )
            )

      union all

      -------------------------------------------
      --- Fixed Database Role Membership
      -------------------------------------------

      select
            ar.Name,
            '',
            '',
            ar.MemberName,
            ar.BaseName
      from
            AllRoles ar
      inner join
            sys.database_principals r
                  on      r.principal_id = ar.principal_id
                  and      r.is_fixed_role = 1
      where
            @AllUsers = 1
            or
            ar.BaseName in (
                  
                  @Users
                  )

      union all

      -------------------------------------------
      --- Fixed Server Role Membership
      --- (Assumes: Login Name == User Name)
      -------------------------------------------

      select
            sr.Name,
            '',
            '',
            'Server Role',
            l.Name
      from sys.server_principals l
      inner join
            sys.server_role_members r
      on
            r.member_principal_id = l.principal_id
      inner join
            sys.server_principals sr
      on
            sr.principal_id = r.role_principal_id
      and      sr.type = 'R'
      where
            @AllUsers = 1
            or
            l.name in (
                        
                        @Users
                        )
      ) a
order by
      BaseName,
      case
            when permission_name = '' then 1 else 2 end,
      '[Object Name(Permissions)]',
      permission_name,
      state_desc,
      Grantee
ASKER CERTIFIED SOLUTION
Qlemo
"Batchelor", Developer and EE Topic Advisor
Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros