EXEC permissions, roles and the system master

[misterjib]

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...

 

[mrdenny]

The user the executes the code against sys.databases will need the VIEW SERVER STATE right. You don't want to give this to all users. You'll want to use impersonation to impersonate a different login which has these rights. Look up EXECUTE AS in the SQL Server Books OnLine (the help file).

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)
MCM (SQL 2008)
MVP

My Blog

 

[misterjib]

Thanks Denny for that nudge in the right direction - it's not quite working yet but found this:

http://www.sommarskog.se/grantperm.html#EXECUTE_AS

which i think is going to prove useful reading.

JIB