Permisson on views only

All,
Is that possible to have a role that has admin permission on views, but only
read/write permission on tables?
Thanks!
TinaSure, you can create your own role e.g.
exec sp_addrole 'ViewCreators'
grant create view to ViewCreators
exec sp_addrolemember 'db_datareader','ViewCreators'
exec sp_addrolemember 'db_datawriter','ViewCreators'
You can then just add users to that role.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Tina Ma" <tina.ma@.parsons.com> wrote in message
news:etFp$YyfEHA.4092@.TK2MSFTNGP10.phx.gbl...
> All,
> Is that possible to have a role that has admin permission on views, but
> only
> read/write permission on tables?
> Thanks!
> Tina
>

Permissions with views and tables

We are attempting to implement security on top of a shrink-wrapped software
package and are trying to get row-level security. Here's the scenario:

1. Table dbo.BOOK contains all the information about books in every
department.
2. There are a large number of developed reports that run queries like
"select * from BOOK..."
3. We wish to have each Department only be able to see their books - without
changing the existing reports.

Our thought was to create a series of views:

create view Dept1.BOOK as
select * from BOOK where Dept=1

...

and then create Roles for each Dept. We'd then remove rights to dbo.BOOK
and grant rights to DeptN.BOOK as appropriate for each role. We started
testing this and seemed to get it working, but are now having problems. Is
this possible? Is there another, better solution?

Thanks!Anon (anon email) writes:
> We are attempting to implement security on top of a shrink-wrapped
> software package and are trying to get row-level security. Here's the
> scenario:
> 1. Table dbo.BOOK contains all the information about books in every
> department.
> 2. There are a large number of developed reports that run queries like
> "select * from BOOK..."
> 3. We wish to have each Department only be able to see their books -
> without changing the existing reports.
> Our thought was to create a series of views:
> create view Dept1.BOOK as
> select * from BOOK where Dept=1
> ...
> and then create Roles for each Dept. We'd then remove rights to
> dbo.BOOK and grant rights to DeptN.BOOK as appropriate for each role.
> We started testing this and seemed to get it working, but are now having
> problems. Is this possible? Is there another, better solution?

And the problems you get are?

Whther this will work a lot, depends on your shrink-wrap. After all,
you are doing something for which it is not prepared. Updates would
fail, but you could have INSTEAD OF triggers to cate for that.

In the view definition, I would recommend that you say dbo.BOOK for
clarity.

You should also beware of that this sort of row-level security is not
fool-proof. It is possible to dig out information about data you don't
have access to. Then again, it's not trivial and it does require
expert skills to do it.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Sorry, I didn't clarify the view; it is created using "select * from
dbo.BOOK". However, when a user with rights to Dept1.BOOK but not to
dbo.BOOK attempts to run the query they get an error that states

Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'BOOK', database 'LIBRARY', owner 'dbo'.

What we'd like to see is the explicit rights on the View supercede the
rights on the table, but that doesn't seem to be the case.

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9662F1EE69756Yazorman@.127.0.0.1...
> Anon (anon email) writes:
>> We are attempting to implement security on top of a shrink-wrapped
>> software package and are trying to get row-level security. Here's the
>> scenario:
>>
>> 1. Table dbo.BOOK contains all the information about books in every
>> department.
>> 2. There are a large number of developed reports that run queries like
>> "select * from BOOK..."
>> 3. We wish to have each Department only be able to see their books -
>> without changing the existing reports.
>>
>> Our thought was to create a series of views:
>>
>> create view Dept1.BOOK as
>> select * from BOOK where Dept=1
>>
>> ...
>>
>> and then create Roles for each Dept. We'd then remove rights to
>> dbo.BOOK and grant rights to DeptN.BOOK as appropriate for each role.
>> We started testing this and seemed to get it working, but are now having
>> problems. Is this possible? Is there another, better solution?
> And the problems you get are?
> Whther this will work a lot, depends on your shrink-wrap. After all,
> you are doing something for which it is not prepared. Updates would
> fail, but you could have INSTEAD OF triggers to cate for that.
> In the view definition, I would recommend that you say dbo.BOOK for
> clarity.
> You should also beware of that this sort of row-level security is not
> fool-proof. It is possible to dig out information about data you don't
> have access to. Then again, it's not trivial and it does require
> expert skills to do it.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Anon (anon email) writes:
> Sorry, I didn't clarify the view; it is created using "select * from
> dbo.BOOK". However, when a user with rights to Dept1.BOOK but not to
> dbo.BOOK attempts to run the query they get an error that states
> Server: Msg 229, Level 14, State 5, Line 1
> SELECT permission denied on object 'BOOK', database 'LIBRARY', owner
> 'dbo'.
> What we'd like to see is the explicit rights on the View supercede the
> rights on the table, but that doesn't seem to be the case.

