min permissions for cross database calls on procs marked with sp_ms_marksystemobject

• Lowell

  SSC Guru

  Points: 323518

  More actions

  May 15, 2014 at 6:05 am

  #291338

  I'm trying to set up permissions for a read only, reporting type user: view definition plus db_datareader is the desired result, plus the ability to execute procs that exist in master which access the relative metadata in the current database context.

  That all works perfectly for my login, being a sysadmin and all, but i get errors for more limited users, and i cannot seem to see where my logic error is.

  I've got a suite of procs in the master database which are super useful, and i'd like to give them access to them....

  here's very simple , full example that demonstrates the whole thing; in the end, my user returns an error , whether i test with EXECUTE AS USER or EXECUTE AS LOGIN

  This behavior occurs regardless of the setting for allow cross database ownership chaining, which is what i thought might be the problem.

  --as user

  Msg 229, Level 14, State 5, Procedure sp_count, Line 1

  The EXECUTE permission was denied on the object 'sp_count', database 'master', schema 'dbo'.

  --as login

  Msg 297, Level 16, State 1, Procedure sp_count, Line 8

  The user does not have permission to perform this action.

  USE master;

  GO

  --our test procedure

  IF OBJECT_ID('[dbo].[sp_count]') IS NOT NULL

  DROP PROCEDURE [dbo].[sp_count] ;

  GO

  --#################################################################################################

  --developer utility function added by Lowell, used in SQL Server Management Studio

  --Purpose: count rows on all tables

  --exec sp_count

  --#################################################################################################

  CREATE PROCEDURE sp_count

  AS

  SELECT o.name AS ObjectName,

  ps.row_count AS TheCount

  FROM sys.indexes AS i

  INNER JOIN sys.objects AS o

  ON i.OBJECT_ID = o.OBJECT_ID

  INNER JOIN sys.dm_db_partition_stats AS ps

  ON i.OBJECT_ID = ps.OBJECT_ID

  AND i.index_id = ps.index_id

  WHERE i.index_id < 2

  AND o.is_ms_shipped = 0

  ORDER BY o.name;

  GO

  --#################################################################################################

  --Mark as a system object

  EXECUTE sp_ms_marksystemobject 'sp_count';

  --#################################################################################################

  --test login

  CREATE LOGIN ClarkKent WITH password = 'NotTheRealPassword!',DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english];

  --create our standard Role with permissions

  CREATE ROLE StandardPermissions;

  EXEC sp_addrolemember 'db_datareader','StandardPermissions';

  GRANT VIEW DEFINITION ON SCHEMA::[dbo] TO [StandardPermissions];

  GRANT EXECUTE ON sp_count TO [StandardPermissions];

  --test user

  CREATE USER [ClarkKent] FOR LOGIN [ClarkKent];

  --permissions for the user

  EXEC sp_addrolemember 'StandardPermissions','ClarkKent';

  GO

  USE SandBox;

  GO

  --now in SandBox db, create our standard Role with permissions

  CREATE ROLE StandardPermissions;

  EXEC sp_addrolemember 'db_datareader','StandardPermissions';

  GRANT VIEW DEFINITION ON SCHEMA::[dbo] TO [StandardPermissions];

  --test user

  CREATE USER [ClarkKent] FOR LOGIN [ClarkKent];

  --permissions for the user

  EXEC sp_addrolemember 'StandardPermissions','ClarkKent';

  EXECUTE AS USER='ClarkKent';

  SELECT SUSER_NAME(); -- I am ClarkKent

  select IS_ROLEMEMBER('StandardPermissions')

  EXEC sp_count;

  REVERT;

  EXECUTE AS LOGIN='ClarkKent';

  SELECT SUSER_NAME(); -- I am ClarkKent

  select IS_ROLEMEMBER('StandardPermissions')

  EXEC sp_count;

  REVERT;

  /*

  Cleanup

  USE SandBox;

  GO

  DROP USER ClarkKent

  DROP Role StandardPermissions

  GO

  USE Master

  GO

  DROP USER ClarkKent

  DROP Role StandardPermissions

  DROP LOGIN ClarkKent

  */

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• ScottPletcher

  SSC Guru

  Points: 101098

  More actions

  May 15, 2014 at 10:40 am

  #1713938

  Under "sys.dm_db_partition_stats" in Books Online:

  "

  Requires VIEW DATABASE STATE permission to query

  "

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".