User Defined Database Role

Question

Post reply

User Defined Database Role

SQLEnthus

SSC Veteran

Points: 266
More actions
January 4, 2010 at 2:23 pm

#214157

I am needed to create a user defined database role with select, update, delete,execute, alter, drop privilges on all objects on all the schemas in one database. What is the best way to achieve it. Using SQL Server 2005 EE.

hello

Viewing 4 posts - 1 through 3 (of 3 total)

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

GRE (Gethyn Ellis) SSCrazy Eights Points: 9602 More actions · Answer 1

I guess the first question would be why do you want to give a user role such high access rights?

Gethyn Elliswww.gethynellis.com

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

I'd create a role and give it those rights on each schema in the database. It's not quite db_owner so I'd avoid that.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Lowell SSC Guru Points: 323518 More actions · Answer 3

Jack Corbett (1/5/2010)
I'd create a role and give it those rights on each schema in the database. It's not quite db_owner so I'd avoid that.

Based on what Jack said, here's how i would create the role, and then add the users to that new role.

does this seem to be correct?

USE [SandBox]

GO

CREATE ROLE [NearlyAdmins]

ALTER AUTHORIZATION ON SCHEMA::[db_ddladmin] TO [NearlyAdmins]

ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [NearlyAdmins]

ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [NearlyAdmins]

GRANT EXECUTE,ALTER TO [NearlyAdmins]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!