The basic concept is to use GRANT/DENY Schema Permissions. You can efficiently manage permissions by creating a role and then adding members to it.
Below is an example that will explain you in detail
use master
go
--Create Logins
CREATE LOGIN UserA WITH Password='UserA123';
go
CREATE LOGIN UserB WITH Password='UserB123';
use AdventureWorks2008R2
go
--Create Database Users
CREATE USER UserA;
go
CREATE USER UserB;
go
--Create the Test Schemas
CREATE SCHEMA SchemaA AUTHORIZATION UserA
go
CREATE SCHEMA SchemaB AUTHORIZATION UserB
go
-- create test tables
create table schemaA.TableA (fname char(5))
go
insert into schemaA.TableA (fname) values ('Kin-A')
go
create table SchemaB.TableB (fname char(5))
go
insert into SchemaB.TableB (fname) values ('Kin-B')
go
Now test :
--Test for UserA in SchemaA
EXEC('select * from schemaA.TableA') AS USER = 'UserA'
go
--Kin-A
-- Test for UserB in SchemaB == this should fail
EXEC('select * from SchemaB.TableB') AS USER = 'UserA'
go
--Msg 229, Level 14, State 5, Line 1
--The SELECT permission was denied on the object 'TableB', database 'AdventureWorks2008R2', schema 'SchemaB'.
Now create Stored Procedures :
CREATE PROCEDURE SchemaB.proc_SelectUserB
AS
select * from schemaA.TableA;
go
create procedure schemaA.proc_SchemaA
as
select * from schemaA.TableA
Now Grant execute permissions to UserA on schemaB's SP
GRANT EXECUTE ON OBJECT::[SchemaB].[proc_SelectUserB] TO [UserA]
go
Test it .. to see if UserA is able to run SP from schemaB. This will PASS
EXECUTE AS LOGIN='UserA';
Exec SchemaB.proc_SelectUserB;
revert;
go
--- Kin-A
But UserA wont be able to see data from SchemaB
EXECUTE AS LOGIN='UserA';
select * from SchemaB.TableB
revert;
go
--- Msg 229, Level 14, State 5, Line 3
--- The SELECT permission was denied on the object 'TableB', database 'AdventureWorks2008R2', schema 'SchemaB'.
Alternatively you can use DATABASE ROLE and just add users to it for better manageability of permissions:
EXEC sp_addrole 'SchemaBUsesSchemaAProc'
go
EXEC sp_addrolemember 'SchemaBUsesSchemaAProc','UserA';
go
Below statement will make sure that UserA is able to see schemaA and NOT schemaB. The good thing is that you can just add users to SchemaBUsesSchemaAProc
role and they will inherit all the permissions granted to that role.
GRANT SELECT ON SCHEMA::SchemaA TO SchemaBUsesSchemaAProc;
go
If you only want to allow UserA to execute SP's which are owned by SchemaB then below statement will do the job:
GRANT EXECUTE ON OBJECT::[SchemaB].[proc_SelectUserB] TO [SchemaBUsesSchemaAProc]
go
This way, UserA is not able to see SchemaB's tables, but still can execute procs from SchemaB.
Below will explain the permission hierarchy :
There are quite a few problems with this code:
Consecutive calls of select ... into result
do no accumulate rows into result
, each one overwrites whatever was previously in result
.
A variable typed after a table (result
of type result_load_status
here) is meant to contain only one row of this type, not an entire resultset. For an entire resultset, use CREATE TEMPORARY TABLE
or a cursor if possible.
select * from name_of_a_variable
does not exist as a valid construct. A temporary table instead of a variable's name would work.
Anyway since accumulating results is already built in RETURN QUERY
, you don't need any of the above.
Quoting from the documentation:
RETURN QUERY appends the results of executing a query to the
function's result set
So you may just write:
RETURN QUERY execute 'select ' || table_name ||'.up_link,' || table_name || '.time_stamp from' || table_name || ' where bar2= ' || b;
RETURN QUERY execute 'select ' || table_name ||'.up_link,' || table_name || '.time_stamp from' || table_name || ' where bar2= ' || c;
without any need for the problematic result
variable.
Best Answer
I'm not sure I understood your question but I'll try to explain the differences.
When you have full permissions in
msdb
you'll get the same rows whenmsdb.dbo.sp_help_schedule
msdb.dbo.sysschedules_localserver_view
msdb.dbo.sysschedules
This is because
msdb.dbo.sp_help_schedule
selects frommsdb.dbo.sysschedules_localserver_view
which selects frommsdb.dbo.sysschedules
.You'll get less rows when select from
msdb.dbo.sysjobschedules
mentioned in your question, this is becausemsdb.dbo.sysjobschedules
While
msdb.dbo.sysschedules
shows also schedules currently not related to any existing job, for example is contains schedules for Data Collector that may not be configured.When the user has only
select
permission inmsdb
he will not be able to execute
msdb.dbo.sp_help_schedule
, he'll get the full result when selecting from tablesmsdb.dbo.sysschedules
andmsdb.dbo.sysjobschedules
, but he'll get no rows when selecting from themsdb.dbo.sysschedules_localserver_view
view.This is because this view contains the
where
clause:This means that to get any row from this view one of the following should be true for the current user:
owner
of the job (and only the schedules related to owned jobs will be returned)sysadmin
fixed server role (all rows will be returned)SQLAgentReaderRole
database role (all rows will be returned)