Hello experts,
I am trying to create a SQL login that has SELECT permissions on around 10 tables in a database. So I don't want it to be able to select from any of the other tables. I tried not adding the login to any database roles. All that was checked was 'public'.
Then I explicitly ran GRANT SELECT statements for the 10 tables.
I tested out the permissions by logging in using SQL Server Authentication and trying to select from another table NOT in the list of 10. To my surprise, the rows from the table were returned.
I had thought that it would return an error saying SELECT permission denied.
I checked the login mapping and saw that its schema was dbo. Maybe that was it. So I changed it to guest, without success, and then to a schema named after the SQL login itself. Still no luck - it returned rows from the supposedly not permitted table.
Does anyone know why this login is able to select from tables for which I did not grant SELECT permissions? It's extremely confusing!
Thanks for any help.
-- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Does the public role have read access to those tables? I don't think you can remove a user from public.
You could create a new role, add the user to the role. Deny access to all schemas, then grant access to the ten tables. The deny may beat the grant, so you might have to revoke the permissions on those ten tables first, then grant select.
This assumes you create a database user for the login, rather than map it to guest.
I don't know a great deal about permissions, all our users have access to at least whole schemas, so you will probably get a more exact answer.
February 25, 2022 at 5:00 pm
Are AD groups given permissions to tables?
And does that login belong to one of those AD groups?
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
February 25, 2022 at 5:02 pm
I tested out the permissions by logging in using SQL Server Authentication and trying to select from another table NOT in the list of 10. To my surprise, the rows from the table were returned.
There's an easier way to test that, assuming you are a sysadmin:
EXEC AS LOGIN = 'login_name'; /* or "USER ="
<test select(s) here>
REVERT;
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
February 25, 2022 at 7:57 pm
Maybe the user is in a role etc. These two queries may help you:
SELECT P.state_desc COLLATE DATABASE_DEFAULT + ' '
+ P.[permission_name] COLLATE DATABASE_DEFAULT + ' ON '
+ QUOTENAME(S.[name] COLLATE DATABASE_DEFAULT)
+ '.' + QUOTENAME(O.[name] COLLATE DATABASE_DEFAULT)
+ COALESCE('(' + QUOTENAME(C.[name] COLLATE DATABASE_DEFAULT) + ')', '')
+ ' TO ' + QUOTENAME(U.name COLLATE DATABASE_DEFAULT) + ';'
FROM sys.database_permissions P
JOIN sys.sysusers U
ON P.grantee_principal_id = U.uid
JOIN sys.objects O
ON P.major_id = O.object_id
JOIN sys.schemas S
ON O.[schema_id] = S.[schema_id]
LEFT JOIN sys.columns C
ON O.object_id = C.[object_id]
AND P.minor_id = C.column_id
ORDER BY U.[name], S.[name], O.[name], C.[name];
SELECT 'ALTER ROLE ' + R.[name] + ' ADD MEMBER ' + U.[name] + ';'
FROM sys.database_principals U
JOIN sys.database_role_members X
ON X.member_principal_id = U.principal_id
JOIN sys.database_principals R
ON R.principal_id = X.role_principal_id
WHERE U.[name] <> 'dbo';
March 1, 2022 at 2:06 pm
Thanks everyone!
I discovered something so weird I had not even guessed it. At some point the public role of this database was assigned permissions. 🙁 As Ed B surmised, so I am marking his reply as the answer. Once I get that cleaned up, the original issue I had will be resolved.
Thanks again to all.
-- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply