SQL Server Login permission to read queue message count of Service Broker

SQL Server Login permission to read queue message count of Service Broker

Few weeks back, I have written one article “Find Service Broker Queue Count in SQL Server”. One of the blog reader has created the UDF I have given in that article and tried to fetch value of that UDF from his web application to find the service broker queue count but, unfortunately, It was returning 0 value in web application but if he tries to execute UDF function in SSMS itself, it used to return proper queue count.

He wrote me back to find the solution of the problem he was facing. I asked him the login he is using in SSMS as well as from application and as soon as I get this answer from him, I get to know the reason exact issue he was facing.

He was using Windows Authentication “Administrator” login while using SSMS and was using WebAppEnt SQL Server login from web application. I guessed that Administrator login would by sysAdmin and WebAppEnt login would have only required permission which my reader confirmed. I told him to give proper permission to WebAppEnt login with following script.

USE Master GO 
GRANT VIEW ANY DEFINITION TO WebAppEnt; 
GO 

As soon as He ran above given script, everything started working fine. :)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah http://Extreme-Advice.com http://www.sqlhub.com Note: Microsoft Books online is a default reference of all articles.

GRANT VIEW DEFINITION permission to all Stored Procedures in SQL Server

GRANT VIEW DEFINITION permission to all Stored Procedures in SQL Server

PermissionI have restricted all user from viewing script/text of stored procedure, views except DBA this is the part of our security policy for live environment. Generally DBA account should have permission to see the code of SP, View, Trigger etc. in live environment but I have requirement to let one person see script of all SPs. I have simply execute “GRANT VIEW DEFINITION” command if I had to let him view only few SPs but there are 100s of SPs in our database and I have to give him permission for all stored procedures. It is hectic to give permission for each SP manually and that is why I have quickly created one small TSQL script which give permission for “VIEW DEFINITION” on all stored procedures to given user.

TSQL script I am providing here is basically for “VIEW DEFINITION” permission for all “Stored Procedure” but you can twist this TSQL code quickly for your need to give any different permission to any other objects.

Let us first create One sample login and user for AdventureWorks2012 database. If you don’t have AdventureWorks2012 database, you can have it in your test or beta database.

[sourcecode language=”sql”]
USE [master]
GO

CREATE LOGIN [TestUser] WITH PASSWORD=N’testing’,
DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE [AdventureWorks2012]
GO

CREATE USER [TestUser] FOR LOGIN [TestUser]
GO
[/sourcecode]

Now, Here is the TSQL script which will give permission to the “TestUser” user in Adventureworks2012 database to view all stored procedures.

[sourcecode language=”sql”]
DECLARE @tblSPList TABLE (
SPID INT IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,
SPName SYSNAME
)

DECLARE @SPName SYSNAME,
@RowCounter INT,
@RecordCounter INT,
@UserName VARCHAR(100),
@ExecuteSQL VARCHAR(1000)

SET @UserName=’TestUser’

INSERT INTO @tblSPList (SPName)
SELECT ‘[‘+ROUTINE_SCHEMA+’].[‘+ROUTINE_NAME+’]’ FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = ‘PROCEDURE’

SET @RecordCounter = (SELECT count(*) FROM @tblSPList)
SET @RowCounter = 1

WHILE (@RowCounter < @RecordCounter + 1)
BEGIN
SELECT @SPName = SPName
FROM @tblSPList
WHERE SPID = @RowCounter
SET @ExecuteSQL = N’Grant VIEW Definition on ‘ + rtrim(cast(@SPName AS VARCHAR(128))) + ‘ to [‘ + @UserName +’]’

–commenting following EXEC statement so that
–one can verify before execute
–EXEC(@ExecuteSQL)

–Print Execute Statement
PRINT @ExecuteSQL

SET @RowCounter += 1
END
GO
[/sourcecode]

You can modify this script to given different permission for different object.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.