SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Permissions to access all sysusers records


Permissions to access all sysusers records

Author
Message
JacekO
JacekO
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3355 Visits: 617
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.

---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.

John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)

Group: General Forum Members
Points: 80498 Visits: 17931
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
Erland Sommarskog
Erland Sommarskog
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13372 Visits: 879
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.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
JacekO
JacekO
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3355 Visits: 617
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.

---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.

John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)

Group: General Forum Members
Points: 80498 Visits: 17931
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
Erland Sommarskog
Erland Sommarskog
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13372 Visits: 879
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.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Erland Sommarskog
Erland Sommarskog
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13372 Visits: 879
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.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
JacekO
JacekO
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3355 Visits: 617
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...

---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.

JacekO
JacekO
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3355 Visits: 617
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'

---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.

Erland Sommarskog
Erland Sommarskog
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13372 Visits: 879
Certificate signing will not require any changes to the code. Have you looked at my article yet?

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search