I will have to admit that if you granted Dept1 rights on dbo.Book, and
then the users rights to Dept1.book it would work, but nope. In fact
I even tried creating a stored procedure Dept1.book_sp and grant users
execute rights on that one, but that also failed. However, this latter
arrangeent actually works on SQL 6.5, so at least I did remember
correctly so far. (But Microsoft has changed the rules. Grr!)

Right now, I have to good ideas to get this to work in SQL 2000. In
SQL 2005, it would be another matter, because Dept1 would just be a
schema, that still could be owned by dbo.

Of course, you can create the view as dbo.Dept1books, but I don't
if that meets your ambition to fool the shrink-wrap package.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Unfortunately it seems our quick trial was on a 2005 server, and that
remains in Beta. Sigh. Does anyone have any other ideas on how to
accomplish this?

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9667F329E4C86Yazorman@.127.0.0.1...
> Anon (anon email) writes:
>> Sorry, I didn't clarify the view; it is created using "select * from
>> dbo.BOOK". However, when a user with rights to Dept1.BOOK but not to
>> dbo.BOOK attempts to run the query they get an error that states
>>
>> Server: Msg 229, Level 14, State 5, Line 1
>> SELECT permission denied on object 'BOOK', database 'LIBRARY', owner
>> 'dbo'.
>>
>> What we'd like to see is the explicit rights on the View supercede the
>> rights on the table, but that doesn't seem to be the case.
> I will have to admit that if you granted Dept1 rights on dbo.Book, and
> then the users rights to Dept1.book it would work, but nope. In fact
> I even tried creating a stored procedure Dept1.book_sp and grant users
> execute rights on that one, but that also failed. However, this latter
> arrangeent actually works on SQL 6.5, so at least I did remember
> correctly so far. (But Microsoft has changed the rules. Grr!)
> Right now, I have to good ideas to get this to work in SQL 2000. In
> SQL 2005, it would be another matter, because Dept1 would just be a
> schema, that still could be owned by dbo.
> Of course, you can create the view as dbo.Dept1books, but I don't
> if that meets your ambition to fool the shrink-wrap package.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Anon (anon email) writes:
> Unfortunately it seems our quick trial was on a 2005 server, and that
> remains in Beta. Sigh. Does anyone have any other ideas on how to
> accomplish this?

Maybe you could start to give the full presumptions for your case. You've
presented some scattered some information, from which I was able to make
some guesses. But it does help to know what exact degrees of freedom
you have with your shrink-wrap.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Permissions on Views vs. Tables

I am using an Access ADP (version 2003) as a front-end to a SQL Server 2000
database. Based on what I have read, I should be able to not let anybody see
the tables directly, and work everything through Views and SPs. To do this,
I
grant no permissions at all on the tables, and appropriate SELECT, INSERT,
UPDATE, and DELETE permissions on the views. However, the views in Access ar
e
still coming up as "Recordset not updatabel". Only by granting permissions o
n
the tables do the views become updatable. Worse yet, if I DENY permissions
for UPDATE etc on the views but grant them on the tables, the views are stil
l
updatable.
This seems very backwards. I thought it was supposed to take the permissions
on the View, regarless of the permissions on the table (except for DENY
permissions, of course).
--
ToddYou can specify the VIEW_METADATA option on the CREATE VIEW statement so
that APIs return metadata for the view rather than the underlying tables.
For example:
CREATE VIEW dbo.MyView
WITH VIEW_METADATA
AS
SELECT MyColumn FROM dbo.MyTable
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Todd Chittenden" <ToddChittenden@.discussions.microsoft.com> wrote in
message news:187A1C5F-5CA1-4FA2-825C-859FFCF0FC20@.microsoft.com...
>I am using an Access ADP (version 2003) as a front-end to a SQL Server 2000
> database. Based on what I have read, I should be able to not let anybody
> see
> the tables directly, and work everything through Views and SPs. To do
> this, I
> grant no permissions at all on the tables, and appropriate SELECT, INSERT,
> UPDATE, and DELETE permissions on the views. However, the views in Access
> are
> still coming up as "Recordset not updatabel". Only by granting permissions
> on
> the tables do the views become updatable. Worse yet, if I DENY permissions
> for UPDATE etc on the views but grant them on the tables, the views are
> still
> updatable.
> This seems very backwards. I thought it was supposed to take the
> permissions
> on the View, regarless of the permissions on the table (except for DENY
> permissions, of course).
> --
> Todd

Permissions on Views

I've created two new views along with a new login that I'd like to only have
select permission on these new views. I've created the views and granted
select permission to the new user to these views. The first view, which
invloves only one table, works fine. The second is giving me problems and I
don't quite understand why.
The 2nd view joins 2 tables, which are owned by dbo, with 2 views, which are
owned by another user. I've verified that the view references the correct
owners and I've granted select permission to the user to this view, but I
keep getting select permission denied on the 4 objects being referenced in
the view, unless I grant select permissions to the objects that I want to
restrict.
What am I missing here?
Any help is appreciated
Thanks
Mike
--
MG"MGeles" <michael.geles@.thomson.com> wrote in message
news:79C5BB72-2C87-4D86-87FC-71B4F388AF2F@.microsoft.com...
> I've created two new views along with a new login that I'd like to only
> have
> select permission on these new views. I've created the views and granted
> select permission to the new user to these views. The first view, which
> invloves only one table, works fine. The second is giving me problems and
> I
> don't quite understand why.
> The 2nd view joins 2 tables, which are owned by dbo, with 2 views, which
> are
> owned by another user. I've verified that the view references the correct
> owners and I've granted select permission to the user to this view, but I
> keep getting select permission denied on the 4 objects being referenced in
> the view, unless I grant select permissions to the objects that I want to
> restrict.
> What am I missing here?
>
The ownership chain is broken.
See
Using Ownership Chains
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_4iyb.asp
Davidsql

Permissions on views

