SQL Server Performance

permission

Discussion in 'SQL Server 2005 General DBA Questions' started by jn4u, Mar 2, 2009.

  1. jn4u Member

    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.

  2. Adriaan New Member

    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();
  3. jn4u Member

    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
  4. jn4u Member

    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.
  5. Subhash Chandra New Member

    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.
  6. ymerejtrebor New Member

    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

Share This Page