Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server Security: Fixed Database Roles Expand / Collapse
Author
Message
Posted Friday, December 5, 2003 12:00 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, November 14, 2014 7:14 AM
Points: 6,625, Visits: 1,876
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/bkelley/sqlserversecurityfixeddatabaseroles.asp

K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #18916
Posted Friday, December 12, 2003 3:45 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:59 AM
Points: 2,913, Visits: 1,842
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.


LinkedIn Profile
Newbie on www.simple-talk.com
Post #90120
Posted Friday, December 12, 2003 6:48 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, November 14, 2014 7:14 AM
Points: 6,625, Visits: 1,876

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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter

Post #90121
Posted Friday, December 12, 2003 9:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 6, 2004 11:03 AM
Points: 109, Visits: 1
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




Post #90122
Posted Friday, December 12, 2003 10:35 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, November 14, 2014 7:14 AM
Points: 6,625, Visits: 1,876

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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter

Post #90123
Posted Friday, January 19, 2007 9:49 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, March 6, 2013 12:29 PM
Points: 332, Visits: 21
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?



Post #338259
Posted Friday, January 19, 2007 1:58 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:34 AM
Points: 6,259, Visits: 2,031
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
Post #338350
Posted Thursday, January 22, 2009 3:50 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 30, 2014 10:13 AM
Points: 80, Visits: 206
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



thanks, ERH
Post #642157
Posted Thursday, January 22, 2009 7:06 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, November 14, 2014 7:14 AM
Points: 6,625, Visits: 1,876
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #642199
Posted Monday, June 15, 2009 5:25 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, December 6, 2012 8:30 AM
Points: 879, Visits: 810
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 DATABASE G GRANT

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
Post #734901
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse