permission | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

permission

A Domain Windows Account has rights execute a Scalar Value Function that it not should. The Domain Windows Account is member of Domain Windows Group called VBAS_GVerkstad, that’s maps to SQL Server Login and the Database User in the database. It’s the same name for AD Group, Login and User. I have also check User is part of role GVerkstad, “Public”.I understand the db user or database role can get associated permissions at the following levels, the Server Level, Database Level, Schema Level, Database Role level, Application Role level or object level.I have tried to check these different levels to find out where the exec permission comes from. I understand that I can use sys.database_permission to get a listing of the permissions. But I’m really not able to find the spot or spots that gives the permission.
You might run this query to find all the roles of which your login is a member in the current db …
EXECUTE
AS login = ‘your_login‘;SELECT
u.*
FROM sys.sysusers u
WHERE IS_MEMBER(u.name) = 1 AND u.name <> USER_NAME();

I have tried that and that gives me the error as sa and my own account, member of
sys_admin.Msg 15404, Level 16, State 19, Line 1
Could not obtain information about Windows NT group/user ‘air5fleborr’, error code 0x5.
Google it and tired to change the dbowner of the databas to valid AD user but still get the error

Google and found this.
select USER_NAME(p.grantee_principal_id) AS principal_name,
dp.type_desc AS principal_type_desc,
p.class_desc,
OBJECT_NAME(p.major_id) AS object_name,
p.permission_name,
p.state_desc AS permission_state_desc
from sys.database_permissions p
inner JOIN sys.database_principals dp
on p.grantee_principal_id = dp.principal_id
where OBJECT_NAME(p.major_id) like ‘%fu_permission_omplanering%’
And if I understand this right its only gives me the Explicit permission. The fu_permission_omplanering has public excute rights. Removes the the excute to public on fu_permission_omplanering. Still the person is able to run the Scalara-value Functions. I tires this to get inherited permissions
WITH perms_cte as
(
select USER_NAME(p.grantee_principal_id) AS principal_name,
dp.principal_id,
dp.type_desc AS principal_type_desc,
p.class_desc,
OBJECT_NAME(p.major_id) AS object_name,
p.permission_name,
p.state_desc AS permission_state_desc
from sys.database_permissions p
inner JOIN sys.database_principals dp
on p.grantee_principal_id = dp.principal_id
)
–users
SELECT p.principal_name, p.principal_type_desc, p.class_desc, p.[object_name],
p.permission_name, p.permission_state_desc, cast(NULL as sysname) as role_name
FROM perms_cte p
WHERE principal_type_desc <> ‘DATABASE_ROLE’
UNION
–role members
SELECT rm.member_principal_name, rm.principal_type_desc,
p.class_desc, p.object_name, p.permission_name, p.permission_state_desc,rm.role_name
FROM perms_cte p
right outer JOIN (
select role_principal_id, dp.type_desc as principal_type_desc,
member_principal_id,user_name(member_principal_id) as member_principal_name,
user_name(role_principal_id) as role_name–,*
from sys.database_role_members rm
INNER JOIN sys.database_principals dp
ON rm.member_principal_id = dp.principal_id
) rm
ON rm.role_principal_id = p.principal_id
order by 1
I don’t find any fu_permission_omplanering in this list and stucked where user or role the get permission to execute.

If you want to stop someone to execute a function then why bother to check where from he is getting the permission, just use the DENY command. DENY is overweighted to all permission on all users except sysadmin.

You could also use this free tool to find out what the permissions are and where they are coming from.
I was able to get it and it helped me determine what my security model was and it helped me to see where things were coming from. It even parsed out my Active Directory Groups that were logins on my SQL Servers and showed me the users inside them.
Idera SQL secure helps to identify holes in SQL Server security and ensure compliance with increasing audit requirements. SQL secure collects and analyzes permissions data from SQL Server and Active Directory as well as the file system and registry to show who has access to what database objects and how that access is granted.
http://www.idera.com/Content/Show42.aspx

]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |