Creating Custom database role

Question

Post reply

Creating Custom database role

Daryl AZ

Hall of Fame

Points: 3087
More actions
November 16, 2006 at 9:05 am

#193169

I have created a new database role. I gave the role db_datareader and db_datawriter.
After assigning Logins to the new Role the users did not have read/write access. When I added the users to the Role they were added to the database.security.users but without the permissions.
Am I missing something obvious here (probably).
thanks
Daryl

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Stephen Merkel Mr or Mrs. 500 Points: 518 More actions · Answer 1

You say you assigned logins to the new role. However, I believe only users are assigned to roles.

Have the logins you created been granted dbaccess ( are they actually users in the database )?

Daryl AZ Hall of Fame Points: 3087 More actions · Answer 2

Steps:

create database role, add db_datareader and db_datawriter

Security (not under database)

create new SQL Server Authentication Login Name:'logintest'

Under UserMapping select target_database and created role.

[ok]

In the target_database I can find the new user/login under Security/Users. It correctly has the Custom Role. However when I try to select from a table with the new login I do not have select permissions.

EdVassie SSC Guru Points: 60476 More actions · Answer 3

You need to add your role to db_datareader and db_datawriter. This may not seem the right way to do things, but it works.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

noeld SSC Guru Points: 96590 More actions · Answer 4

You need to grant SELECT/UPDATE/DELETE permissions to the role you just created on the tables you are going to read/write and if that includes "All" then use the shortcut that my friend Ed posted above

* Noel

Daryl AZ Hall of Fame Points: 3087 More actions · Answer 5

I went in to db_datareader and db_datawriter and VIOLA! Got to get out of my box more often. It never occurred to me to add the new role to db_reader/writer.

thanks

Daryl

kjm SSC Rookie Points: 37 More actions · Answer 6

i am a new mssql admin, with an oracle background...i too wanted to create a user-defined role that had db_reader and writer role with some other privileges added to it but discovered it works the other way around, in that the user-defined role had to be a member of the db_reader and writer role...i want to know if it is possible to create a user-defined role that had the same privileges as say db_reader (select from any table) by just granting select any to table to user-defined role...i can not come up with syntax for this...i know this is possible in oracle but seem to be having problems with mssql...or is the mssql way, that you have to grant the user-defined roles to db roles db_writer and reader?? can one not just grant select any table to a user defined role or user??