March 25, 2015 at 8:37 am
Hello community!
I have a question concerning the database roles.
Here is my scenario.
I have created a new login that is directly mapped to an Active Directory group. I added the group to the database (DB1) where the group should have access. Now I want to create a new database role in DB1. The role should have read permissions on all tables in the database and write permissions on certain tables.
I open the "New Role" dialog. On the general page I give the name of the role that I want to create.
First question: At "Owner" I have to add the login/database role that owns the new role. The owner can modify the permissions of the role and add new logins to the database role. Is this correct?
On the page "Securables" I select those objects that the user should have access to.
I select all tables with the read permissions and give the Permission "Select-->Grant" to the new role.
For the write permissions, I select the concerned tables and give the "Grant" Permission for "Delete, Insert, Update".
Second question: "With Grant" means, that the users of the role can grant the same permission to other users?
Third question: What is configured on the page "Extended properties"?
Thank you for the information.
Best regards
March 25, 2015 at 9:19 am
This is why I rarely use the GUI for stuff like this - you never know what it's going to do behind the scenes. Use sp_addrole to create the role, sp_addrolemember to add members and GRANT to assign permissions. Then you know exactly what you're getting. Extended properties are for documenting objects at database level - for example you can add an extended property to describe what the role is for, and maybe another to show the work request under which it was created.
John
March 25, 2015 at 9:27 am
As John says, stay away from the UI. It's more confusing than helpful.
Create the role with CREATE ROLE (not sp_addrole, that's an old and mouldy command). You don't want a different owner the dbo.
To give the role read permissions on all tables do on SQL 2012 and later:
ALTER ROLE db_datareader ADD MEMBER ON newrole
On SQL 2008 do,
EXEC sp_addrolemember 'db_datareader', 'newrole'
For granting update permissions to
GRANT INSERT, UPDATE, DELETE ON sometble TO newrole
for all tables. The WITH GRANT OPTION is quite esoteric. This option would permit members in this role to grant the permission to someone else.
Extended properties is something you can use for your internal notes.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
March 25, 2015 at 9:31 am
Hello Erland!
I have to admit that I am only working <1% of my time on administrative tasks within the SQL server. The temptation to use the GUI is to big for someone like me.
Thank you for the answers.
Alex
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply