November 8, 2017 at 11:06 pm
Hi All,
I am having confusion and really don't know the purpose / Use of below pre-defined schema in SQL Server
db_accessadmin
db_backupoperator
db_datareader
db_datawriter
db_ddladmin
db_denydatareader
db_denydatawriter
db_owner
db_securityadmin
can anyone explain or give any scenario when these may be require. Thanks in advance !
-----------------
Aditya Rathour
SQL DBA
Not Everything that is faced can be changed,
but nothing can be changed until it is faced.
November 9, 2017 at 6:22 am
Those are first fixed database roles for security. It just happens that roles are also exposed as schemas. Don't think of them as that. Instead, these are security levels that you can easily apply to users, roles and schemas within your database. For example, db_datareader makes it so that any user or role assigned to that fixed database role can read all tables within the database. It's a short hand for security. Read more about them here.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 9, 2017 at 7:36 am
November 9, 2017 at 8:28 am
Arsh - Thursday, November 9, 2017 7:36 AMIn other words...Roles are named set of privileges that can be assigned to other entities like users, schemas , logins..
They go in combination as well .. I suppose ,to grant read-only one has to grant 'db_datareader + db_denydatawriter' .
In a perfect world (or at least my vision of it), the only thing that actually needs to be granted are CONNECT and EXECUTE.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 9, 2017 at 8:50 am
Arsh - Thursday, November 9, 2017 7:36 AMIn other words...Roles are named set of privileges that can be assigned to other entities like users, schemas , logins..
They go in combination as well .. I suppose ,to grant read-only one has to grant 'db_datareader + db_denydatawriter' .
I thought that if someone has no other permissions at the instance level, then they would only need to be a member of db_datareader to have read-only permission ofr a database? wouldn't db_denydatawriter only be required if they had some advanced instance level permissions?
November 9, 2017 at 9:29 pm
Thanks all for your replies ! But my question is regarding schemas , as we can see in every database there are some pr-defined roles and schemas with same name.
Lets take an example if you see in roles and schema under database you will find db_owner in both. I know the use of db_owner role but the use of db_owner schema is not clear.
-----------------
Aditya Rathour
SQL DBA
Not Everything that is faced can be changed,
but nothing can be changed until it is faced.
November 9, 2017 at 9:40 pm
db_owner is a ROLE, not a schema.
https://technet.microsoft.com/en-us/library/ms180977(v=sql.90).aspx
The default SCHEMA is dbo.
November 9, 2017 at 10:00 pm
pietlinden - Thursday, November 9, 2017 9:40 PMdb_owner is a ROLE, not a schema.
https://technet.microsoft.com/en-us/library/ms180977(v=sql.90).aspxThe default SCHEMA is dbo.
if db_owner is only role then why its showing under schema also , please check attached screenshot..
-----------------
Aditya Rathour
SQL DBA
Not Everything that is faced can be changed,
but nothing can be changed until it is faced.
November 10, 2017 at 6:20 am
When you create a principal, a login, group or role, you also get a schema with that principal. These roles have schemas. That's what's going on. They are roles first, schemas second. Don't treat them as regular schemas for ownership, etc.. It'll just make things messy. Treat them as roles only. After that, they're just roles.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 10, 2017 at 11:07 am
I think these schemas are historical artifacts. These security roles were part of SQL back at least to version 6.0. To be a role, they also had to be defined as users. When SQL 2005 came out with separation of users and schemas, when migrating from earlier versions all users had to also be defined as schemas in case they owned any objects. So the security role schemas were born.
These security role schemas are still there for backward compatibility. Maybe someone, somewhere, has decided they need a table named db_ddladmin.ReallyImportantTable. That would be a stupid thing to do, but you can't rule it out. Now these schemas have to be kept around so a version upgrade doesn't break somebody's database.
November 10, 2017 at 10:55 pm
Scott Coleman - Friday, November 10, 2017 11:07 AMI think these schemas are historical artifacts. These security roles were part of SQL back at least to version 6.0. To be a role, they also had to be defined as users. When SQL 2005 came out with separation of users and schemas, when migrating from earlier versions all users had to also be defined as schemas in case they owned any objects. So the security role schemas were born.These security role schemas are still there for backward compatibility. Maybe someone, somewhere, has decided they need a table named db_ddladmin.ReallyImportantTable. That would be a stupid thing to do, but you can't rule it out. Now these schemas have to be kept around so a version upgrade doesn't break somebody's database.
Thanks Scott. I also think so , otherwise I don't see any reason of these schemas. I could not find any Microsoft link / Tutorial on these schemas , please share if you have.
-----------------
Aditya Rathour
SQL DBA
Not Everything that is faced can be changed,
but nothing can be changed until it is faced.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply