database access

Question

Post reply

database access

HBK_4700

SSCrazy Eights

Points: 8932
More actions
October 6, 2010 at 8:14 am

#148922

Hi,
In our development server, I want to give only read and execute permissions to developers. I was checking below two database roles. But I don't want my developers to change the data in tables. Please advice on giving access(read and execute)
db_datawriter
Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.
db_datareader
Members of the db_datareader fixed database role can read all data from all user tables.

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

If all your stored procedures are in the same schema, just grant execute on the schema.

John

Derrick Smith SSChampion Points: 10833 More actions · Answer 2

I grant permission on the SP itself when it is created. Being granular is usually the better, more secure approach.

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 3

laddu4700 (10/6/2010)
Hi,
In our development server, I want to give only read and execute permissions to developers. I was checking below two database roles. But I don't want my developers to change the data in tables. Please advice on giving access(read and execute)
db_datawriter
Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.
db_datareader
Members of the db_datareader fixed database role can read all data from all user tables.

Hi

What about [GRANT,REVOKE,DENY ] SELECT, INSERT, UPDATE, DELETE ON DATABASE::<database name> TO <principal>

Thanks

Parthi

Thanks
Parthi

456789psw SSCarpal Tunnel Points: 4728 More actions · Answer 4

here is one approach....

give them reader access

then use the following script to give them exec SP right to all non system related SP and grant view...

http://www.mssqltips.com/tip.asp?tip=1203

---good luck

USE

GO

/****** Object: StoredProcedure [dbo].[spGrantExectoAllStoredProcs] Script Date: 07/30/2009 15:46:11 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[spGrantExectoAllStoredProcs] @user sysname

AS

/*----------------------------------------------------------------------------

-- Object Name: spGrantExectoAllStoredProcs

-- Author: Edgewood Solutions

-- Development Date: 03.19.2007

-- Called By: TBD

-- Description: Issue GRANT EXEC statement for all stored procedures

-- based on the user name that is passed in to this stored procedure

-- Project: SQL Server Security

-- Database: User defined databases

-- Business Process: SQL Server Security

--

----------------------------------------------------------------------------

-- Num | CRF ID | Date Modified | Developer | Description

----------------------------------------------------------------------------

-- 001 | N\A | 03.15.2007 | Edgewood | Original code for the GRANT

-- EXEC process

--

*/

SET NOCOUNT ON

-- 1 - Variable declarations

DECLARE @CMD1 varchar(8000)

DECLARE @CMD2 varchar(8000)

DECLARE @MAXOID int

DECLARE @OwnerName varchar(128)

DECLARE @ObjectName varchar(128)

-- 2 - Create temporary table

CREATE TABLE #StoredProcedures

(OID int IDENTITY (1,1),

StoredProcOwner varchar(128) NOT NULL,

StoredProcName varchar(128) NOT NULL)

-- 3 - Populate temporary table

INSERT INTO #StoredProcedures (StoredProcOwner, StoredProcName)

SELECT ROUTINE_SCHEMA, ROUTINE_NAME

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_NAME NOT LIKE 'dt_%' and ROUTINE_NAME NOT LIKE 'sp_%'

AND ROUTINE_TYPE = 'PROCEDURE' order by routine_name

-- 4 - Capture the @MAXOID value

SELECT @MAXOID = MAX(OID) FROM #StoredProcedures

-- 5 - WHILE loop

WHILE @MAXOID > 0

BEGIN

-- 6 - Initialize the variables

SELECT @OwnerName = StoredProcOwner,

@ObjectName = StoredProcName

FROM #StoredProcedures

WHERE OID = @MAXOID

-- 7 - Build the string

SELECT @CMD1 = 'GRANT VIEW DEFINITION ON ' + '[' + @OwnerName + ']' + '.' + '[' + @ObjectName + ']' + ' TO ' + @user

SELECT @CMD2 = 'GRANT EXEC ON ' + '[' + @OwnerName + ']' + '.' + '[' + @ObjectName + ']' + ' TO ' + @user

--GRANT VIEW DEFINITION ON [dbo].[BasicSearchInsideDatabook] TO [KNOVEL\Engineering]

-- 8 - Execute the string

-- SELECT @CMD1

EXEC(@CMD1)

EXEC(@CMD2)

-- 9 - Decrement @MAXOID

SET @MAXOID = @MAXOID - 1

END

-- 10 - Drop the temporary table

DROP TABLE #StoredProcedures

SET NOCOUNT OFF