Permissions to access all sysusers records

  • I have this problem I can not figure out.
    The users login to the database using their own SQL Server logins. There is a stored procedure which accesses syslogins in order to show the users who was the one who created certain records.
    It works fine with the exception of one user. This one user can only see her own records or dbo's.
    I need her to see all the records.
    Any idea how to approach this problem?

    Thanks a bunch.

    ---------------------------------------------
    [font="Verdana"]Nothing is impossible.
    It is just a matter of time and money.[/font]

  • Please can we see the code?  Have you tried EXECUTE AS OWNER or dbo?  You should be using sys.database_principals instead of syslogins, since the latter is deprecated.

    John

  • syslogins or sysusers? That is, server logins or database users?

    As John says, you should be using sys.xxxxx_principals, but whether that is database_principals or server_principals depends on wherther you are reading you are reading database users or server logins.

    The default is that  unprivileges users cannot view metadata they don't have access to. So the other logins have some permission or role membership they should not have.

    The correct solution would be to put the code that runs the query against sys.xxxxx_principals in a stored procedure that is signed with a certificate and then a user (if on database level) or login (if on server level) created from that certificate has been granted VIEW DEFINITION (if on database level) or VIEW ANY DEFINITION (if on server level).

    I discuss certificate signing in detail in this article on my web site:
    http://www.sommarskog.se/grantperm.html.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • There are several SPs that link to sysusers. Problem is with all of them.
    This is a fragment of the relevant code:
    SELECT
    name,
    CASE
        WHEN pol_type = 'T' THEN 'Transit'
        WHEN pol_type = 'P' THEN 'Parking'
        WHEN pol_type = 'D' THEN 'DependentCare'
        WHEN pol_type = 'M' AND pol_HRA = 0 THEN 'Medical'
        ELSE 'HRA'
    END AS Product,
    claims
    FROM @Tmp
    INNER JOIN sysusers ON uid = userID
    ORDER BY name, Product

    userID is a value set by UDF on each record when created:
    ALTER FUNCTION [dbo].[fn_SuserID]()
    RETURNS SMALLINT
    AS
    BEGIN
        DECLARE @ID SMALLINT

        SELECT @ID = uid
        FROM sysusers
        WHERE sid = SUSER_SID()

        RETURN (ISNULL(@ID, 1))
    END

    As far as I can tell most users can see all the records when querying SELECT * FROM sysusers except this one. I tried to compare rights and as far as I can tell I do not see any difference in permissions, but I might be missing something obvious.

    Adding 'WITH EXECUTE AS OWNER' causes this error for everyone:

    Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.

    ---------------------------------------------
    [font="Verdana"]Nothing is impossible.
    It is just a matter of time and money.[/font]

  • This is going to fail if the user who created the record has since been deleted.  Why don't you change the function so it gets the user name instead of the ID, and then you wouldn't need to join to a system view at all?

    John

  • That error may be due to that the database has been restored from a different server and there is a mismatch between dbo in the database and the database owner on server level. That can be addressed by changing the database owner forth and back.

    But  EXECITE AS OWNER is not going to work for you anyway, since user_id() will resolve to dbo and not the actual user. Use certificate signing instead.

    The problem is not with the user that cannot access sysusers, but with all the others that have elevate privileges of some sort.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • John Mitchell-245523 - Wednesday, March 8, 2017 5:42 AM

    This is going to fail if the user who created the record has since been deleted.  Why don't you change the function so it gets the user name instead of the ID, and then you wouldn't need to join to a system view at all?

    John

    Good point. Keep it simple.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • The original SQL Server was 2000. At some point it was upgraded to 2008R2. I just did some extra testing and I can see the newer users have the permission access problem and the older users do not.This wasn't noticed before because the users did not need this information and most of them are disabled by now anyway.
    I don't remember when the server was upgraded but I think some users without the problem were created already on the 2008R2 version.
    I understand there might be a  'proper way' to  solve this  issue but it will require extensive changes because the UDF mentioned before is used ALL OVER the database. I just think there should be a way to change some value in one of the system tables to let it work. The question is which table...

    ---------------------------------------------
    [font="Verdana"]Nothing is impossible.
    It is just a matter of time and money.[/font]

  • John Mitchell-245523 - Wednesday, March 8, 2017 5:42 AM

    This is going to fail if the user who created the record has since been deleted.  Why don't you change the function so it gets the user name instead of the ID, and then you wouldn't need to join to a system view at all?

    John

    I don't delete the users. They just get disabled when they leave the company.

    I might have to go this route but this is going to be a lot of work and storing names (VARCHAR) vs IDs (INT) is kind of against the 'SQL nature'

    ---------------------------------------------
    [font="Verdana"]Nothing is impossible.
    It is just a matter of time and money.[/font]

  • Certificate signing will not require any changes to the code. Have you looked at my article yet?

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • Erland Sommarskog - Wednesday, March 8, 2017 5:45 AM

    The problem is not with the user that cannot access sysusers, but with all the others that have elevate privileges of some sort.

    The million dollar question is how do I find out what privileges? Nothing is obvious by looking at the user or login permissions. If this is elevated there might be some other surprises out there I really don't want. One of the users I have is for access form a web portal and it has limited access but what if it does have some secret access I don't know about?

    ---------------------------------------------
    [font="Verdana"]Nothing is impossible.
    It is just a matter of time and money.[/font]

  • Erland Sommarskog - Wednesday, March 8, 2017 6:10 AM

    Certificate signing will not require any changes to the code. Have you looked at my article yet?

    Well, I opened the link but this is not something one can read thru and fully understand in 5 minutes. I will have a closer look and probably some questions.

    ---------------------------------------------
    [font="Verdana"]Nothing is impossible.
    It is just a matter of time and money.[/font]

  • I never said that it would be a quick thing. There is a reason that I wrote an aritcle about it, so I don't have to make a detailed explanation every time as the question comes up.

    Once you have the certificates going, you can start to look at permissions assigned to users and roles to tidy things up.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply