Hey Mr. DBA, What Permissions Do I Have On This Database?

<div><p>I get this question at work from time to time. Fortunately SQL 2005\2008 has a built in table valued function to help answer this. It’s called <code style="font-size: 12px"><span style="color: darkred">fn_my_permissions</span></code> and it returns the effective permissions granted to a securable. The syntax is:</p> <pre class="brush: sql">fn_my_permissions(securable, 'securable_class')</pre> <p>In simple speak, <em>securable </em>means the table, procedure, function, etc. that you want to know what permissions you have on. <em>Securable_class</em> is the name of the class that corresponds to the securable. Examples of securables are: tables, views, functions, and procedures. Examples of securable classes are:  ASSEMBLY, DATABASE, OBJECT, and SERVER. Note that if you use the value “default” (minus the quotes) for either securable or securable_class SQL Server simply interprets it as NULL. </p> <p>Where this comes in really useful is when you need to know the effective permissions of someone else besides you. To do this you can use <code style="font-size: 12px"><span style="color: blue">EXECUTE AS</span></code> to impersonate a login, then call <code style="font-size: 12px"><span style="color: darkred">fn_my_permissions</span></code> to see what that user has. When you’re done, use <code style="font-size: 12px"><span style="color: blue">REVERT</span></code> to change the login context back to yourself. Note to do this requires IMPERSONATE permissions on the login\user that you’re trying to impersonate; for logins this is implicit if you’re a member of the sysadmin fixed server role and for database users this is implicit if you’ve got db_owner rights.</p> <p><strong></strong></p> <p><strong></strong></p> <p><strong>Examples</strong></p> <ul> <li>List your effective permissions on the server <pre class="brush: sql">SELECT * FROM fn_my_permissions(NULL, 'SERVER');</pre> <li>List your effective permissions on the database <pre class="brush: sql">SELECT * FROM fn_my_permissions(NULL, 'DATABASE');</pre> <li>List your effective permissions on a procedure <pre class="brush: sql">SELECT * FROM fn_my_permissions('sp_MSforeachtable', 'OBJECT');</pre> <li>List the effective permissions on the database for a server login <pre class="brush: sql">EXECUTE AS LOGIN = 'MYDOMAIN\some.user'; SELECT * FROM fn_my_permissions(NULL, 'DATABASE'); REVERT; </pre> <li>List the effective permissions on the database for a database user <pre class="brush: sql">EXECUTE AS USER = 'MYDOMAIN\some.user'; SELECT * FROM fn_my_permissions(NULL, 'DATABASE'); REVERT; </pre> <li>List all securable classes <pre class="brush: sql">SELECT DISTINCT class_desc FROM fn_builtin_permissions(DEFAULT) ORDER BY class_desc; </pre></li></ul></div>

Share This

• Twitter
• Google+
• Linked In
• Facebook

Related Posts

Security SQL Server 2005 SQL Server 2008 Tips