Although I have granted select permissions on the views in my database that
are the recordesource for reports in a visual basic application, I cannot
open the reports from the application. I get the VB error 1005 ("can't open
recordset"). It would seem to be a permissions problem because everything
else in the app works fine except the reports that are based on views. I
haven't found anything in Books on Line that has solved the problem. The D
B
was developed in sql 2000 and the compatability level for this DB in sql 200
5
is 80. I'm grateful for any help.Pam
Make sure that you connect with the "right" user from application to run
reports.
Why do you have 80 compatibilty level for SQL Server 2005?
"Pam Davey" <PamDavey@.discussions.microsoft.com> wrote in message
news:16199EB3-EC0B-4A5E-B68F-1EEF73DDDF60@.microsoft.com...
> Although I have granted select permissions on the views in my database
> that
> are the recordesource for reports in a visual basic application, I cannot
> open the reports from the application. I get the VB error 1005 ("can't
> open
> recordset"). It would seem to be a permissions problem because everything
> else in the app works fine except the reports that are based on views. I
> haven't found anything in Books on Line that has solved the problem. The
> DB
> was developed in sql 2000 and the compatability level for this DB in sql
> 2005
> is 80. I'm grateful for any help.|||Hi!
Create view with view_metadata attribute.
Micle.
"Pam Davey" <PamDavey@.discussions.microsoft.com> wrote in message
news:16199EB3-EC0B-4A5E-B68F-1EEF73DDDF60@.microsoft.com...
> Although I have granted select permissions on the views in my database
> that
> are the recordesource for reports in a visual basic application, I cannot
> open the reports from the application. I get the VB error 1005 ("can't
> open
> recordset"). It would seem to be a permissions problem because everything
> else in the app works fine except the reports that are based on views. I
> haven't found anything in Books on Line that has solved the problem. The
> DB
> was developed in sql 2000 and the compatability level for this DB in sql
> 2005
> is 80. I'm grateful for any help.|||Hi Uri-
Thank you. I am connecting with the correct user. I have the database of
interset that resides on SQL Server 2005 set to compatability level 80 so
that it will have backward compatability with SQL server 2000 on which it wa
s
developed.
"Uri Dimant" wrote:

> Pam
> Make sure that you connect with the "right" user from application to run
> reports.
> Why do you have 80 compatibilty level for SQL Server 2005?
>
> "Pam Davey" <PamDavey@.discussions.microsoft.com> wrote in message
> news:16199EB3-EC0B-4A5E-B68F-1EEF73DDDF60@.microsoft.com...
>
>|||Hi Micle-
Thank you for your input. Unfortunely, it didn't seem to make any
difference. I get the same error. Oddly, I can get to all the underlying
tables that make up the view. I just can't get to the view, even when it's
created as you suggested.
"Micle" wrote:

> Hi!
> Create view with view_metadata attribute.
> Micle.
>
> "Pam Davey" <PamDavey@.discussions.microsoft.com> wrote in message
> news:16199EB3-EC0B-4A5E-B68F-1EEF73DDDF60@.microsoft.com...
>
>|||Pam Davey (PamDavey@.discussions.microsoft.com) writes:
> Although I have granted select permissions on the views in my database
> that are the recordesource for reports in a visual basic application, I
> cannot open the reports from the application. I get the VB error 1005
> ("can't open recordset"). It would seem to be a permissions problem
> because everything else in the app works fine except the reports that
> are based on views. I haven't found anything in Books on Line that has
> solved the problem. The DB was developed in sql 2000 and the
> compatability level for this DB in sql 2005 is 80. I'm grateful for any
> help.
Do the reports work when you run it on SQL 2000? Hav you verified that the
queries work when you run them from Query Analyzer or Management Studio.
Could you post the code you are using?
I would not expect a permissons problem, unless you are doing a poor job
of handling errors from SQL server. Nevertheless, here is a kind of shot
in the dark that you can try:
GRANT VIEW DEFINITION ON SCHEMA::dbo TO <user>
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for your help Erland. Yes, the queries work in SQL 2000 as well as
when run from the Query Analyzer and Management Studio.
There's very little code actually. I have a 3rd party report control from
Component One that renders reports from report definitions stored in an xml
file. I've checked the xml file and the various report definitions within th
e
file have the correct queries named as their recordsource.
Thanks for the "GRANT..." thought. Didn't make a difference though. Still, I
appreciate your help.
"Erland Sommarskog" wrote:

> Pam Davey (PamDavey@.discussions.microsoft.com) writes:
> Do the reports work when you run it on SQL 2000? Hav you verified that the
> queries work when you run them from Query Analyzer or Management Studio.
> Could you post the code you are using?
> I would not expect a permissons problem, unless you are doing a poor job
> of handling errors from SQL server. Nevertheless, here is a kind of shot
> in the dark that you can try:
> GRANT VIEW DEFINITION ON SCHEMA::dbo TO <user>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Pam Davey (PamDavey@.discussions.microsoft.com) writes:
> Thanks for your help Erland. Yes, the queries work in SQL 2000 as well as
> when run from the Query Analyzer and Management Studio.
> There's very little code actually. I have a 3rd party report control
> from Component One that renders reports from report definitions stored
> in an xml file. I've checked the xml file and the various report
> definitions within the file have the correct queries named as their
> recordsource.
> Thanks for the "GRANT..." thought. Didn't make a difference though.
> Still, I appreciate your help.
I'm afraid that there is very little to work on. Maybe the best is to
contact the vendor.
All I can really suggest is to use Profiler to eavesdrop on what the
report tool sends to SQL Server. You can include Error events in
the trace, so you can see if any errors are reported.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Okay, I'll give it a try. Thank you again.
"Erland Sommarskog" wrote:

> Pam Davey (PamDavey@.discussions.microsoft.com) writes:
> I'm afraid that there is very little to work on. Maybe the best is to
> contact the vendor.
> All I can really suggest is to use Profiler to eavesdrop on what the
> report tool sends to SQL Server. You can include Error events in
> the trace, so you can see if any errors are reported.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||"Pam Davey" <PamDavey@.discussions.microsoft.com> wrote in message
news:DEB58306-AC92-45FA-A657-A08EA6CD6D2A@.microsoft.com...[vbcol=seagreen]
> Okay, I'll give it a try. Thank you again.
> "Erland Sommarskog" wrote:
>
A good reality check would be to execute the application under an account
that's an admin (as in NT account that is a member of the sysadmin fixed
server role) on the SQL box. If it still doesn't work, the reason is not
permissions! If it does work under admin privileges, the next reality check
might be to grant all to guest; if that works revoke the last grant and
grant all to public.
Another tactic would be to write a quickie script to open a recordset on
that view, and execute it from an end-user-level login context -- that will
allow you to see any error output that the report control may be eating.
Divide and conquer, that's the game. :-)
Good Luck,
Mark
[vbcol=seagreen]

Permissions on Views

I've created two new views along with a new login that I'd like to only have
select permission on these new views. I've created the views and granted
select permission to the new user to these views. The first view, which
invloves only one table, works fine. The second is giving me problems and I
don't quite understand why.
The 2nd view joins 2 tables, which are owned by dbo, with 2 views, which are
owned by another user. I've verified that the view references the correct
owners and I've granted select permission to the user to this view, but I
keep getting select permission denied on the 4 objects being referenced in
the view, unless I grant select permissions to the objects that I want to
restrict.
What am I missing here?
Any help is appreciated
Thanks
Mike
MG
"MGeles" <michael.geles@.thomson.com> wrote in message
news:79C5BB72-2C87-4D86-87FC-71B4F388AF2F@.microsoft.com...
> I've created two new views along with a new login that I'd like to only
> have
> select permission on these new views. I've created the views and granted
> select permission to the new user to these views. The first view, which
> invloves only one table, works fine. The second is giving me problems and
> I
> don't quite understand why.
> The 2nd view joins 2 tables, which are owned by dbo, with 2 views, which
> are
> owned by another user. I've verified that the view references the correct
> owners and I've granted select permission to the user to this view, but I
> keep getting select permission denied on the 4 objects being referenced in
> the view, unless I grant select permissions to the objects that I want to
> restrict.
> What am I missing here?
>
The ownership chain is broken.
See
Using Ownership Chains
http://msdn.microsoft.com/library/de...urity_4iyb.asp
David

Permissions on Views

I've created two new views along with a new login that I'd like to only have
select permission on these new views. I've created the views and granted
select permission to the new user to these views. The first view, which
invloves only one table, works fine. The second is giving me problems and I
don't quite understand why.
The 2nd view joins 2 tables, which are owned by dbo, with 2 views, which are
owned by another user. I've verified that the view references the correct
owners and I've granted select permission to the user to this view, but I
keep getting select permission denied on the 4 objects being referenced in
the view, unless I grant select permissions to the objects that I want to
restrict.
What am I missing here?
Any help is appreciated
Thanks
Mike
--
MG"MGeles" <michael.geles@.thomson.com> wrote in message
news:79C5BB72-2C87-4D86-87FC-71B4F388AF2F@.microsoft.com...
> I've created two new views along with a new login that I'd like to only
> have
> select permission on these new views. I've created the views and granted
> select permission to the new user to these views. The first view, which
> invloves only one table, works fine. The second is giving me problems and
> I
> don't quite understand why.
> The 2nd view joins 2 tables, which are owned by dbo, with 2 views, which
> are
> owned by another user. I've verified that the view references the correct
> owners and I've granted select permission to the user to this view, but I
> keep getting select permission denied on the 4 objects being referenced in
> the view, unless I grant select permissions to the objects that I want to
> restrict.
> What am I missing here?
>
The ownership chain is broken.
See
Using Ownership Chains
http://msdn.microsoft.com/library/d...>
ity_4iyb.asp
David

Permissions for creating views

In database permissions I have granted a user rights to create a view. When she tries to save the view (even save as) it automatically wants to save it to the dbo schema and says she does not have rights to save to the dbo schema.

Two questions:

1) Can I set it up to where she can save a view to a schema which she is the owner?

2) If not, then what permissions must be set to allow her to create / save views but not be able to create, etc. other objects such as tables and stored procedures?

