Avatar of VitaminD
VitaminD
Flag for United States of America asked on

Dynamic SQL Statement not working properly

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
Microsoft DevelopmentMicrosoft SQL Server

Avatar of undefined
Last Comment
jogos

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Qlemo

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
David Todd

Hi,

Which version of SQL? If 2005+ then go straight to nvarchar( max )

HTH
  David
jogos

An awfull lot of chances to fail. Why the dynamic code?

Isn't it easier to fill the @users into a tablevariable?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23