Can someone please tell me what permissions need to be granted to a role (owned by dbo) in order to execute this SP (All the dbs are sa owned)
USE [master]
GO
/****** Object: StoredProcedure [dbo].[spDatabaseProperties] Script Date: 02/10/2011 11:05:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spDatabaseProperties] @SearchString VARCHAR(50),@SearchCriteria VARCHAR(50)='%',@UserName VARCHAR(50)='' AS
BEGIN
DECLARE @Name VARCHAR(100),
@dbid SMALLINT,
@crdate DATETIME,
@epName VARCHAR(100),
@epValue VARCHAR(100),
@SQLString NVARCHAR(1000),
@ParmDefinition NVARCHAR(500),
@Description VARCHAR(100),
@StudyDescription VARCHAR(150),
@Notes VARCHAR(1000)
CREATE TABLE #DBProperties
(
[name] NVARCHAR(128),
[dbid] SMALLINT,
[crdate] DATETIME,
[epName] NVARCHAR(100),
[epValue] NVARCHAR(100),
[Description] VARCHAR(100),
[StudyDescription] VARCHAR(150),
[Notes] VARCHAR(1000)
)
DECLARE c_Database CURSOR FOR
SELECT CONVERT(VARCHAR(100),SD.name),DD.StudyName,DD.StudyDescription,DD.Notes,SD.dbid,SD.crdate
FROM master.sys.sysdatabases SD INNER JOIN dbo.DatabaseDetails DD ON CONVERT(VARCHAR(100),SD.name)=DD.DatabaseName
WHERE name LIKE @SearchString + '%'
OPEN c_Database
FETCH NEXT FROM c_Database INTO
@Name,
@Description,
@StudyDescription,
@Notes,
@dbid,
@crDate
WHILE @@FETCH_STATUS=0
BEGIN
--GRANT SELECT, INSERT ON #tmpTable TO rolWebUser
CREATE TABLE #tmpTable
(
[name] NVARCHAR(100),
[value] NVARCHAR(100),
[Description] VARCHAR(100),
[StudyDescription] VARCHAR(150),
[Notes] VARCHAR(1000)
)
--Build the SQL string one time.
SET @SQLString = N'INSERT INTO #tmpTable SELECT CONVERT(VARCHAR(100),name), CONVERT(VARCHAR(100),value),' + CHAR(39) + @Description + CHAR(39) +',' + CHAR(39) + @StudyDescription + CHAR(39)+ ',' + CHAR(39) + COALESCE(@Notes,'') + CHAR(39)
SET @SQLString = @SQLString + ' FROM ' + @name + '.sys.fn_listextendedproperty (default, default, default, default, default, default, default)'
EXECUTE sp_executesql @SQLString
DECLARE c_Details CURSOR FOR
SELECT name,value,Description FROM #tmpTable ORDER BY name
OPEN c_Details
FETCH NEXT FROM c_Details INTO
@epName,
@epValue,
@Description
WHILE @@FETCH_STATUS=0
BEGIN
--Insert the returned values
INSERT INTO #DBProperties ([Name],[dbid],[crdate],[epName],[epValue],[Description]) VALUES (@Name,@dbid,@crDate,@epName,@epValue,@Description)
-- Get Data from First Record in Cursor
FETCH NEXT FROM c_Details INTO
@epName,
@epValue,
@Description
END
--Clear the objects and memory
DROP TABLE #tmpTable
CLOSE c_Details
DEALLOCATE c_Details
-- Get Data from First Record in Cursor
FETCH NEXT FROM c_Database INTO
@Name,
@Description,
@StudyDescription,
@Notes,
@dbid,
@crDate
END
CLOSE c_Database
DEALLOCATE c_Database
--Delete unwanted master databases.
DELETE FROM #DBProperties WHERE epValue LIKE '%Master'
--Filter the details for the user.
IF UPPER(@UserName)='ALL'
BEGIN
SELECT *
FROM #DBProperties
WHERE UPPER(epName) LIKE '%' + UPPER(@SearchCriteria) + '%'
END ELSE
BEGIN
SELECT *
FROM #DBProperties DP INNER JOIN
CVResearch_ASPNETDB.dbo.aspnet_UserDatabase UD ON DP.dbid=UD.DatabaseID
WHERE UPPER(UD.UserID) = UPPER(@UserName) AND UPPER(epName) LIKE '%' + UPPER(@SearchCriteria) + '%'
END
--Build the SQL string one time.
--SET @SQLString = N'SELECT * FROM #DBProperties DP INNER JOIN CVResearch_ASPNETDB.dbo.aspnet_UserDatabase UD ON DP.dbid=UD.DatabaseID WHERE UPPER(UD.UserID) LIKE ''' + UPPER(@UserName) + ''' AND DP.epName LIKE ''' + '%' + @SearchCriteria + '%' + ''''
--EXECUTE sp_executesql @SQLString
END
I am using a Windows Authenticated user login as a member of the role. I've tried adding the role to the schemas after explicitly granting the role exec permission to the sp with no joy. The proc executes fine when logged in as sysadmin...
USE [master]
GO
/****** Object: StoredProcedure [dbo].[spDatabaseProperties] Script Date: 02/10/2011 11:05:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spDatabaseProperties] @SearchString VARCHAR(50),@SearchCriteria VARCHAR(50)='%',@UserName VARCHAR(50)='' AS
BEGIN
DECLARE @Name VARCHAR(100),
@dbid SMALLINT,
@crdate DATETIME,
@epName VARCHAR(100),
@epValue VARCHAR(100),
@SQLString NVARCHAR(1000),
@ParmDefinition NVARCHAR(500),
@Description VARCHAR(100),
@StudyDescription VARCHAR(150),
@Notes VARCHAR(1000)
CREATE TABLE #DBProperties
(
[name] NVARCHAR(128),
[dbid] SMALLINT,
[crdate] DATETIME,
[epName] NVARCHAR(100),
[epValue] NVARCHAR(100),
[Description] VARCHAR(100),
[StudyDescription] VARCHAR(150),
[Notes] VARCHAR(1000)
)
DECLARE c_Database CURSOR FOR
SELECT CONVERT(VARCHAR(100),SD.name),DD.StudyName,DD.StudyDescription,DD.Notes,SD.dbid,SD.crdate
FROM master.sys.sysdatabases SD INNER JOIN dbo.DatabaseDetails DD ON CONVERT(VARCHAR(100),SD.name)=DD.DatabaseName
WHERE name LIKE @SearchString + '%'
OPEN c_Database
FETCH NEXT FROM c_Database INTO
@Name,
@Description,
@StudyDescription,
@Notes,
@dbid,
@crDate
WHILE @@FETCH_STATUS=0
BEGIN
--GRANT SELECT, INSERT ON #tmpTable TO rolWebUser
CREATE TABLE #tmpTable
(
[name] NVARCHAR(100),
[value] NVARCHAR(100),
[Description] VARCHAR(100),
[StudyDescription] VARCHAR(150),
[Notes] VARCHAR(1000)
)
--Build the SQL string one time.
SET @SQLString = N'INSERT INTO #tmpTable SELECT CONVERT(VARCHAR(100),name), CONVERT(VARCHAR(100),value),' + CHAR(39) + @Description + CHAR(39) +',' + CHAR(39) + @StudyDescription + CHAR(39)+ ',' + CHAR(39) + COALESCE(@Notes,'') + CHAR(39)
SET @SQLString = @SQLString + ' FROM ' + @name + '.sys.fn_listextendedproperty (default, default, default, default, default, default, default)'
EXECUTE sp_executesql @SQLString
DECLARE c_Details CURSOR FOR
SELECT name,value,Description FROM #tmpTable ORDER BY name
OPEN c_Details
FETCH NEXT FROM c_Details INTO
@epName,
@epValue,
@Description
WHILE @@FETCH_STATUS=0
BEGIN
--Insert the returned values
INSERT INTO #DBProperties ([Name],[dbid],[crdate],[epName],[epValue],[Description]) VALUES (@Name,@dbid,@crDate,@epName,@epValue,@Description)
-- Get Data from First Record in Cursor
FETCH NEXT FROM c_Details INTO
@epName,
@epValue,
@Description
END
--Clear the objects and memory
DROP TABLE #tmpTable
CLOSE c_Details
DEALLOCATE c_Details
-- Get Data from First Record in Cursor
FETCH NEXT FROM c_Database INTO
@Name,
@Description,
@StudyDescription,
@Notes,
@dbid,
@crDate
END
CLOSE c_Database
DEALLOCATE c_Database
--Delete unwanted master databases.
DELETE FROM #DBProperties WHERE epValue LIKE '%Master'
--Filter the details for the user.
IF UPPER(@UserName)='ALL'
BEGIN
SELECT *
FROM #DBProperties
WHERE UPPER(epName) LIKE '%' + UPPER(@SearchCriteria) + '%'
END ELSE
BEGIN
SELECT *
FROM #DBProperties DP INNER JOIN
CVResearch_ASPNETDB.dbo.aspnet_UserDatabase UD ON DP.dbid=UD.DatabaseID
WHERE UPPER(UD.UserID) = UPPER(@UserName) AND UPPER(epName) LIKE '%' + UPPER(@SearchCriteria) + '%'
END
--Build the SQL string one time.
--SET @SQLString = N'SELECT * FROM #DBProperties DP INNER JOIN CVResearch_ASPNETDB.dbo.aspnet_UserDatabase UD ON DP.dbid=UD.DatabaseID WHERE UPPER(UD.UserID) LIKE ''' + UPPER(@UserName) + ''' AND DP.epName LIKE ''' + '%' + @SearchCriteria + '%' + ''''
--EXECUTE sp_executesql @SQLString
END
I am using a Windows Authenticated user login as a member of the role. I've tried adding the role to the schemas after explicitly granting the role exec permission to the sp with no joy. The proc executes fine when logged in as sysadmin...