From the description it seems like this is a question regarding tools (most likely Management studio).

I have moved the question to the appropriate forum, but if you have additional questions regarding the permissions please let us know.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||Moved to Security|||

You will need to grant alter on the schema and create view on the database to the user who wants to create views.

T-SQL:

grant create view to user1

grant alter on schema:bo to user1

go

HTH,

-Steven Gott

SDE/T

SQL Server

Permissions

Hi
How to solve next problem
a) I have database TEST
b) Login and user - webguest
Now i need set SELECT permission to all tables and views and INSERT,UPDATE,
DELETE perimissions
on couple tables.
I have tried management studio but no luck )
Regards;
Red
Hi
Is 'webguest' member of db_owner database role? Add him to this role
"Redivivus" <meelis.lilbok@.deltmar.ee> wrote in message
news:uGnVUsEdHHA.3632@.TK2MSFTNGP02.phx.gbl...
> Hi
> How to solve next problem
> a) I have database TEST
> b) Login and user - webguest
> Now i need set SELECT permission to all tables and views and
> INSERT,UPDATE, DELETE perimissions
> on couple tables.
> I have tried management studio but no luck )
>
> Regards;
> Red
>
|||yes webguest is db_owner
but setting all permissions manually is veeeery hard there are over 100
tables in db.
how to set GRANT SELECT all tables and DENY UPDATE,INSERT,DELETE to all
tables
and then manually set GRANT INSERT, UPDATE, DELETE to specific tables
Red.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:elI30eFdHHA.4188@.TK2MSFTNGP02.phx.gbl...
> Hi
> Is 'webguest' member of db_owner database role? Add him to this role
> "Redivivus" <meelis.lilbok@.deltmar.ee> wrote in message
> news:uGnVUsEdHHA.3632@.TK2MSFTNGP02.phx.gbl...
>
|||You can run the following select statement within the database you wish to
grant the permission to - then execute the resulting code.
select 'grant select,insert,update,delete on ' + name + ' to webguest' +
char(13) + ';' from sysobjects where xtype in( 'U','V')
Thanks,
Scott H.
"Redivivus" wrote:

