December 12, 2003 at 12:00 am
Comments posted to this topic are about the item SQL Server Security: Fixed Database Roles
K. Brian Kelley
@kbriankelley
December 12, 2003 at 3:45 am
Nice summary.
The one thing that puzzles me is that in SQL6.5 we used the sp_addalias in order to alias users to the dbo as there wasn't a db_owner role.
This got around the business of accidentally creating objects not owned by the dbo.
The current books online says that this practice is only included for backwards compatibility. The implication of this is that
a) It is going to be made obsolete at some time in the future.
b) Its use is frowned upon.
Frankly, I find the db_owner role a bit of a red herring given the non-dbo owned object pitfall and that sp_addalias works fine.
What are your views on this?
========================
He was not wholly unware of the potential lack of insignificance.
December 12, 2003 at 6:48 am
Keep in mind that as either a member of the db_owner or db_ddladmin role, you could create objects that are dbo owned. We make it a standard practice where I work to include owners on all object creation statements just as a general rule so the extra 4 characters per object isn't a concern for us. However, if that isn't a standard practice, I can see the issue.
One of the issues with using aliases is they don't show up in Enterprise Manager. While the information can be seen by executing the T-SQL commands, a lot of DBAs rely on EM because it's quick and easy. When dealing with users and roles, I'll admit I use it a lot myself. That's one of the main issues I see with it.
Another issue is there may be cases where you don't want anyone accidentally touching certain objects in the "wrong" way. For instance, you have a lookup table and there shouldn't be any INSERT, UPDATE, or DELETE statements run against it. Yet you have folks that need to have basically db_owner permissions (this is a rare case, but it is possible). The dbo user bypasses all permissions. Members of the db_owner role do not. Therefore, the table could be set with DENY INSERT, UPDATE, DELETE to public and those who were a member of the db_owner role (but not mapped or aliased to dbo) could do what they needed to do within the database, but they couldn't accidentally change data in the table.
K. Brian Kelley
@kbriankelley
December 12, 2003 at 9:55 am
Thanks for the article.
What buggs me is the public role ijn Master db
because it has powers against information_schema objects
Could we get rid of those permissons with no further negative effects?
Thanks
Lavinia T
December 12, 2003 at 10:35 am
In some cases, yes, but not in all cases. My GSEC practical is now online. Its title: SQL Server 2000: Permissions on System Tables Granted to Logins Due to the Public Role.
You can find it here: http://www.giac.org/practical/GSEC/KBrian_Kelley_GSEC.pdf
K. Brian Kelley
@kbriankelley
January 19, 2007 at 9:49 am
I enjoyed this article, it brings to mind a question that I've not been able to answer regarding permissions, maybe someone out there can suggest a solution.
I'm working on an ASP.NET application that uses SQL2005 as the DBMS. We're using Integrated Security only -- SQL Security is not enabled. We're also using transactional replication to replicate data to local clients, we also retrieve and process data from these clients (not via replication). The application maps all users to a Windows user that has explicited defined permissions to the database, which do not include dbo_owner or syaadmin rights. The application is meant to include the ability to back up the database (after the user has determined that all transaction data has been retrieved from the client machines), therefore this standard user needs the ability to create a backup.
The logical solution is to add this standard user to the db_backupoperator role. However, I've found that when replication is installed, the server fires a procedure called sp_MSrepl_backup_start before running the backup. This proc. examines the permissions of the user running the backup and raises an error (18799) if the user is not a member of the sysadmin or dbcreator server roles, or db_owner on the database.
I defeated this by using a try-catch in the procedure we call to run the backup, however, I'm concerned that I'm working around a problem that is more profound than I understand. Can anyone propose a better way to solve this problem?
January 19, 2007 at 1:58 pm
dbachmann,
When replication is installed *many* of the database security roles that used to work are effectively broken. The reason is that M$ is hardcoding those permissions on the replication stored procedures that are "hooked" at call time when either backup/restore occur. Personally I confronted the same expericence not only at backup time but also at restore time under "certain" conditions. I my opinion Replication stored procedures are incorrectly coded because they are not only called from a wrapper but also from BACKUP/RESTORE.
My solution was simpler in one case that I could afford granting db_owner permissions to that account but in other cases the solution was what you just did. Now if you are in control of the server another trick is to create a pooling job that reads from a table where you post your requests and the job (running under higher privileges ) does the backup for you.
Good Luck
* Noel
January 22, 2009 at 3:50 pm
Good Article.
I posted this here as this is a question on this same topic. I know it is documented by MSFT that these roles are set in stone, cannot be changed, etc. That being said has anyone been able to find a way to create a new server level role or modify any of the default ones?
Any feedback would be appreciated.
Thanks,
Eddie
January 22, 2009 at 7:06 pm
There is not a way to create new server roles, no. You can, in SQL Server 2005 and 2008, assign server level permissions to logins. So if your logins were in a Windows group, you would accomplish the same purpose.
K. Brian Kelley
@kbriankelley
June 15, 2009 at 5:25 am
I'm trying to get a question answered and have not found the complete answer; consider the following:
I create ba new database, NewDB with a table, NewTable. I create a new login, let's make it an SQL login: NewUser. I give NewUser access to NewDatabase but assign NO PERMISSIONS what-so-ever. When I open a Querywindow on NewDB and change connection to NewUser; next I type SELECT * FROM NewTable and lo! I see a resultset! Which surprises me. I thought that the PUBLIC role default had not any permission, but it seems it has at least READ permission. Which is not what I would want.
Now, is it so, that a user which is given access to a database will always have read permission because of the PUBLIC role? I have checked the security catalogues for PUBLIC and find among other the following:
select * from sys.server_permissions where grantee_principal_id= (select principal_id from sys.server_principals where name='public')
The first result is this:
class class_descr major_id minor_d grantee_id grontor_id type permission_name state state_descr
100 SERVER 0 0 2 1 VWDB VIEW ANY DATABASEGGRANT
Specifically the type/permissionname makes me wonder: VWDB/View Any Database. This seems to be the default for the Public role, and it is the case on any SQL Server I have checked sofar, 2005 AND 2008.
Is this behaviour intentional? Do I miss something here?
Greetz,
Hans Brouwer
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply