September 20, 2011 at 3:00 am
Hi, I have a user in one of our databases which I cannot clarify how the user has administrative priviledges (ALTER,CONTROL) over all object of the database.
Here are the explicit given permissions:
User Name: atmUser
Mapped Login: atmUser
Role Membership: db_datareader, db_datareader
Owned Schema: none
Default schema: dbo
Server Role: public
In the securables tab of user properties window, the user has no explicit, but effectively alter-control for all tables,schemas, also it can create tables, rename tables, how can I understand the source of these priviledges?
Any help appreciated, Thanks for reading
September 20, 2011 at 3:29 am
I experienced something similar on a legacy database.
I added the username to the db_denydatawriter role...
USE [EachOfYourDatabases]
GO
EXEC sp_addrolemember N'db_denydatawriter', N'loginnamegoeshere'
GO
It turned out the [NT\Loginname] was part of an AD group with full rights but deny trumps allow...
May be worth testing this theory on a local/development environment first!
gsc_dba
September 20, 2011 at 3:33 am
This is an interesting read on permissions:
http://www.simple-talk.com/sql/database-administration/sql-server-security-cribsheet/
gsc_dba
September 20, 2011 at 7:29 am
Is has to read and write data, what we don't want is create, alter tables, db_denydatawriter will stop read and writes I think.. Can it be related to guest or public roles?
September 20, 2011 at 7:34 am
Is that login the database owner? Is it a member of a fixed server role?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 20, 2011 at 8:03 am
No, unfortunately not the owner of the database (my own account is db owner) , only public server role is selected for the login. Any ideas??
September 20, 2011 at 8:15 am
My bet: some of your developers have the nasty habit of granting permissions to groups like public or datareader.
run the following against your database to enumerate object level permissions , and database level permisions. the script is designed to created scripts to retain permissions, but you can just as easily use it to revoke them:
print 'go'
print '--object level perms'
select p.state_desc + ' ' + p.permission_name + ' ON [' + s.name +'].['+ o.name collate Latin1_general_CI_AS+ '] TO [' + u.name collate Latin1_general_CI_AS + ']' from sys.database_permissions p inner join sys.objects o on p.major_id = o.object_id inner join sys.schemas s on s.schema_id = o.schema_id inner join sys.database_principals u on p.grantee_principal_id = u.principal_id
print 'go'
print '--grant databasewide permissions'
select p.state_desc + ' ' + p.permission_name +' TO [' + u.name collate Latin1_general_CI_AS + ']' from sys.database_permissions p inner join sys.database_principals u on p.grantee_principal_id = u.principal_id
where p.class_desc='DATABASE'
September 20, 2011 at 8:35 am
Your bet was right.. Somebody has given all database level permissions to public role and guest user (there is no guest role, its a user but not mapped to a login??), I found the GUI where this was set.
So all users are member of public role implicitly?
print '--grant databasewide permissions'
select p.state_desc + ' ' + p.permission_name +' TO [' + u.name collate Latin1_general_CI_AS + ']' from sys.database_permissions p inner join sys.database_principals u on p.grantee_principal_id = u.principal_id
where p.class_desc='DATABASE'
Results:
GRANT ALTER TO [public]
GRANT ALTER ANY ASYMMETRIC KEY TO [public]
GRANT ALTER ANY APPLICATION ROLE TO [public]
GRANT ALTER ANY ASSEMBLY TO [public]
GRANT ALTER ANY CERTIFICATE TO [public]
GRANT ALTER ANY DATABASE AUDIT TO [public]
GRANT ALTER ANY DATASPACE TO [public]
GRANT ALTER ANY DATABASE EVENT NOTIFICATION TO [public]
GRANT ALTER ANY FULLTEXT CATALOG TO [public]
GRANT ALTER ANY MESSAGE TYPE TO [public]
GRANT ALTER ANY ROLE TO [public]
GRANT ALTER ANY ROUTE TO [public]
GRANT ALTER ANY REMOTE SERVICE BINDING TO [public]
GRANT ALTER ANY CONTRACT TO [public]
GRANT ALTER ANY SYMMETRIC KEY TO [public]
GRANT ALTER ANY SCHEMA TO [public]
GRANT ALTER ANY SERVICE TO [public]
GRANT ALTER ANY DATABASE DDL TRIGGER TO [public]
GRANT ALTER ANY USER TO [public]
GRANT AUTHENTICATE TO [public]
GRANT BACKUP DATABASE TO [public]
GRANT BACKUP LOG TO [public]
...
GRANT ALTER TO
GRANT ALTER ANY ASYMMETRIC KEY TO
GRANT ALTER ANY APPLICATION ROLE TO
GRANT ALTER ANY ASSEMBLY TO
GRANT ALTER ANY CERTIFICATE TO
GRANT ALTER ANY DATABASE AUDIT TO
GRANT ALTER ANY DATASPACE TO
GRANT ALTER ANY DATABASE EVENT NOTIFICATION TO
GRANT ALTER ANY FULLTEXT CATALOG TO
GRANT ALTER ANY MESSAGE TYPE TO
GRANT ALTER ANY ROLE TO
GRANT ALTER ANY ROUTE TO
GRANT ALTER ANY REMOTE SERVICE BINDING TO
GRANT ALTER ANY CONTRACT TO
GRANT ALTER ANY SYMMETRIC KEY TO
GRANT ALTER ANY SCHEMA TO
GRANT ALTER ANY SERVICE TO
GRANT ALTER ANY DATABASE DDL TRIGGER TO
GRANT ALTER ANY USER TO
GRANT AUTHENTICATE TO
GRANT BACKUP DATABASE TO
GRANT BACKUP LOG TO
...
September 20, 2011 at 8:43 am
sporoy (9/20/2011)
Your bet was right.. Somebody has given all database level permissions to public role and guest user (there is no guest role, its a user but not mapped to a login??), I found the GUI where this was set.So all users are member of public role implicitly?
print '--grant databasewide permissions'
select p.state_desc + ' ' + p.permission_name +' TO [' + u.name collate Latin1_general_CI_AS + ']' from sys.database_permissions p inner join sys.database_principals u on p.grantee_principal_id = u.principal_id
where p.class_desc='DATABASE'
Results:
GRANT ALTER TO [public]
GRANT ALTER ANY ASYMMETRIC KEY TO [public]
GRANT ALTER ANY APPLICATION ROLE TO [public]
GRANT ALTER ANY ASSEMBLY TO [public]
GRANT ALTER ANY CERTIFICATE TO [public]
GRANT ALTER ANY DATABASE AUDIT TO [public]
GRANT ALTER ANY DATASPACE TO [public]
GRANT ALTER ANY DATABASE EVENT NOTIFICATION TO [public]
GRANT ALTER ANY FULLTEXT CATALOG TO [public]
GRANT ALTER ANY MESSAGE TYPE TO [public]
GRANT ALTER ANY ROLE TO [public]
GRANT ALTER ANY ROUTE TO [public]
GRANT ALTER ANY REMOTE SERVICE BINDING TO [public]
GRANT ALTER ANY CONTRACT TO [public]
GRANT ALTER ANY SYMMETRIC KEY TO [public]
GRANT ALTER ANY SCHEMA TO [public]
GRANT ALTER ANY SERVICE TO [public]
GRANT ALTER ANY DATABASE DDL TRIGGER TO [public]
GRANT ALTER ANY USER TO [public]
GRANT AUTHENTICATE TO [public]
GRANT BACKUP DATABASE TO [public]
GRANT BACKUP LOG TO [public]
...
GRANT ALTER TO
GRANT ALTER ANY ASYMMETRIC KEY TO
GRANT ALTER ANY APPLICATION ROLE TO
GRANT ALTER ANY ASSEMBLY TO
GRANT ALTER ANY CERTIFICATE TO
GRANT ALTER ANY DATABASE AUDIT TO
GRANT ALTER ANY DATASPACE TO
GRANT ALTER ANY DATABASE EVENT NOTIFICATION TO
GRANT ALTER ANY FULLTEXT CATALOG TO
GRANT ALTER ANY MESSAGE TYPE TO
GRANT ALTER ANY ROLE TO
GRANT ALTER ANY ROUTE TO
GRANT ALTER ANY REMOTE SERVICE BINDING TO
GRANT ALTER ANY CONTRACT TO
GRANT ALTER ANY SYMMETRIC KEY TO
GRANT ALTER ANY SCHEMA TO
GRANT ALTER ANY SERVICE TO
GRANT ALTER ANY DATABASE DDL TRIGGER TO
GRANT ALTER ANY USER TO
GRANT AUTHENTICATE TO
GRANT BACKUP DATABASE TO
GRANT BACKUP LOG TO
...
Think of public as literally the public... do you want joe hacker to have alter on your tables- if so grant the permssions to public. Guest is most likely disabled, so slightly less concerning...
This is the laziest type of programming. Grant every permission to everyone and you are sure to not get any errors. You probably have to talk to the application folks an convince them to actually tell you (if they even know) what permissions the user actually needs. grant those, and then start revoking from public and guest. Or, if you dont care about breaking the application, just start revoking.
This is why you dont give your developers the ability to grant permssions in your production system.
September 20, 2011 at 8:43 am
sporoy (9/20/2011)
So all users are member of public role implicitly?
Yup, that's why it's called 'public'
May I suggest, shoot your developers, then get them to fix this mess (and maybe shoot them again). That's insane. Anyone that can connect to the server has massively high permissions in that database (through the guest user)
Guest is the permissions a login gets if it's not explicitly granted access to the DB. General recommendations are that public and guest have no permissions at all.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 21, 2011 at 12:58 am
Thanks, I will gradually revoke all public and guest permissions,
Have a nice day.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply