troubleshooting Question

Dynamic SQL

Avatar of VitaminD
VitaminDFlag for United States of America asked on
Microsoft SQL Server
3 Comments1 Solution589 ViewsLast Modified:
I want to be able to run this sproc with any Database Name
The example on the bottom works when I run it ad hoc
I am not proficient with dynamic sql syntax
How would I write the dynamic SQL part so it works correctly
Any help will be greatly appreciated
Thanks In Advance


/****** Object:  StoredProcedure [dbo].[usp_enDBAReport]    Script Date: 07/03/2012 11:55:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




--exec [dbo].[usp_enDBAReport]NULL,NULL,0
CREATE       PROCEDURE [dbo].[usp_GetUserPermissions]
             (      
              @DatabaseName NVARCHAR(100)
          )

AS
BEGIN

      SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
      SET NOCOUNT ON


--IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects
--WHERE id = OBJECT_ID(N'[tempdb].[dbo].[SQL_DB_REP]'))
--DROP TABLE [tempdb].[dbo].[SQL_DB_REP]  
--GO

CREATE TABLE [tempdb].[dbo].[SQL_DB_REP]
(
[Server] [varchar](100) NOT NULL,
[DB_Name] [varchar](70) NOT NULL,
[User_Name] [nvarchar](90) NULL,
[Group_Name] [varchar](100) NULL,
[Account_Type] [varchar](22) NULL,
[Login_Name] [varchar](80) NULL,
[Def_DB] [varchar](100) NULL
)
ON [PRIMARY]
INSERT INTO [tempdb].[dbo].[SQL_DB_REP]
Exec sp_MSForEachDB 'SELECT CONVERT(varchar(100),
SERVERPROPERTY(''Servername'')) AS Server,
''' + @DatabaseName + ''' as DB_Name,
usu.name u_name
,CASE
WHEN (usg.uid is null) then ''public''
ELSE usg.name
END as Group_Name
,CASE
WHEN usu.isntuser=1 then ''Windows Domain Account''
WHEN usu.isntgroup = 1 then ''Windows Group''
WHEN usu.issqluser = 1 then ''SQL Account''
WHEN usu.issqlrole = 1 then ''SQL Role''
END as Account_Type
,lo.loginname
,lo.dbname as Def_DB
FROM
'''+ [@DatabaseName] + ''' ..sysusers usu LEFT OUTER JOIN
''' + ([@DatabaseName]+ ''' ..sysmembers mem INNER JOIN [db_DIDXDRone]..sysusers usg ON
mem.groupuid = usg.uid) ON usu.uid = mem.memberuid
LEFT OUTER JOIN master.dbo.syslogins lo on usu.sid =
lo.sid
WHERE
(usu.islogin = 1 and usu.isaliased = 0 and usu.hasdbaccess =
1) and
(usg.issqlrole = 1 or usg.uid is null)'
SELECT [Server],
[DB_Name],
[User_Name],
[Group_Name],
[Account_Type],
[Login_Name],
[Def_DB]
FROM [tempdb].[dbo].[SQL_DB_REP]


END



--------------------------------

--
When I run this Manually It works fine
But only for the Database I specify




IF EXISTS ( SELECT *
FROM tempdb.dbo.sysobjects
WHERE id =
OBJECT_ID(N'[tempdb].[dbo].[SQL_DB_REP]') )
DROP TABLE [tempdb].[dbo].[SQL_DB_REP] ; /*I intentionally left out the space */
GO
CREATE TABLE [tempdb].[dbo].[SQL_DB_REP]
(
[Server] [varchar](100) NOT NULL,
[DB_Name] [varchar](70) NOT NULL,
[User_Name] [nvarchar](90) NULL,
[Group_Name] [varchar](100) NULL,
[Account_Type] [varchar](22) NULL,
[Login_Name] [varchar](80) NULL,
[Def_DB] [varchar](100) NULL
)
ON [PRIMARY]
INSERT INTO [tempdb].[dbo].[SQL_DB_REP]
Exec sp_MSForEachDB 'SELECT CONVERT(varchar(100),
SERVERPROPERTY(''Servername'')) AS Server,
''MyDatabaseName'' as DB_Name,
usu.name u_name
,CASE
WHEN (usg.uid is null) then ''public''
ELSE usg.name
END as Group_Name
,CASE
WHEN usu.isntuser=1 then ''Windows Domain Account''
WHEN usu.isntgroup = 1 then ''Windows Group''
WHEN usu.issqluser = 1 then ''SQL Account''
WHEN usu.issqlrole = 1 then ''SQL Role''
END as Account_Type
,lo.loginname
,lo.dbname as Def_DB
FROM
[MyDatabaseName]..sysusers usu LEFT OUTER JOIN
([MyDatabaseName]..sysmembers mem INNER JOIN [?]..sysusers usg ON
mem.groupuid = usg.uid) ON usu.uid = mem.memberuid
LEFT OUTER JOIN master.dbo.syslogins lo on usu.sid =
lo.sid
WHERE
(usu.islogin = 1 and usu.isaliased = 0 and usu.hasdbaccess =
1) and
(usg.issqlrole = 1 or usg.uid is null)'
SELECT [Server],
[DB_Name],
[User_Name],
[Group_Name],
[Account_Type],
[Login_Name],
[Def_DB]
FROM [tempdb].[dbo].[SQL_DB_REP]
ASKER CERTIFIED SOLUTION
nishant joshi
Technology Development Consultant

Our community of experts have been thoroughly vetted for their expertise and industry experience.

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