> Hi
> How to solve next problem
> a) I have database TEST
> b) Login and user - webguest
> Now i need set SELECT permission to all tables and views and INSERT,UPDATE,
> DELETE perimissions
> on couple tables.
> I have tried management studio but no luck )
>
> Regards;
> Red
>
>

permissions

Hi All,
how I can setup a role, so that they can only create, drop stored procedure,
views and functions. and can't create triggers, tables or modify schema.
Tim
grant create procedure, create view, create function to <login>
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"t" <t@.t.t> wrote in message news:ehIWCmpaEHA.4048@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> how I can setup a role, so that they can only create, drop stored
procedure,
> views and functions. and can't create triggers, tables or modify schema.
>
> Tim
>
>

permissions

Hi All,
how I can setup a role, so that they can only create, drop stored procedure,
views and functions. and can't create triggers, tables or modify schema.
Tim
Hi,
You can only assign CREATE PROC, CREATE FUNCTION, CREATE VIEW to a
USER/ROLE. The DROP
permissons for any of this objects is not grantable.
Thanks
Hari
MCDBA
"t" <t@.t.t> wrote in message news:eXYPwlpaEHA.384@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> how I can setup a role, so that they can only create, drop stored
procedure,
> views and functions. and can't create triggers, tables or modify schema.
>
> Tim
>
>

permissions

Hi All,
how I can setup a role, so that they can only create, drop stored procedure,
views and functions. and can't create triggers, tables or modify schema.
Timgrant create procedure, create view, create function to <login>
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"t" <t@.t.t> wrote in message news:ehIWCmpaEHA.4048@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> how I can setup a role, so that they can only create, drop stored
procedure,
> views and functions. and can't create triggers, tables or modify schema.
>
> Tim
>
>

Permissions

