September 15, 2017 at 6:39 am
Hi Folks,
I have a login with 'public' server role and mapped to many databases . In response to a request to grant read-only to a user associated with this login , I made the user member of db_datareader and db_denydatawriter DB roles and stripped it off the db_owner role but still the user is able to write to that specific database. I even removed the access to that database at login level. Any reason that allows it to write ? Its a sql native user . Thank you.
September 15, 2017 at 6:56 am
Arsh - Friday, September 15, 2017 6:39 AMHi Folks,
I have a login with 'public' server role and mapped to many databases . In response to a request to grant read-only to a user associated with this login , I made the user member of db_datareader and db_denydatawriter DB roles and stripped it off the db_owner role but still the user is able to write to that specific database. I even removed the access to that database at login level. Any reason that allows it to write ? Its a sql native user . Thank you.
What Server roles does the login have? If you have denied the write permission, and revoked it's db_owner permissions, yet it can still write, this implies the user is a sysadmin. This sounds like a recipe for disaster, considering you start your post by stating it is a "public" role. You're public roles should NEVER be a db_owner on any database and NEVER EVER EVER have sysadmin rights.
Also, is the user a member of any AD groups that have db_owner rights? If so, db_owner will overrule the deny permissions.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 15, 2017 at 8:20 am
Thom A - Friday, September 15, 2017 6:56 AMArsh - Friday, September 15, 2017 6:39 AMAlso, is the user a member of any AD groups that have db_owner rights? If so, db_owner will overrule the deny permissions.
Thanks . No , it's an SQL native user. The public is assigned to the login so that because the user has to have write permissions to other DBs. Does this overwrite the read-only on any one database ?
September 15, 2017 at 8:31 am
Arsh - Friday, September 15, 2017 8:20 AMThanks . No , it's an SQL native user. The public is assigned to the login so that because the user has to have write permissions to other DBs. Does this overwrite the read-only on any one database ?
Public, by default, has no permissions on any database. I'm not sure what you mean by the public is assigned to a login. Public is a Server role, that Logins are given by default. Public is also a database role, that users are given by default.
As your user appears to have permissions to still write to the database , even after giving them DENY permissions, then either they are a db_owner on the database, or a sysadmin on their login..
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 15, 2017 at 8:52 am
Thom A - Friday, September 15, 2017 8:31 AMArsh - Friday, September 15, 2017 8:20 AMThanks . No , it's an SQL native user. The public is assigned to the login so that because the user has to have write permissions to other DBs. Does this overwrite the read-only on any one database ?Public, by default, has no permissions on any database. I'm not sure what you mean by the public is assigned to a login. Public is a Server role, that Logins are given by default. Public is also a database role, that users are given by default.
As your user appears to have permissions to still write to the database , even after giving them DENY permissions, then either they are a db_owner on the database, or a sysadmin on their login..
The login is mapped to many database with different role assignments . My objective is to let it have read-only on one particular database. I removed the db_owner role membership from the 'security' folder opening that particular database and granted the db_datareader and db_denydatawriter .
September 15, 2017 at 9:06 am
Arsh - Friday, September 15, 2017 8:52 AMThe login is mapped to many database with different role assignments . My objective is to let it have read-only on one particular database. I removed the db_owner role membership from the 'security' folder opening that particular database and granted the db_datareader and db_denydatawriter .
Are you saying that you deleted the db_owner role? That doesn't seem like a good idea in my opinion.
What does the below return?USE [Your Database];
GO
SELECT l.sysadmin, dp.name
FROM sys.sysusers u
JOIN sys.syslogins l on u.sid = l.sid
JOIN sys.database_role_members drm ON u.uid = drm.member_principal_id
JOIN sys.database_principals dp on drm.role_principal_id = dp.principal_id
where u.[name] = '[The Username in Question]';
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 15, 2017 at 9:50 am
Also check if you have trustworthy on in databases or cross database ownership chaining.
September 18, 2017 at 2:36 am
Thom A - Friday, September 15, 2017 9:06 AMArsh - Friday, September 15, 2017 8:52 AMThe login is mapped to many database with different role assignments . My objective is to let it have read-only on one particular database. I removed the db_owner role membership from the 'security' folder opening that particular database and granted the db_datareader and db_denydatawriter .Are you saying that you deleted the db_owner role? That doesn't seem like a good idea in my opinion.
What does the below return?
USE [Your Database];
GOSELECT l.sysadmin, dp.name
FROM sys.sysusers u
JOIN sys.syslogins l on u.sid = l.sid
JOIN sys.database_role_members drm ON u.uid = drm.member_principal_id
JOIN sys.database_principals dp on drm.role_principal_id = dp.principal_id
where u.[name] = '[The Username in Question]';
Thanks Steve and Thom . The above query returns zero records. Steve , the trustworthy setting is set to False. Thom , I meant I only unchecked the db_owner role for this user on this particular database. The same user has db_owner role assingned to it on other databases in the instance . Can this be a reason for it to be able to write despite having db_datareader and db_denydatawrite ?
Thanks
September 18, 2017 at 2:41 am
Arsh - Monday, September 18, 2017 2:36 AMThom A - Friday, September 15, 2017 9:06 AMAre you saying that you deleted the db_owner role? That doesn't seem like a good idea in my opinion.What does the below return?
USE [Your Database];
GOSELECT l.sysadmin, dp.name
FROM sys.sysusers u
JOIN sys.syslogins l on u.sid = l.sid
JOIN sys.database_role_members drm ON u.uid = drm.member_principal_id
JOIN sys.database_principals dp on drm.role_principal_id = dp.principal_id
where u.[name] = '[The Username in Question]';Thanks Steve and Thom . The above query returns zero records. Steve , the trustworthy setting is set to False. Thom , I meant I only unchecked the db_owner role for this user on this particular database. The same user has db_owner role assingned to it on other databases in the instance . Can this be a reason for it to be able to write despite having db_datareader and db_denydatawrite ?
Thanks
If that query is returning no results, then either the user doesn't exist, or they aren't linked to a login. That further implies (to me) that either you ran the query incorrect, or the login isn't mapped to a user on the database (and thus must be a sysadmin to be able to access said database).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 18, 2017 at 4:14 am
Thom A - Monday, September 18, 2017 2:41 AMArsh - Monday, September 18, 2017 2:36 AMThom A - Friday, September 15, 2017 9:06 AMAre you saying that you deleted the db_owner role? That doesn't seem like a good idea in my opinion.What does the below return?
USE [Your Database];
GOSELECT l.sysadmin, dp.name
FROM sys.sysusers u
JOIN sys.syslogins l on u.sid = l.sid
JOIN sys.database_role_members drm ON u.uid = drm.member_principal_id
JOIN sys.database_principals dp on drm.role_principal_id = dp.principal_id
where u.[name] = '[The Username in Question]';Thanks Steve and Thom . The above query returns zero records. Steve , the trustworthy setting is set to False. Thom , I meant I only unchecked the db_owner role for this user on this particular database. The same user has db_owner role assingned to it on other databases in the instance . Can this be a reason for it to be able to write despite having db_datareader and db_denydatawrite ?
Thanks
If that query is returning no results, then either the user doesn't exist, or they aren't linked to a login. That further implies (to me) that either you ran the query incorrect, or the login isn't mapped to a user on the database (and thus must be a sysadmin to be able to access said database).
Yes Thom you are right , the user is not linked to a login . But now the question is how come its able to do updates to tables despite having allocated only read-only privileges as above ? Please note that the same user is db_owner for some other databases in the instance . Can this be the reason ?
September 18, 2017 at 4:24 am
Arsh - Monday, September 18, 2017 4:14 AMYes Thom you are right , the user is not linked to a login . But now the question is how come its able to do updates to tables despite having allocated only read-only privileges as above ? Please note that the same user is db_owner for some other databases in the instance . Can this be the reason ?
db_owner is database specific role; much like a user is database specific. If the user is not linked to a login, how are you using it? With EXECUTE AS, impersonation?
What is returned in the messages if you connect to your server under the LOGIN you are having problems with, accessing the database the LOGIN can still write to:USE [Database in question];
GO
PRINT ('The current User is: ' + CURRENT_USER)
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 18, 2017 at 7:33 am
Thom A - Monday, September 18, 2017 4:24 AMArsh - Monday, September 18, 2017 4:14 AMYes Thom you are right , the user is not linked to a login . But now the question is how come its able to do updates to tables despite having allocated only read-only privileges as above ? Please note that the same user is db_owner for some other databases in the instance . Can this be the reason ?db_owner is database specific role; much like a user is database specific. If the user is not linked to a login, how are you using it? With EXECUTE AS, impersonation?
What is returned in the messages if you connect to your server under the LOGIN you are having problems with, accessing the database the LOGIN can still write to:
USE [Database in question];
GO
PRINT ('The current User is: ' + CURRENT_USER)
I am getting the current user as the same user the problem is . Also , I checked to find that this user exists as without a login in some databases and with the login in some . Is that a problem ? I think the people who created it , have kept it unnecessarily complicated . To enable deleting if altogether and creating afresh , I've now transferred authorization/schema ownership to dbo going manually to every database but in one of the databases , I cannot transfer the db_owner schema which this user owns so that I can drop this login and associated users and recreated at afresh . Would be great if you can help in transferring the db_owner schema to some other user. Thanks.
September 18, 2017 at 8:13 am
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply