March 8, 2017 at 4:52 am
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]
March 8, 2017 at 4:57 am
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
March 8, 2017 at 5:06 am
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]
March 8, 2017 at 5:30 am
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]
March 8, 2017 at 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
March 8, 2017 at 5:45 am
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]
March 8, 2017 at 5:46 am
John Mitchell-245523 - Wednesday, March 8, 2017 5:42 AMThis 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]
March 8, 2017 at 6:04 am
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]
March 8, 2017 at 6:09 am
John Mitchell-245523 - Wednesday, March 8, 2017 5:42 AMThis 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]
March 8, 2017 at 6:10 am
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]
March 8, 2017 at 6:13 am
Erland Sommarskog - Wednesday, March 8, 2017 5:45 AMThe 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]
March 8, 2017 at 6:16 am
Erland Sommarskog - Wednesday, March 8, 2017 6:10 AMCertificate 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]
March 8, 2017 at 6:27 am
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