Hi
How to solve next problem
a) I have database TEST
b) Login and user - webguest
Now i need set SELECT permission to all tables and views and INSERT,UPDATE,
DELETE perimissions
on couple tables.
I have tried management studio but no luck )
Regards;
RedHi
Is 'webguest' member of db_owner database role? Add him to this role
"Redivivus" <meelis.lilbok@.deltmar.ee> wrote in message
news:uGnVUsEdHHA.3632@.TK2MSFTNGP02.phx.gbl...
> Hi
> How to solve next problem
> a) I have database TEST
> b) Login and user - webguest
> Now i need set SELECT permission to all tables and views and
> INSERT,UPDATE, DELETE perimissions
> on couple tables.
> I have tried management studio but no luck )
>
> Regards;
> Red
>|||yes webguest is db_owner
but setting all permissions manually is veeeery hard there are over 100
tables in db.
how to set GRANT SELECT all tables and DENY UPDATE,INSERT,DELETE to all
tables
and then manually set GRANT INSERT, UPDATE, DELETE to specific tables
Red.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:elI30eFdHHA.4188@.TK2MSFTNGP02.phx.gbl...
> Hi
> Is 'webguest' member of db_owner database role? Add him to this role
> "Redivivus" <meelis.lilbok@.deltmar.ee> wrote in message
> news:uGnVUsEdHHA.3632@.TK2MSFTNGP02.phx.gbl...
>|||You can run the following select statement within the database you wish to
grant the permission to - then execute the resulting code.
select 'grant select,insert,update,delete on ' + name + ' to webguest' +
char(13) + ';' from sysobjects where xtype in( 'U','V')
--
Thanks,
Scott H.
"Redivivus" wrote:

> Hi
> How to solve next problem
> a) I have database TEST
> b) Login and user - webguest
> Now i need set SELECT permission to all tables and views and INSERT,UPDATE
,
> DELETE perimissions
> on couple tables.
> I have tried management studio but no luck )
>
> Regards;
> Red
>
>

Permissions

Hi
How to solve next problem
a) I have database TEST
b) Login and user - webguest
Now i need set SELECT permission to all tables and views and INSERT,UPDATE,
DELETE perimissions
on couple tables.
I have tried management studio but no luck :))
Regards;
RedHi
Is 'webguest' member of db_owner database role? Add him to this role
"Redivivus" <meelis.lilbok@.deltmar.ee> wrote in message
news:uGnVUsEdHHA.3632@.TK2MSFTNGP02.phx.gbl...
> Hi
> How to solve next problem
> a) I have database TEST
> b) Login and user - webguest
> Now i need set SELECT permission to all tables and views and
> INSERT,UPDATE, DELETE perimissions
> on couple tables.
> I have tried management studio but no luck :))
>
> Regards;
> Red
>|||yes webguest is db_owner
but setting all permissions manually is veeeery hard there are over 100
tables in db.
how to set GRANT SELECT all tables and DENY UPDATE,INSERT,DELETE to all
tables
and then manually set GRANT INSERT, UPDATE, DELETE to specific tables
Red.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:elI30eFdHHA.4188@.TK2MSFTNGP02.phx.gbl...
> Hi
> Is 'webguest' member of db_owner database role? Add him to this role
> "Redivivus" <meelis.lilbok@.deltmar.ee> wrote in message
> news:uGnVUsEdHHA.3632@.TK2MSFTNGP02.phx.gbl...
> > Hi
> >
> > How to solve next problem
> >
> > a) I have database TEST
> > b) Login and user - webguest
> > Now i need set SELECT permission to all tables and views and
> > INSERT,UPDATE, DELETE perimissions
> > on couple tables.
> >
> > I have tried management studio but no luck :))
> >
> >
> > Regards;
> > Red
> >
>|||You can run the following select statement within the database you wish to
grant the permission to - then execute the resulting code.
select 'grant select,insert,update,delete on ' + name + ' to webguest' +
char(13) + ';' from sysobjects where xtype in( 'U','V')
--
Thanks,
Scott H.
"Redivivus" wrote:
> Hi
> How to solve next problem
> a) I have database TEST
> b) Login and user - webguest
> Now i need set SELECT permission to all tables and views and INSERT,UPDATE,
> DELETE perimissions
> on couple tables.
> I have tried management studio but no luck :))
>
> Regards;
> Red
>
>

