sql-server


T-SQL How to grant role to user with ADMIN/GRANT option in SQL Server 2008


After creating roles and granting privileges to them, I want to grant the privileges of a specified role to a user with grant option so that the user can grant privileges to others, how to do with it?
GRANT INSERT ON someTable TO rolem_has_some_permissions WITH GRANT OPTION
GO
EXEC sp_addrolemember 'rolem_has_some_permissions', 'sabertazimi'
GO
Doing like this can't make 'sabertazimi' to grant his privileges to others.
The stored procedure sp_addrolemember has 2(two) parameters only:
sp_addrolemember [ #rolename = ] 'role', [ #membername = ] 'security_account'
WITH GRANT OPTION has nothing to do with sp_addrolemember, instead it is the option of GRANT.
edited:
If you want your role members to be able to grant their permissions to others, you should grant these permissions to the role with the grant option.
When the user has to grant a permission to some other user, he must use the additional AS clause like this:
grant select on myTbl to AnotherUser as MyRole

Related Links

Entity Framework: Max. number of “subqueries”?
SQL Server 2012 Filtered Index - Is the Where clause too complex
SQL Server management in VS2012
Msg 137, Level 15, State 2, Line 29 Must declare the scalar variable “#ACTIVE_STATUS”
How to Select Random Rows in a data table of C#?
sp_getapplock to synchronize concurrent access to in-memory tables
How can I use sys.sp_MSforeachtable with different schema?
T-SQL First_Value() - How can it return more than a single value?
Best practice on how to store geography
SQL Server Database Trigger Update different table on update
Column Store Indexes Update With table locking
How do I know a query plan is invalidated?
how to reset SQL Server sa password
sql server table schema export in excel file automate
Derived Column condition to get string between two Hyphens
What data type should be used for storing frequencies in SQL Server?

Categories

HOME
sql-server-2008
makefile
jenkins-plugins
answer-set-programming
shopify
webrtc
playframework
hana
tomcat7
apollo
apache2
vlc
snap.svg
browserify
unity2d
gatsby
phoenix
reduction
session-timeout
zend-framework-mvc
google-api-java-client
device-detection
kadanes-algorithm
piwik
npm-install
philips-hue
croppic
interrupt-handling
system.data.sqlite
data-conversion
google-api-dotnet-client
forum
dd-wrt
nsurlconnection
phpspreadsheet
geomesa
oscommerce
mmdrawercontroller
office365connectors
strstr
linq-to-entities
restful-url
wsadmin
bing-translator-api
html-agility-pack
python-idle
dojox.grid.datagrid
nsjsonserialization
range-v3
tuleap
spell-checking
greenhills
mbaas
forerunnerdb
photon-controller
mcrypt
info
emoticons
keycode
icefaces
git-annex
webkit2
nofollow
kendo-combobox
search-regex
git-rebase
schtasks.exe
insert-into
sourcegear-vault
matcaffe
js-cookie
futuretask
on-duplicate-key
bridge
robocode
google-earth-plugin
ctrlp
libsndfile
qtestlib
errorprovider
eclipse-classpath
kraken.js
hamsterdb
bignum
image-zoom
ruboto
fraud-prevention
prng
trailing-slash
enumerators
static-variables
caliper
subtract
nintendo
fileutils
scraperwiki
curljs
.app
j-interop
quick-search
unreachable-code
openvg
mvvm-foundation
hmacsha1

Resources

Mobile Apps Dev
Database Users
javascript
java
csharp
php
android
MS Developer
developer works
python
ios
c
html
jquery
RDBMS discuss
Cloud Virtualization
Database Dev&Adm
javascript
java
csharp
php
python
android
jquery
ruby
ios
html
Mobile App
Mobile App
Mobile App