June 7, 2011 at 3:32 am
If a user is a member of the dbo database role, in what circumstances might they need any other database role memebrship?
The reason I ask this is I was on a course that asked us to add a new user to a database and assign them to dbo AND securityadmin? Surely if they are dbo they inherit the securityadmin permissions?
Regards
Steve
June 12, 2011 at 6:10 pm
Steve Hindle-535638 (6/7/2011)
If a user is a member of the dbo database role, in what circumstances might they need any other database role memebrship?The reason I ask this is I was on a course that asked us to add a new user to a database and assign them to dbo AND securityadmin? Surely if they are dbo they inherit the securityadmin permissions?
I assume that you are referring to the db_securityadmin role not to be confused with the fixed server role securityadmin?
The db_owner role is a fixed database role and securityadmin role is a fixed server role.
Members of the securityadmin fixed server role can grant both server-level and database-level permissions.
Members of the db_owner role Fixed Database Role do not inherit Fixed Server Roles.
Members of the securityadmin fixed server role can grant both server-level and database-level permissions.
Members of the db_owner role has complete control of the Database and inherits permissions granted by the db_securityadmin role.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 13, 2011 at 2:34 am
Thanks for the response which clarified a few thing for me. However, the course specifically said to add the user to the fixed database roles of db_owner AND db_securityadmin (not the fixed server role)?? This doesn't make sense?
Regards
Steve
June 13, 2011 at 8:18 am
Steve Hindle-535638 (6/13/2011)
the course specifically said to add the user to the fixed database roles of db_owner AND db_securityadmin
If you add the User to the fixed database roles db_owner they will be able to perform the tasks of a db_securityadmin.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 13, 2011 at 8:22 am
Thanks - that's what I thought. The course was a bit misleading here then.
June 13, 2011 at 9:28 am
Steve Hindle-535638 (6/7/2011)
If a user is a member of the dbo database role...
I just want to clarify one other point for you Steve: dbo is not a Database Role and should not be used interchangeably with db_owner when discussing SQL Server security. dbo stands for "database owner" and is different from "db_owner". That one is a common "trick" test or interview question because so many folks do not make a distinction. They are completely different entities. dbo is the owner of the database and describes a server principal. Members of the sysadmin Fixed Server Role are authenticated into a database under the context of being the dbo, but at the end of the day there can only be one owner of a given database.
db_owner is different from dbo in that it is a Fixed Database Role, not a server principal. The dbo is automatically included in the db_owner role and TMK cannot be removed. In addition to dbo you can add any other Database Roles or Users you would like as members of the db_owner role, but the fact remains that there can be only one explicit dbo (a.k.a. "database owner").
IMHO sa is a good candidate to own all databases on an instance regardless of whether the login is enabled.
SELECT d.name AS database_name,
sp.name AS dbo
FROM sys.databases d
LEFT JOIN sys.server_principals sp ON d.owner_sid = sp.sid ;
Note that a NULL dbo in the query above means the database could be owned by an Active Directory account that does not resolve to a row shown by sys.server_principals.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 13, 2011 at 9:41 am
Apologies - my first post should have said db_owner (fixed database role) and not dbo (user). If a user DOMAIN\steve is a member of the db_owner database role then they will be able to perform anything the db_securityadmin database role can anyway.
I have also seen this on some of the databases that 3rd parties have installed. For example they will have a user as db_owner and db_datareader + db_datawriter which is unnecessary.
June 13, 2011 at 10:36 am
Steve Hindle-535638 (6/13/2011)
Apologies - my first post should have said db_owner (fixed database role) and not dbo (user). If a user DOMAIN\steve is a member of the db_owner database role then they will be able to perform anything the db_securityadmin database role can anyway.
I agree with that assertion.
From your original post:
The reason I ask this is I was on a course that asked us to add a new user to a database and assign them to dbo AND securityadmin? Surely if they are dbo they inherit the securityadmin permissions?
Are you sure they were not asking you to add the Server Login to the securityadmin Fixed Server Role as well as to either 1) make the login the dbo of a database or 2) simply grant the login access to a specific database and it into the db_owner role?
That request would make a bit more sense than simply adding a Database User to both the db_owner Role and the db_securityadmin Fixed Database Roles. I say this because while a member of a db_owner role has permissions to manage Database Roles and Database Users they will have no permissions to manage Server Logins or Passwords. Having membership in the securityadmin Fixed Server Role would allow them to add a new login to the server as well as grant them easy access to manage permissions for the new Database User in the specific database where they were also now db_owner.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 13, 2011 at 10:55 am
What is the exact requirement for your homework assignment?
I would take a close look at it and assign the appropriate permissions wheter it be at the database or server level.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 14, 2011 at 1:52 am
This came up on a Microsoft course I recently attended - 6234a Implementing and Maintaining Microsoft SQL Server 2008 Analysis Services.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply