VitaminD
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'',''I F'',''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
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'',
)
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
)
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
An awfull lot of chances to fail. Why the dynamic code?
Isn't it easier to fill the @users into a tablevariable?
Isn't it easier to fill the @users into a tablevariable?
Which version of SQL? If 2005+ then go straight to nvarchar( max )
HTH
David