Monday, March 26, 2012

Grant Permission Statement

Can I use the Grant Statement to grant permissions to all tables in a
database.
I can only get this to work on individual tables?Simply add the user to the db_datareader role:
sp_addrolemember 'db_datareader', 'MyUser'
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:ugLANg1nGHA.2264@.TK2MSFTNGP04.phx.gbl...
Can I use the Grant Statement to grant permissions to all tables in a
database.
I can only get this to work on individual tables?|||Hi
SELECT 'GRANT SELECT ON [' + USER_NAME(uid) + '].[' + name + '] TO ' +
'[MyUser]'
FROM sysobjects
WHERE
type = 'U'
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(USER_NAME(uid)) + '.' +
QUOTENAME(name)), 'IsMSShipped') = 0
Copy-Paste the output into the QA and run it against a database
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:ugLANg1nGHA.2264@.TK2MSFTNGP04.phx.gbl...
> Can I use the Grant Statement to grant permissions to all tables in a
> database.
> I can only get this to work on individual tables?
>
>|||In 2005 you can do:
USE dbname
GRANT SELECT ON DATABASE::dbname TO UsrName
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:ugLANg1nGHA.2264@.TK2MSFTNGP04.phx.gbl...
> Can I use the Grant Statement to grant permissions to all tables in a database.
> I can only get this to work on individual tables?
>
>

No comments:

Post a Comment