database roles to be assigned??

Question

database roles to be assigned??

Mani-584606

SSChampion

Points: 11788
More actions
August 18, 2009 at 6:23 pm

#213694

Hi,
I have created a database "Mydb" in SQL Server 2005 and created a login "scott" . This login should has permission to create/alter tables, create/alter views, and create/alter stored procedures in the database.
For this I did the following:
Security->Logins->Scott->properties->user mapping->mydb and given the following database roles:
db_datareader
db_datawriter
db_ddladmin
are the above assigned roles correct? what role should I assign to create/alter stored procedures in Mydb??
thanks

Viewing 10 posts - 1 through 9 (of 9 total)

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

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

I typically do not use the fixed database roles as they tend to be more open than just granting Create Table, Create Procedure, Create View. I'd usually create my own role with appropriate permissions and then assign the user to that role.

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

Mani-584606 SSChampion Points: 11788 More actions · Answer 2

Could you explain me how to create a custom database role to achive the following:

login should has permission to create/alter tables, create/alter views, and create/alter stored procedures in the database.

thanks

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

You would use CREATE ROLE, the use GRANT to assign the necessary permissions, then use sp_addrolemember to add the user to the role.

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

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 4

The role will also need ALTER SCHEMA rights on any schema that the role doesn't own or have CONTROL rights on.

K. Brian Kelley
@kbriankelley

Mani-584606 SSChampion Points: 11788 More actions · Answer 5

Hi,

login should has permission to create/alter tables, create/alter views, and create/alter stored procedures in the database

I did the following steps in SQL Server 2005:

Step 1.

Use Mydb

Go

/* CREATE A NEW ROLE */

CREATE ROLE db_executor

/* GRANT EXECUTE TO THE ROLE */

GRANT EXECUTE TO db_executor

Step 2.

Created a login Scott and mapped that user to Mydb and selected the below database roles:

db_datareader

db_datawriter

db_ddladmin

db_executor

Now the user do not have any problems.

But I want to do the same in SQL Server 2000. I'm not able to the step 1 of above. How to achieve the same in SQL Server 2000?

please advice..

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 6

#1 - Creating the role

Use sp_addrole. The CREATE ROLE T-SQL is new in SQL Server 2005.

#2 - Creating an "executor" role.

SQL Server 2000 doesn't have the same concepts of securables that 2005 and above does. That means you can't just GRANT EXECUTE on the schema (best practice) or database (not recommended) level. You've got to GRANT EXECUTE on each individual stored procedure, like so:

SQL Server Security: The db_executor Role[/url]

K. Brian Kelley
@kbriankelley

Mani-584606 SSChampion Points: 11788 More actions · Answer 7

In SQL 2000, I did the following:

Created a new database Mydb and granted the below rights:

Created a login Scott and mapped that user to Mydb and selected the below database roles:

db_datareader

db_datawriter

db_ddladmin

But when the user Scott installing his application, getting permission issue saying something like execute privileges are not there.

Then I gave the user Scott db_owner permission and everything went well.

If I want give Execute permission on stored procedure on a newly created database, for what procedures I need to grant execute permission instead of giving db_owner?

Mani-584606 SSChampion Points: 11788 More actions · Answer 8

Hi Brain,

I did the following in sql 2000. Please correct me if I'm wrong

db_datareader

db_datawriter

db_ddladmin

To give Execute permission on stored procedure on a newly created database: after creating the database go to

NewDatabase->Right Click->Properties->permissions->check the create table, create view and create SP

please see the attachment

Is that right?

Mani-584606 SSChampion Points: 11788 More actions · Answer 9

I tried by giving above mentioned permissions But it did not work. While installing the application, it will ask for the following details

Connection Type: SQL Authentication

Server Name: ABC

Database name: Test

User: Scott

pswd: XXXX

At this point, I'm getting the below error:

An error occured while setting up the database. The error was Execute Permission was denied on object 'abc_Install_DropFKConstraints', database Test, owner.

Now I went to the database Test and granted db_owner. Now its working.