Custom Database Roles -- Insert

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ExcelMan - 27 May 2007 01:54 GMT

I am trying to set up a Custom Database Role that allows the user to
select, update, insert and delete data. I created the role by
combining the built-in roles of db_datareader and db_datawriter.
However when I logged with the custom role I was not able to insert a
new record.

When I then went in and assigned Insert rights to the table I was
trying to insert to, I was able to do it. But if I go that route I
end up having to set permissions on every table in the database. I
just want to grant these rights to ALL the tables.

Is there a straighforward way to do this and why didn't adding
db_datawriter to the role definition accomplish this?

Thanks.

Steven

Reply to this Message

Hari Prasad - 27 May 2007 03:20 GMT

Hello,

See if there is any Deny permissions set for the user for the specific table
you tried to insert.

Thanks
Hari

>I am trying to set up a Custom Database Role that allows the user to
> select, update, insert and delete data. I created the role by
[quoted text clipped - 13 lines]
>
> Steven

Reply to this Message

Erland Sommarskog - 27 May 2007 11:57 GMT

> I am trying to set up a Custom Database Role that allows the user to
> select, update, insert and delete data. I created the role by
[quoted text clipped - 9 lines]
> Is there a straighforward way to do this and why didn't adding
> db_datawriter to the role definition accomplish this?

As Hari suggested, a DENY permission may be the problem. Here is a
script for SQL 2000 that demonstrates that what you want to do really
works. By the way, if you are using SQL 2005, you are better off granting
access on schema or database level.

USE tempdb
go
CREATE DATABASE rolle
EXEC sp_addlogin rollerull, '§12§'
go
USE rolle
go
CREATE TABLE mulle (a int NOT NULL, x sysname DEFAULT USER)
go
EXEC sp_addrole rolle
EXEC sp_addrolemember 'db_datawriter', 'rolle'
EXEC sp_addrolemember 'db_datareader', 'rolle'

EXEC sp_grantdbaccess rollerull
EXEC sp_addrolemember rolle, rollerull
go
SETUSER 'rollerull'
go
INSERT mulle (a) VALUES (12)
go
SETUSER
go
SELECT * FROM mulle
go
use tempdb
go
DROP DATABASE rolle
EXEC sp_droplogin rollerull

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Reply to this Message