How to view Effective Permissions? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

How to view Effective Permissions?

I want to view permissions assigned to a group. I read about using has_perms_by_name, however I don’t believe you can impersonate a group. EXECUTE AS Login = ‘BLAIRNETMIS Developers’
GO
SELECT has_perms_by_name(db_name(), ‘Database’, ‘ANY’)
GO
REVERT
GO The above does not work. Any suggestions? Thanks, Dave
Check the BOL topic "EXECUTE AS" name must be a singleton account, and cannot be a group, role, certificate, key, or built-in account, such as NT AUTHORITYLocalService, NT AUTHORITYNetworkService, or NT AUTHORITYLocalSystem.
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
You could use:
select [Role] = r.name, [Member] = m.name
from sys.database_role_members rm
join sys.database_principals r on rm.role_principal_id = r.principal_id
join sys.database_principals m on
rm.member_principal_id = m.principal_id
order by r.name, m.name And also look into fn_my_permissions function. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
What about effective permissions such as types of ‘References’, ‘Take Ownership’, ‘View Definition’? I know how to grant someone these permissions on an object by object basis, but I don’t know how to view a list of permissions already granted and I want to avoid having to click on each object in the GUI to see Effective Permissions for that object. Thanks, Dave
Check sp_helprotect in BOL…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
That did the trick. Have you ever tried to look at the code for sp_helprotect in 2005? I thought I would take a look to see what system tables/views are being referenced for the effective permissions data, however when I try to run the code directly it errors on the following command. UPDATE #t1_Prots
SET ProtectTypeName = CASE ProtectTypeName
WHEN ‘G’ THEN ‘Grant’ WHEN ‘D’ THEN ‘Deny’ WHEN ‘W’ THEN ‘Grant_WGO’ END
, ActionFullName = ISNULL((SELECT v.name FROM sys.syspalnames v WHERE v.class = ‘HPRT’ AND v.value = ActionName),
permission_name(ActionCategory, ActionName) )
Permission_name is not recognized as a function. When i trace the code in profiler I see the update statement execute. Any idea what permission_name is and why the command cannot be executed from a query window? Thanks, Dave
In sql server 2005 some of the tables/procedures/functions are not directly accessed by the users…
In sql server 2005 security is locked down…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
See this bloghttp://blogs.conchango.com/jamietho…L-Server-2005_3A00_-View-all-permissions.aspx in this case about viewing all the permissions. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Thanks Satya. That’s perfect. I like learning the system tables, despite Microsoft’s warnings about using them. I prefer to create my own administration scripts rather then using the functions and stored procs that come with SQL Server. That way I can taylor the output/reports to my needs. Dave
One way it is good to have complete authorisation on the server but version to version you might get the problems due to the deprecated features. As long as you know what to take care, go ahead. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Very true. Usually the system tables remain fairly unchanged between releases, but when a major release occurs is when the risk of change becomes more possible. I usually have anywhere from 3-5 years on a given release, depending upon the in-house application or third-party application vendor. Because of that I’m willing to put up with the disruption of changing my code once every few years. It’s a good thing to do for me because it forces me to understand the system tables in order to improve my troubleshooting ability. Dave
]]>