grant select to public

I'm NOT the dbo
but I am allowed to create table/views/UDF
But I can't not do this:
grant select on vw_I_created to public
got this error:
SQL Server Database Error: Grantor does not have GRANT permission.
Is this right ?
Your user was not given GRANT permission, which is the ability to grant a
specified permission to other principals.
"klabu" wrote:

> I'm NOT the dbo
> but I am allowed to create table/views/UDF
> But I can't not do this:
> grant select on vw_I_created to public
> got this error:
> SQL Server Database Error: Grantor does not have GRANT permission.
> Is this right ?
>
>
|||Hi,
On specific objects if you have to give grant again to other securiy
accounts you can use or ask the admin for the grant with below option
WITH GRANT OPTION
Thanks
Ajay
"Edgardo Valdez, MCTS / MCITP"
<EdgardoValdezMCTSMCITP@.discussions.microsoft.com> wrote in message
news:B7485BF1-0937-4FE6-97A0-A4486985F37B@.microsoft.com...[vbcol=seagreen]
> Your user was not given GRANT permission, which is the ability to grant a
> specified permission to other principals.
> "klabu" wrote:

grant select to public

I'm NOT the dbo
but I am allowed to create table/views/UDF
But I can't not do this:
grant select on vw_I_created to public
got this error:
SQL Server Database Error: Grantor does not have GRANT permission.
Is this right ?Your user was not given GRANT permission, which is the ability to grant a
specified permission to other principals.
"klabu" wrote:

> I'm NOT the dbo
> but I am allowed to create table/views/UDF
> But I can't not do this:
> grant select on vw_I_created to public
> got this error:
> SQL Server Database Error: Grantor does not have GRANT permission.
> Is this right ?
>
>|||Hi,
On specific objects if you have to give grant again to other securiy
accounts you can use or ask the admin for the grant with below option
WITH GRANT OPTION
Thanks
Ajay
"Edgardo Valdez, MCTS / MCITP"
<EdgardoValdezMCTSMCITP@.discussions.microsoft.com> wrote in message
news:B7485BF1-0937-4FE6-97A0-A4486985F37B@.microsoft.com...[vbcol=seagreen]
> Your user was not given GRANT permission, which is the ability to grant a
> specified permission to other principals.
> "klabu" wrote:
>sql

Grant on all tables

Hi,

is there a way to grants object privileges on
all tables of database to an user?

like this:

grant select, insert, update, delete on <all tables> to usernamedb
go

thanks!!!!

No. You can add user to db_datareader and db_datawriter roles. This will however provide SELECT, INSERT, UPDATE and DELETE permission on tables/ views / table-valued functions etc. See Books Online for more details on the permissions / roles. Also, you should create a group/role and grant permissions to it instead of directly to the user. This is easier to manage and control.

Alternatively, you can write few lines of code that loops through the desired objects and grants necessary permissions on each object using dynamic SQL.

|||

In 2005 you can grant access to a schema's set of objects:

grant select, insert, update, delete on schema::dbo to bob

For example, in the AdventureWorks DB:

use adventureWorks
go
create user bob without login
go
--first prove no accss
execute as user='bob'
go
select * from production.product --will error
go
revert
go
grant select, insert,update, delete on schema::production to bob
go
execute as user='bob'
go
select * from production.product --will work
go
revert
go

Note that this gives access to table valued user-defined functions also...

|||Very cool... thank u

Grant Access Error

SQL Server returns an error when I try go grant privileges to a username
containing '.' (dot). The statement goes like this:
GRANT <privileges> on <table_name> TO gh.om
The error message point to the '.' Is there a workaround?
We use the format "sitename"."username" on quite a lot of
serverconfigurations in our company and it will be quite a job to change al
l
the logon ids.
Thanks for any assistance
/Leif S
--
Systems AnalystUse brackets as delimiters:
GRANT <privileges> on <table_name> TO [gh.om]
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Leif S" <LeifS@.discussions.microsoft.com> wrote in message
news:39A5D87F-9801-4013-87CF-FD6A729AEF47@.microsoft.com...
SQL Server returns an error when I try go grant privileges to a username
containing '.' (dot). The statement goes like this:
GRANT <privileges> on <table_name> TO gh.om
The error message point to the '.' Is there a workaround?
We use the format "sitename"."username" on quite a lot of
serverconfigurations in our company and it will be quite a job to change
all
the logon ids.
Thanks for any assistance
/Leif S
--
Systems Analyst|||Thanks, Tom! Problem solved.
/Leif S.
--
Systems Analyst
"Tom Moreau" wrote:

> Use brackets as delimiters:
> GRANT <privileges> on <table_name> TO [gh.om]
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Leif S" <LeifS@.discussions.microsoft.com> wrote in message
> news:39A5D87F-9801-4013-87CF-FD6A729AEF47@.microsoft.com...
> SQL Server returns an error when I try go grant privileges to a username
> containing '.' (dot). The statement goes like this:
> GRANT <privileges> on <table_name> TO gh.om
> The error message point to the '.' Is there a workaround?
> We use the format "sitename"."username" on quite a lot of
> serverconfigurations in our company and it will be quite a job to change
> all
> the logon ids.
> Thanks for any assistance
> /Leif S
> --
> Systems Analyst
>