Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- deny view definition to some_db_role
- grant view definition on dbo.some_proc to some_db_role
- grant view definition, references on symmetric key::some_key to some_db_role
- USE master;
- GO
- IF EXISTS (SELECT 1 FROM master.sys.databases d WHERE d.name = 'TestPerms_20160921')
- BEGIN
- DROP DATABASE TestPerms_20160921;
- END;
- GO
- CREATE DATABASE TestPerms_20160921;
- GO
- USE TestPerms_20160921;
- GO
- IF OBJECT_ID('dbo.Test') IS NOT NULL
- DROP TABLE dbo.Test;
- CREATE TABLE dbo.Test
- (
- TestID INT NOT NULL
- );
- GO
- IF OBJECT_ID('dbo.TestProc') IS NOT NULL
- DROP PROCEDURE dbo.TestProc;
- GO
- CREATE PROCEDURE dbo.TestProc
- AS
- BEGIN
- SELECT 1;
- END
- GO
- IF EXISTS (
- SELECT 1
- FROM sys.database_principals dp
- WHERE dp.name = 'TestU'
- )
- BEGIN
- DROP USER TestU;
- END
- CREATE USER TestU WITHOUT LOGIN;
- IF EXISTS (
- SELECT *
- FROM sys.database_principals dp
- WHERE dp.name = 'dbtestrole'
- )
- BEGIN
- DROP ROLE dbtestrole;
- END
- CREATE ROLE dbtestrole;
- ALTER ROLE dbtestrole ADD MEMBER TestU;
- GRANT SELECT ON dbo.Test TO dbtestrole;
- GRANT EXECUTE ON dbo.TestProc TO dbtestrole;
- EXECUTE AS USER = 'TestU';
- /* column names and types **are** visible */
- SELECT s.name
- , t.name
- , c.name
- , ty.name
- FROM sys.tables t
- INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
- INNER JOIN sys.columns c ON t.object_id = c.object_id
- INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
- WHERE s.name = 'dbo'
- AND t.name = 'Test'
- ORDER BY s.name
- , t.name
- , c.name;
- /* access to view the definition is denied */
- EXEC sys.sp_helptext @objname = 'dbo.TestProc', @columnname = NULL;
- /* we can run the proc! */
- EXEC dbo.TestProc;
- REVERT;
- DENY VIEW DEFINITION ON dbo.Test TO dbtestrole;
- EXECUTE AS USER = 'TestU';
- /* no rows returned as we can no longer view the table definition */
- SELECT s.name
- , t.name
- , c.name
- , ty.name
- FROM sys.tables t
- INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
- INNER JOIN sys.columns c ON t.object_id = c.object_id
- INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
- WHERE s.name = 'dbo'
- AND t.name = 'Test'
- ORDER BY s.name
- , t.name
- , c.name;
- /* we can still run the proc */
- EXEC dbo.TestProc;
- /* we can still see the content of the table */
- SELECT *
- FROM dbo.Test;
- REVERT;
- USE master;
- GO
- IF EXISTS (SELECT 1 FROM master.sys.databases d WHERE d.name = 'TestPerms_20160921')
- DROP DATABASE TestPerms_20160921;
Add Comment
Please, Sign In to add comment