Impersonate - how do you tell what permissions the use is being impersonate with?

Question

Impersonate - how do you tell what permissions the use is being impersonate with?

OlyKLin

Ten Centuries

Points: 1333
More actions
April 6, 2018 at 10:44 am

#336962

I am trying to clean up access to our database and grant access to views to a specific schema only. I found out when I tested a windows group that I added, that every user that connects to the server and database is granted by Impersonation. But, I can't tell what permissions are being impersonated. They can see all of the objects. What steps do I need to take to revoke permission for everyone that connects and only allow what I want them to see?
I inherited this database so I'm finding that this server is wide open. I've looked up best practices but I'm not seeing anything that says remove permission to public. The guest on the database in question has DENY on Execute of all securables.
I'm using a Windows group that I'm adding users into via Active Directory. I've granted this the Db_datareader role. I want to use this as the role that anyone logging in, in this group will connect as. Otherwise, I don't wan't just anyone to have access to any databases.
I'm sure this is really basic security but I've tried to find something other than microsoft docs to explain how to do this. I've never had a problem with databases that I've created or servers that I've set up so there must be something that I'm missing to check
Thanks!
April 7, 2021 at 7:59 am

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

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

OlyKLin Ten Centuries Points: 1333 More actions · Answer 1

Update to my post - I found the group that was giving me grief. I had checked the db_datareader in the membership group to by windows group . Even though I granted permission to only BI Schema objects, they could still see all the objects for every schema. Once I unchecked the membership role, they can only see the objects with the BI Schema.

But now I have a different issue. The views access dbo tables so when the user tries to select from the view, it get's a message permission denied on a column in the view from a dbo table.

How do I grant permission to select from the view, still have access to the dbo tables but not necessarily see the tables?

Headlock Mike SSC Veteran Points: 225 More actions · Answer 2

Have you granted object level permissions?
GRANT SELECT ON View TO Login

I just created a login on my test box with no permissions; just mapped to the DB with the public role.
I then created a view to an existing table.
I then verified that the login could see no objects, then applied the grant, and was able to return rows from the view.
The underlying table was still invisible to the test login.

OlyKLin Ten Centuries Points: 1333 More actions · Answer 3

I did and I think the issue is that the tables are dbo and the views are a [BI] schema. I've seen posts where the only option was to create a table-value function to get around that. I'm hoping that's not the case?

Headlock Mike SSC Veteran Points: 225 More actions · Answer 4

Interesting. Try this and see what you get.

CREATE DATABASE TestDB GO

USE [master] GO

CREATE LOGIN [test] WITH PASSWORD=N'test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO

USE [TestDB] GO

CREATE USER [test] FOR LOGIN [test] GO

CREATE SCHEMA BI GO

GRANT SELECT ON SCHEMA :: BI TO test GO

CREATE TABLE dbo.TestTable ( Col1 VARCHAR(10) ,Col2 INT ) GO

INSERT INTO dbo.TestTable (Col1, Col2) VALUES ('Test',0),('Test2',1) GO

CREATE VIEW BI.TestTable AS

SELECT * FROM dbo.TestTable GO

Then change your connection to use the test login and run this query:

USE [TestDB] GO

SELECT * FROM BI.TestTable

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 5

OlyKLin - Friday, April 6, 2018 2:22 PM
I did and I think the issue is that the tables are dbo and the views are a [BI] schema. I've seen posts where the only option was to create a table-value function to get around that. I'm hoping that's not the case?

Do DBO and BI schemas have different owners? If so that could be the problem. If you want to re-establish ownership chaining, you could change the owner of the BI schema to be dbo, otherwise permissions would need to be granted at the table level.

OlyKLin Ten Centuries Points: 1333 More actions · Answer 6

Chris Harshman - Friday, April 6, 2018 3:03 PM
OlyKLin - Friday, April 6, 2018 2:22 PM
I did and I think the issue is that the tables are dbo and the views are a [BI] schema. I've seen posts where the only option was to create a table-value function to get around that. I'm hoping that's not the case?
Do DBO and BI schemas have different owners? If so that could be the problem. If you want to re-establish ownership chaining, you could change the owner of the BI schema to be dbo, otherwise permissions would need to be granted at the table level.

No, they are both DBO. I think I'm looking in the right place...I'm going to the schema under the database/security. Click properties. Schema Owner: dbo

OlyKLin Ten Centuries Points: 1333 More actions · Answer 7

Headlock Mike - Friday, April 6, 2018 2:49 PM

Hi Mike - this worked thank you. I noticed that you created a login and I'm trying to use a windows Group to assign permissions. Should I assign this group to a SQL User? I seem to remember something like this needing to be done years ago when I had to manage security...I'm really out of practice with this