Security. Oh that most painful of topics. I discussed it a few months earlier when I discussed the need to give rights only through roles to users, so everything is the same in dev and prod except the users who are placed in each role (SQL Server Database Security And Source Control). As I was updating some of our security to use this method, the problem arose that it is hard to know if we got it right. How to make sure that userX who had access to a certain set of objects, still does, even though we have given them access via a different method. This is something that we often wish to know. “Can user X access resource Y?”

So, I went about to build a small utility, most of which is part of this blog. SQL Server gives up tools to determine if a principal has access to an object, so I will introduce them, and then use them to check the “normal” security case, which is trying to determine a user’s rights to access to objects in the database. In a later blog, I will attempt to expand the security beyond object access to all of the various types of security a user may have that we need to track.

SQL Server provides us with two functions that can be used effectively to discover the rights that a user has to an object. It doesn’t tell us HOW the achieved this right, but it tells us if they do have it (how is a bit more complex, and something I plan to tackle some other day!) For example, say a user has SELECT access to TableY. This user could have it directly granted, inherited from a grant at the schema level, inherited through a user-defined role’s access, or even from a built-in role (database or server!)

This is fairly complex, but the general need I am aiming to solve today is to determine what a user has access to. The functions (available in the on-premises and Azure versions) allow you to determine what the current security principal has access to. They are:

• HAS_PERMS_BY_NAME – This is a scalar function that you can pass in various things like tables and columns, database and server rights.
• fn_my_permissions – This is a table valued function that you can use to return the rights you have for a certain object

The parameters for both include the securable you are checking, and securable class (and subclass for certain things like column level security. The securable class I will be focusing on in this blog is simply ‘OBJECT’, for objects in the database such as tables, procedures, etc. There are quite a few other ones, if you were interested in a complete view of all of the rights a user may have: APPLICATION ROLE, ASSEMBLY, ASYMMETRIC KEY, CERTIFICATE, CONTRACT, DATABASE, ENDPOINT, FULLTEXT CATALOG, LOGIN, MESSAGE TYPE, REMOTE SERVICE BINDING, ROLE, ROUTE, SCHEMA, SERVER, SERVICE, SYMMETRIC KEY, TYPE, USER, XML SCHEMA COLLECTION. For more details, check: HAS_PERMS_BY_NAME and fn_my_permissions.

To get started, I will build a small scenario that will allow me to grant rights, and then change rights around and verify that things match as desired.

If you want to check if a different user other than the one logged in has access to an object, you will either need to:

1. Login as that user
2. Impersonate that user

As we typically will be working as a database owner/system administrator when configuring security, we will usually use impersonation to check the user’s rights. For example, if you want to ask SQL Server about a particular object, you might use HAS_PERMS_BY_NAME, in the following manner:

This returns:

server_principal     database_principal
-------------------- ------------------------
Login1               User1

-----------
1

Now, checking to see if User1 can access Table2:

This returns:

———–
0

This function works great if you are checking one item, like from an application before trying to execute it. But if you want to see all of a user’s effective rights on an object, use fn_my_permissions.

For example, if we need to see User1 rights to Table1, we can execute (still in the security context of User1 from the previous code):

This returns the following, with the row with subentity_name as ” being table permissions, and Table1Id being the column permission:

entity_name      subentity_name    permission_name
---------------- ----------------- ------------------------
dbo.Table1                         SELECT
dbo.Table1       Table1Id          SELECT

Now, let’s change back to the security context of the user that is a member of the db_owner role:

This will show that the dbo user has every possible, logical, right to the table:

permission_name
--------------------------------
SELECT
UPDATE
REFERENCES
INSERT
DELETE
VIEW CHANGE TRACKING
VIEW DEFINITION
ALTER
TAKE OWNERSHIP
CONTROL

Finally, taking this to the final step for this blog, let’s get all of the object level rights for a user in the entire database:

To get all of the user’s rights, take all of the objects in the database, ignoring any objects that have a parent_object_id such as triggers or constraints, and use CROSS APPLY to execute the function for every object.

To make this easier to user, I am going to compile this into a view, called Utility.EffectiveSecurity (and give rights to it to Public, so every user can execute it.)

Now I can execute:

This returns:

object_type             schema_name     object_name     permission_name
----------------------- --------------- --------------- ---------------------------
USER_TABLE              Demo            Table1          SELECT
SQL_STORED_PROCEDURE    Demo            Procedure1      EXECUTE

The real value here, is that what if we need to verify a change in security works.

The following example is similar to what I recently have done for our system (though a bit less derived!) Let’s take User1, and put the security that it currently has into two roles, one that can execute the stored procedure, one that can select from the table. We first execute:

This saves off the current security, giving us the ability to compare User1’s rights before the security change. Now, make your changes to the security:

Query from Utility.EffectiveSecurity as User1 and you will see no output as User1 has been stripped of all rights, other than the rights to select from the EffectiveSecurity view. Then execute the following:

And now check to make sure you got the security right, in that it matches what User1 previously had for security:

You can see that I added a new permission in the mix when I was creating my roles, so this code outputs:

permission_disposition object_type     schema_name    object_name         permission_name
---------------------- --------------- -------------- ------------------- ----------------------
NEW                    USER_TABLE      Demo            Table2              SELECT

Remove that right:

Run the previous query again, and you will see that everything is the same in the current security configuration for User1 as it was before we started. So we have achieved the desired effect by removing rights from User1, and giving back rights through a couple of roles.