Understand Permission Function

fn_my_permissions

fn_my_permissions ( securable , 'securable_class')

Arguments

securable

Is the name of the securable. If the securable is the server or a database, this value should be set to NULL. securable is a scalar expression of type sysname. securablecan be a multipart name.

' securable_class '

Is the name of the class of securable for which permissions are listed. securable_class is a sysname. securable_class must be one of the following: APPLICATION ROLE, ASSEMBLY, ASYMMETRIC KEY, CERTIFICATE, CONTRACT, DATABASE, ENDPOINT, FULLTEXT CATALOG, LOGIN, MESSAGE TYPE, OBJECT, REMOTE SERVICE BINDING, ROLE, ROUTE, SCHEMA, SERVER, SERVICE, SYMMETRIC KEY, TYPE, USER, XML SCHEMA COLLECTION.

Remarks

---

This table-valued function returns a list of the effective permissions held by the calling principal on a specified securable. An effective permission is any one of the following:

· A permission granted directly to the principal, and not denied.

· A permission implied by a higher-level permission held by the principal and not denied.

· A permission granted to a role or group of which the principal is a member, and not denied.

· A permission held by a role or group of which the principal is a member, and not denied.

The permission evaluation is always performed in the security context of the caller. To determine whether some other principal has an effective permission, the caller must have IMPERSONATE permission on that principal.

For schema-level entities, one-, two-, or three-part nonnull names are accepted. For database-level entities, a one-part name is accepted, with a null value meaning "current database". For the server itself, a null value (meaning "current server") is required. fn_my_permissions cannot check permissions on a linked server.

The following query will return a list of built-in securable classes:

SELECT DISTINCT class_desc FROM fn_builtin_permissions(default)

ORDER BY class_desc;

GO

If DEFAULT is supplied as the value of securable or securable_class, the value will be interpreted as NULL.

Columns Returned

---

The following table lists the columns that fn_my_permissions returns. Each row that is returned describes a permission held by the current security context on the securable. Returns NULL if the query fails.

Column name	Type	Description
entity_name	sysname	Name of the securable on which the listed permissions are effectively granted.
subentity_name	sysname	Column name if the securable has columns, otherwise NULL.
permission_name	nvarchar	Name of the permission.

Examples

A. Listing effective permissions on the server

The following example returns a list of the effective permissions of the caller on the server.

SELECT * FROM fn_my_permissions(NULL, 'SERVER');

GO

B. Listing effective permissions on the database

The following example returns a list of the effective permissions of the caller on the AdventureWorks database.

USE AdventureWorks;

SELECT * FROM fn_my_permissions (NULL, 'DATABASE');

GO

C. Listing effective permissions on a view

The following example returns a list of the effective permissions of the caller on the vIndividualCustomer view in the Sales schema of the AdventureWorks database.

USE AdventureWorks;

SELECT * FROM fn_my_permissions('Sales.vIndividualCustomer', 'OBJECT')

ORDER BY subentity_name, permission_name ;

GO

D. Listing effective permissions of another user

The following example returns a list of the effective permissions of database user Wanida on the Employee table in the HumanResources schema of the AdventureWorksdatabase. The caller requires IMPERSONATE permission on user Wanida.

EXECUTE AS USER = 'Wanida';

SELECT * FROM fn_my_permissions('HumanResources.Employee', 'OBJECT')

ORDER BY subentity_name, permission_name ;

REVERT;

GO

E. Listing effective permissions on a certificate

The following example returns a list of the effective permissions of the caller on a certificate named Shipping47 in the current database.

SELECT * FROM fn_my_permissions('Shipping47', 'CERTIFICATE');

GO

F. Listing effective permissions on an XML Schema Collection

The following example returns a list of the effective permissions of the caller on an XML Schema Collection named ProductDescriptionSchemaCollection in theAdventureWorks database.

USE AdventureWorks;

SELECT * FROM fn_my_permissions('ProductDescriptionSchemaCollection',

'XML SCHEMA COLLECTION');

GO

G. Listing effective permissions on a database user

The following example returns a list of the effective permissions of the caller on a user named MalikAr in the current database.

SELECT * FROM fn_my_permissions('MalikAr', 'USER');

GO

H. Listing effective permissions of another login

The following example returns a list of the effective permissions of SQL Server login WanidaBenshoof on the Employee table in the HumanResources schema of theAdventureWorks database. The caller requires IMPERSONATE permission on SQL Server login WanidaBenshoof.

EXECUTE AS LOGIN = 'WanidaBenshoof';

SELECT * FROM fn_my_permissions('AdventureWorks.HumanResources.Employee', 'OBJECT')

ORDER BY subentity_name, permission_name ;

REVERT;

GO

Read more »

The "Balanced Data Distributor" for SSIS

There is a new transform component available for SQL Server Integration Services.  It's called the Balanced Data Distributor (BDD) and the download is available here.  The BDD provides an easy way to amp up your usage of multi-processor and multi-core servers by introducing parallelism in the data flow of an SSIS package.

Functionality of the BDD

The functionality of the BDD is very simple:  It takes its input data and routes it in equal proportions to its outputs, however many there are.  If you have four outputs, roughly ¼ of the input rows will go to each output.  Instead of routing individual rows, the BDD operates on buffers of data, so it's very efficient.

Some of you will already be noticing that there is no transformational value in the BDD, and no control over which data rows go to which output.  You may be wondering, what the heck is the value of that?

The value of the BDD comes from the way modern servers work:  Parallelism.  When there are independent segments of an SSIS data flow, SSIS can distribute the work over multiple threads.  BDD provides an easy way to create independent segments.

This diagram gives a trivial example:

If you run this data flow on a laptop, there probably won't be any speed advantage, and there may even be a speed cost.  But suppose you run this on a server with multiple cores and many disk spindles supporting the destination database.  Then there might be a substantial speed advantage to using this data flow.

When to use the BDD

Using the BDD requires an understanding of the hardware you will be running on, the performance of your data flow and the nature of the data involved.   Therefore it won't be for everyone, but for those who are willing to think through these things there can be significant benefits.  Here is my summary description of when to use BDD:

1. There is a large amount of data coming in.
   
2. The data can be read faster than the rest of the data flow can process it, either because there is significant transformation work to do or because the destination is the bottleneck.  If the destination is the bottleneck, it must be parallelizable.
   
3. There is no ordering dependency in the data rows.  For example if the data needs to stay sorted, don't go and split it up using BDD.
   
   
   

Relieving bottlenecks in the SSIS Data Flow

Let's talk about bottlenecks, since changing bottlenecks is what BDD is all about.  A bottleneck is whatever limits the performance of the system.  In general there are three places that could be the bottleneck in an SSIS data flow:  The source, the transformations, or the destination.

Bottlenecks in the Source

If the limiting factor is the rate at which data can be read from the source, then the BDD is not going to help.  It would be better to look for ways to parallelize right from the source.

Bottlenecks in the Transformations

If the limiting factor is the transformation work being done in the data flow, BDD can help.  Imagine that there are some lookups, derived columns, fuzzy lookups and so on:  These could easily be the components limiting performance.  Make two or four or eight copies of the transformations, and split the data over them using the BDD.  Let the processing run in parallel.  If there are several transformations in the data flow, put as much as you can after the BDD, to get more things running in parallel.

Bottlenecks in the Destination

If the limiting factor is the destination, BDD might be able to help - you need to determine whether the destination can be run in parallel.  You might be surprised at some times when it can.  One example is when loading data into a simple heap (table with no indexes) in SQL Server.  With the database properly distributed over a number of disks, it is quite possible to load in parallel with good performance.  When working on the ETL World Record a while ago, we used a heap for a side experiment and found that loading 56 streams concurrently into a single heap was almost as fast as loading 56 streams into 56 independent tables.  Many sites already drop or disable their indexes during data loading, so this could be more of a freebie than you would expect.  More recently we saw a benefit from parallel loading into SQL Server Parallel Data Warehouse (PDW).  PDW is an architecture designed for parallelism!

When the destination does not support parallel loading

A final case to consider is when the limiting factor is the transformation work being done in the data flow but the destination cannot receive data in parallel for some reason.  In this case, consider using BDD to parallelize the transforms followed by a Union All to rejoin the data into a single flow; then a single destination can be used.  Here is an illustration:

Best practice – balanced!

One final note:  Whatever you put behind the BDD, be sure the same work is being done on all paths.  It doesn't make logical sense to have the paths be different, and from a performance point of view, you want them all to be the same speed.  Remember, the "B" in BDD stands for "Balanced".

Conclusion

Someday maybe SSIS will be able to do the work of the BDD automatically, but for now you have an easy way to amp up your usage of multi-processor and multi-core servers by introducing parallelism in the data flow of an SSIS package.

Read more »