An Example of User-Defined Database Roles

To demo the power of a user-defined database role,let's look at a simple example.
Let's say that you have a group of users who need to read all the tables in a database but
should be granted access to update only one table.

Here there is a solution that you can refer.

 

/*
An Example of User-Defined Database Roles
*/
-- The following statements must be run by an administrator to
-- add a login and database user with no explicit permission granted
create login test_user with password=N'pw',
default_database=master,check_expiration=off,check_policy=off
go

use AdventureWorksLT
go
create user test_user for login test_user
go

-- the following statement fails when executed by test_user
-- which has no explicit permission defined in the AdventureWorksLT
execute as user = 'test_user'
go
select SYSTEM_USER
select top 5 *
from SalesLT.Customer

update SalesLT.Customer
set ModifiedDate = GETDATE()
where CustomerID = 5

-- The following statement is run by administrator to:
-- 1) add a new TestDbRole with permission to update
-- 2) grant update permission on the SalesLT.Customer table
-- 3) add the test_user to TestDbRole Role
revert
go
select SYSTEM_USER
go

use AdventureWorksLT
go
-- 1)
create role TestDbRole authorization dbo
-- 2)
grant update on SalesLT.Customer to TestDbRole
grant select on SalesLT.Customer to TestDbRole
-- 3)
exec sp_addrolemember N'TestDbRole',N'test_user'
go

-- The following statement now succeed when executed
-- by the test_user as the role that it
-- was added to has SELECT and UPDATE permission on
-- that table
-- Execute as test_user
execute as user = 'test_user'
go
select SYSTEM_USER
go

select top 5 * from SalesLT.Customer
update SalesLT.Customer
set ModifiedDate = GETDATE()
where CustomerID = 5

-- The following select fails because 'testdbrole' role
-- does not have permission select on any table except
-- SalesLT.Address
select * from SalesLT.Address

-- The following statement is run by an administrator
-- to add the testdbrole database role to the db_datareader
revert
go
select SYSTEM_USER
go

exec sp_addrolemember N'db_datareader',N'test_user'
go

-- Finally,the test_user can update SalesLT.customer table
-- and select from any other table in the database
execute as user = 'test_user'
go
select SYSTEM_USER
go
select * from SalesLT.Address

select * from SalesLT.Product

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/355374/viewspace-626677/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/355374/viewspace-626677/