Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server Security: Fixed Database Roles


SQL Server Security: Fixed Database Roles

Author
Message
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (6.8K reputation)

Group: Moderators
Points: 6770 Visits: 1908
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
David.Poole
David.Poole
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3662 Visits: 3111
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
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (6.8K reputation)

Group: Moderators
Points: 6770 Visits: 1908

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

tulcanla
tulcanla
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
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



K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (6.8K reputation)

Group: Moderators
Points: 6770 Visits: 1908

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

cbachmann
cbachmann
Old Hand
Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)

Group: General Forum Members
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?



noeld
noeld
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6320 Visits: 2048
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
@STLSQLGuy
@STLSQLGuy
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 209
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

K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (6.8K reputation)

Group: Moderators
Points: 6770 Visits: 1908
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
FreeHansje
FreeHansje
SSC Eights!
SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)

Group: General Forum Members
Points: 885 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search