permissions

Hi All,
how I can setup a role, so that they can only create, drop stored procedure,
views and functions. and can't create triggers, tables or modify schema.
Timgrant create procedure, create view, create function to <login>
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"t" <t@.t.t> wrote in message news:ehIWCmpaEHA.4048@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> how I can setup a role, so that they can only create, drop stored
procedure,
> views and functions. and can't create triggers, tables or modify schema.
>
> Tim
>
>

Permission settings for views to be created

Hi,
I'm a beginner when it comes to SQL Server 2005 and user permissions.
I'm wondering, how do I set the default rights for views yet to be
created? I've only find a way to configure the already existing views.
Br,
Tom...What I need is to have a user that can only access the views in a
database (meaning access the data in tables through views).
Tom wrote:
> Hi,
> I'm a beginner when it comes to SQL Server 2005 and user permissions.
> I'm wondering, how do I set the default rights for views yet to be
> created? I've only find a way to configure the already existing views.
> Br,
> Tom|||Tom
GRANT SELECT on SCHENA ...... GRANT SELECT on VIEW .... . (See details
in the BOL) .
Do the users have SSMS installed on their workstations?
"Tom" <tom.kostiainen@.saunalahti.fi> wrote in message
news:1163166994.843815.142270@.h48g2000cwc.googlegroups.com...
> Hi,
> I'm a beginner when it comes to SQL Server 2005 and user permissions.
> I'm wondering, how do I set the default rights for views yet to be
> created? I've only find a way to configure the already existing views.
> Br,
> Tom
>|||Using a schema I can deny or grant permissions to use the alter
command, but not specifically grant alter for any view (existing or to
be created) AND deny alter for any table. I can only either grant or
deny alter, right?
Do I really have to specifically set permissions for every table and
view and not just specify a default setting for all views and table in
a database?
Uri Dimant wrote:[vbcol=seagreen]
> Tom
> GRANT SELECT on SCHENA ...... GRANT SELECT on VIEW .... . (See details
> in the BOL) .
> Do the users have SSMS installed on their workstations?
>
> "Tom" <tom.kostiainen@.saunalahti.fi> wrote in message
> news:1163166994.843815.142270@.h48g2000cwc.googlegroups.com...|||Tom
> Do I really have to specifically set permissions for every table and
> view and not just specify a default setting for all views and table in
> a database?
You can GRANT SELECT ... on database level , take a look at GRANT topic in
the BOL
"Tom" <tom.kostiainen@.saunalahti.fi> wrote in message
news:1163403167.187392.286270@.m7g2000cwm.googlegroups.com...
> Using a schema I can deny or grant permissions to use the alter
> command, but not specifically grant alter for any view (existing or to
> be created) AND deny alter for any table. I can only either grant or
> deny alter, right?
> Do I really have to specifically set permissions for every table and
> view and not just specify a default setting for all views and table in
> a database?
>
> Uri Dimant wrote:
>

Permission on views with underlying table in diff. DB

We have many views that are in one database and the underlying tables are in
a different database. We had all of our data on one server and this worked
fine. We now moved our data to a new server with a fairly new installation
of SQL Server, and our users cannot access the views. We have a role that w
e
assign our users to and then we grant select to that role on our views. Is
there any setting that needs to be changed in order for this to work?
ThanksFor accessing different database you have to configure crossdatabase
ownership chains:
http://support.microsoft.com/kb/810474/en-us
HTH, Jens SUessmeyer.
"Andy" <Andy@.discussions.microsoft.com> schrieb im Newsbeitrag
news:B8A87C25-F49C-4A79-B8A9-4FAF1CF17234@.microsoft.com...
> We have many views that are in one database and the underlying tables are
> in
> a different database. We had all of our data on one server and this
> worked
> fine. We now moved our data to a new server with a fairly new
> installation
> of SQL Server, and our users cannot access the views. We have a role that
> we
> assign our users to and then we grant select to that role on our views.
> Is
> there any setting that needs to be changed in order for this to work?
> Thanks