Permit view and deny table?

This has probably been asked before, but is there a way to allow a user to
access a view but deny access to the underlying table? If so, how is this
done (even as a kluge)?
Thanks!For a user to be given select permissions on a view but not a table
referenced by the view, the view and underlying table must have the same
owner. This forms an ownership chain. As long as that is unbroken then the
permission check will be on the view and not the underlying table. By not
granting explict permissions on your tables and the user will not be able to
select from them.
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
"Neil W." <neilw@.netlib.com> wrote in message
news:uflVC0shEHA.3320@.TK2MSFTNGP11.phx.gbl...
> This has probably been asked before, but is there a way to allow a user to
> access a view but deny access to the underlying table? If so, how is this
> done (even as a kluge)?
> Thanks!
>

Permissions...

I have been looking for an example of applying permissions to all the
tables, view, stored procs, etc., in X sql database without much luck.
Clearly, it's simple to do for a single record but I have over 500 tables,
views, etc., that need to have permissions assigned for SELECT, INSERT,
UPDATE and DELETE for the tables (EXECUTE for the stored procs).
The must be an example that I am missing that allows me to assign all these
permissions quickly, we have a tool within our application but since I have
no permission currently set I cannot even get to that tool to run it.
Thanks again - I always get assistance from users here! It's the best part
about moving our app from VFP data to SQL data.
Chrisyou can put users in db_datareader and db_datawriter roles to get select,
insert, delete and update permissions.
"Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
news:%23tnwBCWXFHA.2348@.TK2MSFTNGP14.phx.gbl...
>I have been looking for an example of applying permissions to all the
>tables, view, stored procs, etc., in X sql database without much luck.
>Clearly, it's simple to do for a single record but I have over 500 tables,
>views, etc., that need to have permissions assigned for SELECT, INSERT,
>UPDATE and DELETE for the tables (EXECUTE for the stored procs).
> The must be an example that I am missing that allows me to assign all
> these permissions quickly, we have a tool within our application but since
> I have no permission currently set I cannot even get to that tool to run
> it.
> Thanks again - I always get assistance from users here! It's the best
> part about moving our app from VFP data to SQL data.
> Chris
>|||Hi,
Use the database level fixed roles db_datareader and db_datawriter to
assign SELECT, UPDATE, INSERT, DELETE previlages for all
TABLES/VIEW. But for stored procedure Execution rights there is no roles
available. Only way is to give Execute previlages individually.
GRANT EXEC on Proc_name to Username
If you have numerous procs then write a small script to query the sysobjects
table for Xtype ='P' to get all the procedure names and use the above Grant
statement inside a cursor.
Thanks
Hari
Sql Server Mvp
"Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
news:%23tnwBCWXFHA.2348@.TK2MSFTNGP14.phx.gbl...
>I have been looking for an example of applying permissions to all the
>tables, view, stored procs, etc., in X sql database without much luck.
>Clearly, it's simple to do for a single record but I have over 500 tables,
>views, etc., that need to have permissions assigned for SELECT, INSERT,
>UPDATE and DELETE for the tables (EXECUTE for the stored procs).
> The must be an example that I am missing that allows me to assign all
> these permissions quickly, we have a tool within our application but since
> I have no permission currently set I cannot even get to that tool to run
> it.
> Thanks again - I always get assistance from users here! It's the best
> part about moving our app from VFP data to SQL data.
> Chris
>|||Thank you!!!!!
"Hari Pra" <hari_pra_k@.hotmail.com> wrote in message
news:exHE4XWXFHA.628@.tk2msftngp13.phx.gbl...
> Hi,
> Use the database level fixed roles db_datareader and db_datawriter to
> assign SELECT, UPDATE, INSERT, DELETE previlages for all
> TABLES/VIEW. But for stored procedure Execution rights there is no roles
> available. Only way is to give Execute previlages individually.
> GRANT EXEC on Proc_name to Username
> If you have numerous procs then write a small script to query the
> sysobjects table for Xtype ='P' to get all the procedure names and use the
> above Grant
> statement inside a cursor.
> Thanks
> Hari
> Sql Server Mvp
>
> "Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
> news:%23tnwBCWXFHA.2348@.TK2MSFTNGP14.phx.gbl...
>|||Thank you!!!!
"Richard Ding" <rding@.acadian-asset.com> wrote in message
news:eHa6nNWXFHA.2128@.TK2MSFTNGP14.phx.gbl...
> you can put users in db_datareader and db_datawriter roles to get select,
> insert, delete and update permissions.
>
> "Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
> news:%23tnwBCWXFHA.2348@.TK2MSFTNGP14.phx.gbl...
>sql

Permissions with Windows groups and view to other databases

I am having a problem with permissions using Windows groups. I have a database (database1) that has permissions granted via Windows groups. Two groups (group1 and group2) are members of the db_datareader role in database1, and this work fine. Do to the number of tables that get created during our work, using db_datareader is the easiest way to keep up with permissions without creating a maintenance problem. Now I have a table that I want to add to this database, but I only want group2 to have select permission on this one table which is a problem because group1 has the db_datareader role. So I thought I could create a view in this database to the restricted table that I put in database2. Then in database2 I only added group2 as a user with the permission to select from this table. Unfortunately the group membership does not seem to get interpretted correctly in database2 and no one can successfult select from the view in database1.

In other words, user1 who belongs to group1 connects to database1 and cannot select from the restricted view -- this is what I would expect. However, when user2 who belongs to group2 connects to database1 they also cannot select from the restricted view -- not the behvior I would expect. Now, if I make user2 a user in database2 with select on the restricted table then user2 can connect to database1 and successfuly get data from the restricted view. So it looks like the fact that user2 belongs to group2 is never passed to database2 via the select from the view on database1. Is this indeed the way that Windows group security is working or is meant to work in SQL Server?

I realize I could solve this simplified version of the problem by creating my own role in database1 for group1 etc., but I am trying to solve a bigger problem in our environment that has hundreds of databases across numerous servers.

Thanks

Rob

Why not simply deny SELECT permission to group1 for that particular table? The rule that you need to remember is that a deny will trump a grant, so the deny will take precedence over the db_datareader membership.

Thanks

Laurentiu

|||Well, that won't quite work. The two Windows groups we are talking about have some overlapping members, but one group is not a complete subset of the other. So if I deny SELECT to group1 then some people that I want to access the data (group2) because they are in both groups and as you point out deny has a higher priority. Is there any reason group permissions are not valid in database2 when selecting from the view in database1?|||

This is the expected behavior; but it sounds like you may be trying to attempt cross-database ownership chaining (also known as CDOC, look for “Using ownership chains” topic in BOL). CDOC is a feature that is disabled by default and we recommend against using it because of the security risks inherent from this feature. For more information on CDOC look for “Using ownership chains” topic in BOL.

The reason why user2 is failing to access the table is that there is a separate user token for database1 and database2 (both derived from the same Windows login token). On database1 the user2 token will look similar to this:

Primary identity:

· user2, Windows user

Secondary identities:

· group2, Windows group

· db_datareader, role

When accessing the view, the permissions are checked against this token, and they will succeed, but the view is making reference to database2.<some_schema>.restricted_table, therefore it is necessary to create a token for database2.

On your first attempt (without creating a user in Database2, and granting permission to access the table) the user token creation process for database2 should have failed with a “user cannot access this database”-type of error.

Here are a few potential workarounds that may help you:

Instead of using db_datareader you can use different schemas and grant SELECT based on the schemas to differentiate groups, for example:

GRANT SELECT ON SCHEMA::[Schema_group1] TO group1

GRANT SELECT ON SCHEMA::[Schema_group2] TO group2

GRANT SELECT ON SCHEMA::[Schema_all] TO group1, group2

That way the SELECT permission would be restricted to only the schemas you defined.

Another alternative for cross-DB access could be using signatures, similar to the one I described in the following article: http://blogs.msdn.com/raulga/archive/2006/10/30/using-a-digital-signature-as-a-secondary-identity-to-replace-cross-database-ownership-chaining.aspx

Please, let us know if any of this alternatives worked for you or if you have any additional questions.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

Permissions with Windows groups and view to other databases

I am having a problem with permissions using Windows groups. I have a database (database1) that has permissions granted via Windows groups. Two groups (group1 and group2) are members of the db_datareader role in database1, and this work fine. Do to the number of tables that get created during our work, using db_datareader is the easiest way to keep up with permissions without creating a maintenance problem. Now I have a table that I want to add to this database, but I only want group2 to have select permission on this one table which is a problem because group1 has the db_datareader role. So I thought I could create a view in this database to the restricted table that I put in database2. Then in database2 I only added group2 as a user with the permission to select from this table. Unfortunately the group membership does not seem to get interpretted correctly in database2 and no one can successfult select from the view in database1.

In other words, user1 who belongs to group1 connects to database1 and cannot select from the restricted view -- this is what I would expect. However, when user2 who belongs to group2 connects to database1 they also cannot select from the restricted view -- not the behvior I would expect. Now, if I make user2 a user in database2 with select on the restricted table then user2 can connect to database1 and successfuly get data from the restricted view. So it looks like the fact that user2 belongs to group2 is never passed to database2 via the select from the view on database1. Is this indeed the way that Windows group security is working or is meant to work in SQL Server?

I realize I could solve this simplified version of the problem by creating my own role in database1 for group1 etc., but I am trying to solve a bigger problem in our environment that has hundreds of databases across numerous servers.

Thanks

Rob

Why not simply deny SELECT permission to group1 for that particular table? The rule that you need to remember is that a deny will trump a grant, so the deny will take precedence over the db_datareader membership.

Thanks

Laurentiu

|||Well, that won't quite work. The two Windows groups we are talking about have some overlapping members, but one group is not a complete subset of the other. So if I deny SELECT to group1 then some people that I want to access the data (group2) because they are in both groups and as you point out deny has a higher priority. Is there any reason group permissions are not valid in database2 when selecting from the view in database1?|||

This is the expected behavior; but it sounds like you may be trying to attempt cross-database ownership chaining (also known as CDOC, look for “Using ownership chains” topic in BOL). CDOC is a feature that is disabled by default and we recommend against using it because of the security risks inherent from this feature. For more information on CDOC look for “Using ownership chains” topic in BOL.

The reason why user2 is failing to access the table is that there is a separate user token for database1 and database2 (both derived from the same Windows login token). On database1 the user2 token will look similar to this:

Primary identity:

· user2, Windows user

Secondary identities:

· group2, Windows group

· db_datareader, role

When accessing the view, the permissions are checked against this token, and they will succeed, but the view is making reference to database2.<some_schema>.restricted_table, therefore it is necessary to create a token for database2.

On your first attempt (without creating a user in Database2, and granting permission to access the table) the user token creation process for database2 should have failed with a “user cannot access this database”-type of error.

Here are a few potential workarounds that may help you:

Instead of using db_datareader you can use different schemas and grant SELECT based on the schemas to differentiate groups, for example:

GRANT SELECT ON SCHEMA::[Schema_group1] TO group1

GRANT SELECT ON SCHEMA::[Schema_group2] TO group2

GRANT SELECT ON SCHEMA::[Schema_all] TO group1, group2

That way the SELECT permission would be restricted to only the schemas you defined.

Another alternative for cross-DB access could be using signatures, similar to the one I described in the following article: http://blogs.msdn.com/raulga/archive/2006/10/30/using-a-digital-signature-as-a-secondary-identity-to-replace-cross-database-ownership-chaining.aspx

Please, let us know if any of this alternatives worked for you or if you have any additional questions.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

permissions to view jobs but not change them

is there any combination of permissions or a role that will let a user view
stored procedures and their success without being able to modify them? Thanks.
What do you exactly mean by "view stored procedures"? Look at the source code? Everyone can do that
unless you have encrypted the procedure (then no-one can do that).
Also, what do you mean by "and their success"?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:7D66188B-9C98-42B3-8989-8CC23E7C97DC@.microsoft.com...
> is there any combination of permissions or a role that will let a user view
> stored procedures and their success without being able to modify them? Thanks.
|||Sorry, I meant view jobs... in the Jobs viewer in Enterprise Manager. Is
there a combination of permissions or a role that would let a user view a job
and check its success without being able to modify it in any way. Thanks.
"Tibor Karaszi" wrote:

> What do you exactly mean by "view stored procedures"? Look at the source code? Everyone can do that
> unless you have encrypted the procedure (then no-one can do that).
> Also, what do you mean by "and their success"?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
> news:7D66188B-9C98-42B3-8989-8CC23E7C97DC@.microsoft.com...
>
>
|||There is no such feature. SQL Server Agent doesn't have any "role" scheme or similar. You could try
playing with the TargetServersRole. It *might* do what you want, but it is designed for something
else...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:F0678877-B513-4AC6-BE4F-DA44830D9048@.microsoft.com...[vbcol=seagreen]
> Sorry, I meant view jobs... in the Jobs viewer in Enterprise Manager. Is
> there a combination of permissions or a role that would let a user view a job
> and check its success without being able to modify it in any way. Thanks.
> "Tibor Karaszi" wrote:
that[vbcol=seagreen]

permissions to view jobs but not change them

is there any combination of permissions or a role that will let a user view
stored procedures and their success without being able to modify them? Thanks.What do you exactly mean by "view stored procedures"? Look at the source code? Everyone can do that
unless you have encrypted the procedure (then no-one can do that).
Also, what do you mean by "and their success"?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:7D66188B-9C98-42B3-8989-8CC23E7C97DC@.microsoft.com...
> is there any combination of permissions or a role that will let a user view
> stored procedures and their success without being able to modify them? Thanks.|||Sorry, I meant view jobs... in the Jobs viewer in Enterprise Manager. Is
there a combination of permissions or a role that would let a user view a job
and check its success without being able to modify it in any way. Thanks.
"Tibor Karaszi" wrote:
> What do you exactly mean by "view stored procedures"? Look at the source code? Everyone can do that
> unless you have encrypted the procedure (then no-one can do that).
> Also, what do you mean by "and their success"?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
> news:7D66188B-9C98-42B3-8989-8CC23E7C97DC@.microsoft.com...
> > is there any combination of permissions or a role that will let a user view
> > stored procedures and their success without being able to modify them? Thanks.
>
>|||There is no such feature. SQL Server Agent doesn't have any "role" scheme or similar. You could try
playing with the TargetServersRole. It *might* do what you want, but it is designed for something
else...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:F0678877-B513-4AC6-BE4F-DA44830D9048@.microsoft.com...
> Sorry, I meant view jobs... in the Jobs viewer in Enterprise Manager. Is
> there a combination of permissions or a role that would let a user view a job
> and check its success without being able to modify it in any way. Thanks.
> "Tibor Karaszi" wrote:
> > What do you exactly mean by "view stored procedures"? Look at the source code? Everyone can do
that
> > unless you have encrypted the procedure (then no-one can do that).
> >
> > Also, what do you mean by "and their success"?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
> > news:7D66188B-9C98-42B3-8989-8CC23E7C97DC@.microsoft.com...
> > > is there any combination of permissions or a role that will let a user view
> > > stored procedures and their success without being able to modify them? Thanks.
> >
> >
> >

Permissions to view code but not modify (Help)

Hello Everyone,
I am new to SQL 2005 and am trying to setup permissions for the vb .net
programmers.
I want them to be able to look at the stored procedure/view/functions code
but not be able to modify the code.
How can I accomplish this.
Thanks for all the help
Shabnam
Grant them VIEW DEFINTION permissions. Either at the server, database, schema or object level.
See ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/151b7a2e-ab97-42ba-baf0-6929c5334e29.htm
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Shabnam Gupta" <sgupta@.evcsolutions.com> wrote in message
news:ePR8IIhFGHA.3056@.TK2MSFTNGP09.phx.gbl...
> Hello Everyone,
> I am new to SQL 2005 and am trying to setup permissions for the vb .net programmers.
> I want them to be able to look at the stored procedure/view/functions code but not be able to
> modify the code.
> How can I accomplish this.
>
> Thanks for all the help
> Shabnam
>

Permissions to view code but not modify (Help)

Hello Everyone,
I am new to SQL 2005 and am trying to setup permissions for the vb .net
programmers.
I want them to be able to look at the stored procedure/view/functions code
but not be able to modify the code.
How can I accomplish this.
Thanks for all the help
ShabnamGrant them VIEW DEFINTION permissions. Either at the server, database, schema or object level.
See ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/151b7a2e-ab97-42ba-baf0-6929c5334e29.htm
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Shabnam Gupta" <sgupta@.evcsolutions.com> wrote in message
news:ePR8IIhFGHA.3056@.TK2MSFTNGP09.phx.gbl...
> Hello Everyone,
> I am new to SQL 2005 and am trying to setup permissions for the vb .net programmers.
> I want them to be able to look at the stored procedure/view/functions code but not be able to
> modify the code.
> How can I accomplish this.
>
> Thanks for all the help
> Shabnam
>sql

Permissions to view code but not modify (Help)

Hello Everyone,
I am new to SQL 2005 and am trying to setup permissions for the vb .net
programmers.
I want them to be able to look at the stored procedure/view/functions code
but not be able to modify the code.
How can I accomplish this.
Thanks for all the help
ShabnamGrant them VIEW DEFINTION permissions. Either at the server, database, schem
a or object level.
See ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/151b7a2e-ab97-42ba-baf0-
6929c5334e29.htm
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Shabnam Gupta" <sgupta@.evcsolutions.com> wrote in message
news:ePR8IIhFGHA.3056@.TK2MSFTNGP09.phx.gbl...
> Hello Everyone,
> I am new to SQL 2005 and am trying to setup permissions for the vb .net pr
ogrammers.
> I want them to be able to look at the stored procedure/view/functions code
but not be able to
> modify the code.
> How can I accomplish this.
>
> Thanks for all the help
> Shabnam
>

permissions to see design view

Is there a way to allow users to see the design view of a table without
having dbo permissions?
ThanksI'm not familiar with the design view, but you can use sp_help to view
the table structure from Query Analyzer.

Simon

Permissions to Deploy and view RS Homepage

I messed up some settings security settings in IIS manager
and due to which I am having trouble with deployment
through VS.NET as well as trouble viewing the Reporting
Services main page.
When I type http://localhost/Reports, I just get the Home
page with Home, My Subscriptions and Help links on the
right top corner. All my reports have dissapeared. Any
tips?Go to properties of Reports and ReportServer virtual roots, open security
tab, select "Edit" under "authentication and access control", then make sure
anonymous in NOT checked. See if this helps.
--
Dmitry Vasilevsky, SQL Server Reporting Services Developer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
---
"Amar Karande" <anonymous@.discussions.microsoft.com> wrote in message
news:24f3d01c4606e$869a6170$a601280a@.phx.gbl...
> I messed up some settings security settings in IIS manager
> and due to which I am having trouble with deployment
> through VS.NET as well as trouble viewing the Reporting
> Services main page.
> When I type http://localhost/Reports, I just get the Home
> page with Home, My Subscriptions and Help links on the
> right top corner. All my reports have dissapeared. Any
> tips?|||It helped. Thanks a bunch.
>--Original Message--
>Go to properties of Reports and ReportServer virtual
roots, open security
>tab, select "Edit" under "authentication and access
control", then make sure
>anonymous in NOT checked. See if this helps.
>--
>Dmitry Vasilevsky, SQL Server Reporting Services
Developer
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>--
>---
>"Amar Karande" <anonymous@.discussions.microsoft.com>
wrote in message
>news:24f3d01c4606e$869a6170$a601280a@.phx.gbl...
>> I messed up some settings security settings in IIS
manager
>> and due to which I am having trouble with deployment
>> through VS.NET as well as trouble viewing the Reporting
>> Services main page.
>> When I type http://localhost/Reports, I just get the
Home
>> page with Home, My Subscriptions and Help links on the
>> right top corner. All my reports have dissapeared. Any
>> tips?
>
>.
>

permissions resetting on a View

I have a View that pulls data from one table.
I have assigned a Role with Select-only permission for
that View.
For some reason the permissions for that Role/View keep
getting deleted, so I have to go back and re-grant Select
access over and over again.
Anybody know why this is happening, and is there any way
to prevent it? There should be no change to the
permissions for that Role at all.
TIA,
TerrellWhen you delete a Role or a View, or any other object for that matter, you
also delete the permissions associated with those objects. If you what to
keep the permissions for the View then don't delete the view and recreate,
but instead just ALTER the view. When you ALTER an object the permissions
that are associated with the object stay intact.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Terrell Miller" <millerto@.bellsouth.net> wrote in message
news:2afa201c46819$3b6e4510$a501280a@.phx
.gbl...
> I have a View that pulls data from one table.
> I have assigned a Role with Select-only permission for
> that View.
> For some reason the permissions for that Role/View keep
> getting deleted, so I have to go back and re-grant Select
> access over and over again.
> Anybody know why this is happening, and is there any way
> to prevent it? There should be no change to the
> permissions for that Role at all.
> TIA,
> Terrell|||
>--Original Message--
>When you delete a Role or a View, or any other object for
that matter, you
>also delete the permissions associated with those
objects. If you what to
>keep the permissions for the View then don't delete the
view and recreate,
>but instead just ALTER the view. When you ALTER an
object the permissions
>that are associated with the object stay intact.
Greg, we aren't changing the view or the roles. It's just
that from time to time the Select permission on that View
gets removed.
Question: when you use sp_refreshview does that actually
delete the view and recreate it? I can't set up an ALTER
inside a sproc (because the ALTER has to be the first line
in a batch, but the CREATE PROCEDURE statement has to
execute before it), which is why I'm using sp_refreshview.
Thanks again,
Terrell|||Since the sp_renameview is a system store procedure, I'm not exactly sure
whether it drops and recreates the view. I did a little test I did, when
you run the sp_refreshview it appears to keep the permissions on a view.
If you really want to create or alter a view via a stored procedure you can
do that with dynamic SQL. Something like so:
create procedure yoursp as
declare @.cmd char(1000)
set @.cmd = 'alter view yourview as select bing, bang, boom from yourtable'
exec(@.cmd)
-- rest of sp
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Terrell Miller" <millerto@.bellsouth.net> wrote in message
news:2b1a001c4682f$259d8b90$a501280a@.phx
.gbl...
>
> that matter, you
> objects. If you what to
> view and recreate,
> object the permissions
> Greg, we aren't changing the view or the roles. It's just
> that from time to time the Select permission on that View
> gets removed.
> Question: when you use sp_refreshview does that actually
> delete the view and recreate it? I can't set up an ALTER
> inside a sproc (because the ALTER has to be the first line
> in a batch, but the CREATE PROCEDURE statement has to
> execute before it), which is why I'm using sp_refreshview.
> Thanks again,
> Terrell

permissions resetting on a View

I have a View that pulls data from one table.
I have assigned a Role with Select-only permission for
that View.
For some reason the permissions for that Role/View keep
getting deleted, so I have to go back and re-grant Select
access over and over again.
Anybody know why this is happening, and is there any way
to prevent it? There should be no change to the
permissions for that Role at all.
TIA,
Terrell
When you delete a Role or a View, or any other object for that matter, you
also delete the permissions associated with those objects. If you what to
keep the permissions for the View then don't delete the view and recreate,
but instead just ALTER the view. When you ALTER an object the permissions
that are associated with the object stay intact.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Terrell Miller" <millerto@.bellsouth.net> wrote in message
news:2afa201c46819$3b6e4510$a501280a@.phx.gbl...
> I have a View that pulls data from one table.
> I have assigned a Role with Select-only permission for
> that View.
> For some reason the permissions for that Role/View keep
> getting deleted, so I have to go back and re-grant Select
> access over and over again.
> Anybody know why this is happening, and is there any way
> to prevent it? There should be no change to the
> permissions for that Role at all.
> TIA,
> Terrell
|||
>--Original Message--
>When you delete a Role or a View, or any other object for
that matter, you
>also delete the permissions associated with those
objects. If you what to
>keep the permissions for the View then don't delete the
view and recreate,
>but instead just ALTER the view. When you ALTER an
object the permissions
>that are associated with the object stay intact.
Greg, we aren't changing the view or the roles. It's just
that from time to time the Select permission on that View
gets removed.
Question: when you use sp_refreshview does that actually
delete the view and recreate it? I can't set up an ALTER
inside a sproc (because the ALTER has to be the first line
in a batch, but the CREATE PROCEDURE statement has to
execute before it), which is why I'm using sp_refreshview.
Thanks again,
Terrell
|||Since the sp_renameview is a system store procedure, I'm not exactly sure
whether it drops and recreates the view. I did a little test I did, when
you run the sp_refreshview it appears to keep the permissions on a view.
If you really want to create or alter a view via a stored procedure you can
do that with dynamic SQL. Something like so:
create procedure yoursp as
declare @.cmd char(1000)
set @.cmd = 'alter view yourview as select bing, bang, boom from yourtable'
exec(@.cmd)
-- rest of sp
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Terrell Miller" <millerto@.bellsouth.net> wrote in message
news:2b1a001c4682f$259d8b90$a501280a@.phx.gbl...
> that matter, you
> objects. If you what to
> view and recreate,
> object the permissions
> Greg, we aren't changing the view or the roles. It's just
> that from time to time the Select permission on that View
> gets removed.
> Question: when you use sp_refreshview does that actually
> delete the view and recreate it? I can't set up an ALTER
> inside a sproc (because the ALTER has to be the first line
> in a batch, but the CREATE PROCEDURE statement has to
> execute before it), which is why I'm using sp_refreshview.
> Thanks again,
> Terrell

permissions resetting on a View

I have a View that pulls data from one table.
I have assigned a Role with Select-only permission for
that View.
For some reason the permissions for that Role/View keep
getting deleted, so I have to go back and re-grant Select
access over and over again.
Anybody know why this is happening, and is there any way
to prevent it? There should be no change to the
permissions for that Role at all.
TIA,
TerrellWhen you delete a Role or a View, or any other object for that matter, you
also delete the permissions associated with those objects. If you what to
keep the permissions for the View then don't delete the view and recreate,
but instead just ALTER the view. When you ALTER an object the permissions
that are associated with the object stay intact.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Terrell Miller" <millerto@.bellsouth.net> wrote in message
news:2afa201c46819$3b6e4510$a501280a@.phx.gbl...
> I have a View that pulls data from one table.
> I have assigned a Role with Select-only permission for
> that View.
> For some reason the permissions for that Role/View keep
> getting deleted, so I have to go back and re-grant Select
> access over and over again.
> Anybody know why this is happening, and is there any way
> to prevent it? There should be no change to the
> permissions for that Role at all.
> TIA,
> Terrell|||>--Original Message--
>When you delete a Role or a View, or any other object for
that matter, you
>also delete the permissions associated with those
objects. If you what to
>keep the permissions for the View then don't delete the
view and recreate,
>but instead just ALTER the view. When you ALTER an
object the permissions
>that are associated with the object stay intact.
Greg, we aren't changing the view or the roles. It's just
that from time to time the Select permission on that View
gets removed.
Question: when you use sp_refreshview does that actually
delete the view and recreate it? I can't set up an ALTER
inside a sproc (because the ALTER has to be the first line
in a batch, but the CREATE PROCEDURE statement has to
execute before it), which is why I'm using sp_refreshview.
Thanks again,
Terrell|||Since the sp_renameview is a system store procedure, I'm not exactly sure
whether it drops and recreates the view. I did a little test I did, when
you run the sp_refreshview it appears to keep the permissions on a view.
If you really want to create or alter a view via a stored procedure you can
do that with dynamic SQL. Something like so:
create procedure yoursp as
declare @.cmd char(1000)
set @.cmd = 'alter view yourview as select bing, bang, boom from yourtable'
exec(@.cmd)
-- rest of sp
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Terrell Miller" <millerto@.bellsouth.net> wrote in message
news:2b1a001c4682f$259d8b90$a501280a@.phx.gbl...
> >--Original Message--
> >When you delete a Role or a View, or any other object for
> that matter, you
> >also delete the permissions associated with those
> objects. If you what to
> >keep the permissions for the View then don't delete the
> view and recreate,
> >but instead just ALTER the view. When you ALTER an
> object the permissions
> >that are associated with the object stay intact.
> Greg, we aren't changing the view or the roles. It's just
> that from time to time the Select permission on that View
> gets removed.
> Question: when you use sp_refreshview does that actually
> delete the view and recreate it? I can't set up an ALTER
> inside a sproc (because the ALTER has to be the first line
> in a batch, but the CREATE PROCEDURE statement has to
> execute before it), which is why I'm using sp_refreshview.
> Thanks again,
> Terrell

Permissions problem accessing external DLL through reports

Hi,
I'm having this wee problem when trying to view my report via the report
manager screen - I get the following error "Request for the permission of
type System.Security.Permissions.SecurityPermission, mscorlib,
Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
failed"...This seems to be due to me trying to access a seperate DLL which in
turn accesses the database that I created for the reports for translation
purposes (I added the DLL to the References collection for each of the
reports). Any suggestions would be greatly appreciated.I am also tring to use an assembly to translate y i got the same message.
I already try to configure my rssvpolicy.config like:
<CodeGroup class="UnionCodeGroup"
version="1"
PermissionSetName="XMLFile_MultiLang_Permission"
Name="MultiLangCodeGroup"
Description="A special code group for my custom assembly.">
<IMembershipCondition class="UrlMembershipCondition"
version="1"
Url="C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\bin\MultLang.dll"/>
</CodeGroup>
but i habe the same error.
Have you found out how to solve it ?
"Rowan Massey" wrote:
> Hi,
> I'm having this wee problem when trying to view my report via the report
> manager screen - I get the following error "Request for the permission of
> type System.Security.Permissions.SecurityPermission, mscorlib,
> Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
> failed"...This seems to be due to me trying to access a seperate DLL which in
> turn accesses the database that I created for the reports for translation
> purposes (I added the DLL to the References collection for each of the
> reports). Any suggestions would be greatly appreciated.sql

permissions mystery: ActiveDirectory issue?

I don't see why my user cannot see a view when she can see the tables that
underlie it and has also been granted select permission on the view.
exec sp_grantlogin [OURDOMAIN\user99]
exec sp_grantdbaccess [OURDOMAIN\user99], 'SARAH'
exec sp_addrole 'TheRole'
exec sp_addrolemember 'TheRole', 'SARAH'create view TestView
as select * from table1
inner join table2
on t1.id = t2.anotherid
grant select on table1 to TheRole
grant select on table2 to TheRole
grant select on TestView to TheRole
User SARAH can see the tables but not the view.
We're using SQL Server 2000 and Windows 2003 Server with ActiveDirectory.
Thanks
TimoI've installed Query Analyzer on the user's desktop, and she CAN see the
view. So the problem has to do with the client application (Access 2000 ADP)
and/or ActiveDirectory. Our domain admin put the Access 2000 ADP on his PC
and he can see the view fine.
Timo
"Timo" <Timo@.unspam.biz> wrote in message
news:eRc2XxAeFHA.2420@.TK2MSFTNGP12.phx.gbl...
> I don't see why my user cannot see a view when she can see the tables that
> underlie it and has also been granted select permission on the view.
> exec sp_grantlogin [OURDOMAIN\user99]
> exec sp_grantdbaccess [OURDOMAIN\user99], 'SARAH'
> exec sp_addrole 'TheRole'
> exec sp_addrolemember 'TheRole', 'SARAH'create view TestView
> as select * from table1
> inner join table2
> on t1.id = t2.anotherid
>
> grant select on table1 to TheRole
> grant select on table2 to TheRole
> grant select on TestView to TheRole
> User SARAH can see the tables but not the view.
> We're using SQL Server 2000 and Windows 2003 Server with ActiveDirectory.
> Thanks
> Timo
>

Permissions Management

Hello,
I am trying to set up permissions to secure databases in our Developement. I
have set up "View any Database" to Deny at the Database level.
when i login as admin i see all the database while the rest see Master and
Temp DB. What do i have to do at the database level so i can make other
databases visible to appropriate personnel and also give them control such as
Create Tables, Update SP etc.
Thanks
MannyManny
If the user is an owner of db try the following
Create a new SQL login "login1"
? Create a user named ?login1? in master database
? Grant CREATE DATABASE to login1
? While impersonating login1, create a database called ?dbteste?
? Revoke CREATE DATABASE permission from login1
? Revoke VIEW ANY DATABASE permission from PUBLIC
? Register this server as login1
? From the ?login1? session, expand database tree. Now, you should see
master, tempdb, dbteste
? Grant VIEW ANY DATABASE to PUBLIC
? From the ?login1? session, you should see all the databases
"Manny Chohan" <MannyChohan@.discussions.microsoft.com> wrote in message
news:5E4EAE5B-E79D-408C-98AC-946C8EC0566E@.microsoft.com...
> Hello,
> I am trying to set up permissions to secure databases in our Developement.
> I
> have set up "View any Database" to Deny at the Database level.
> when i login as admin i see all the database while the rest see Master and
> Temp DB. What do i have to do at the database level so i can make other
> databases visible to appropriate personnel and also give them control such
> as
> Create Tables, Update SP etc.
> Thanks
> Manny|||So basically the user who is creating the database will be only one who can
view the database in the tree.
What i am trying to do is following:
I have users User1, User2, User3 and Databases as DB1, DB2 and DB3.
I would like to revoke Public from seeing all the databases.
User 1 should have access to DB1 (as DBO) and DB2 as read/write
User 2 should only have access to DB2 (DBO)
User 3 should have access to DB2 (DBO) and DB3 as read only.
Hope i am clear in explaining things
THanks
Manny
"Uri Dimant" wrote:
> Manny
> If the user is an owner of db try the following
> Create a new SQL login "login1"
> â?¢ Create a user named â'login1â' in master database
> â?¢ Grant CREATE DATABASE to login1
> â?¢ While impersonating login1, create a database called â'dbtesteâ'
> â?¢ Revoke CREATE DATABASE permission from login1
> â?¢ Revoke VIEW ANY DATABASE permission from PUBLIC
> â?¢ Register this server as login1
> â?¢ From the â'login1â' session, expand database tree. Now, you should see
> master, tempdb, dbteste
> â?¢ Grant VIEW ANY DATABASE to PUBLIC
> â?¢ From the â'login1â' session, you should see all the databases
>
>
> "Manny Chohan" <MannyChohan@.discussions.microsoft.com> wrote in message
> news:5E4EAE5B-E79D-408C-98AC-946C8EC0566E@.microsoft.com...
> > Hello,
> >
> > I am trying to set up permissions to secure databases in our Developement.
> > I
> > have set up "View any Database" to Deny at the Database level.
> >
> > when i login as admin i see all the database while the rest see Master and
> > Temp DB. What do i have to do at the database level so i can make other
> > databases visible to appropriate personnel and also give them control such
> > as
> > Create Tables, Update SP etc.
> >
> > Thanks
> >
> > Manny
>
>|||Manny,
VIEW ANY DATABASE is a server level permission and cannot be granted nor
denied at the database level. To accomplish what you wish to do, you can
deny the permission to the public role (rather than to individual users) and
put users in the db_owner role of the database(s) they should have access.
This would then allow them to see only databases which they have ownership
of.
With regards to granting/revoking DDL permissions, I suggest you try out the
tool SQL CodeSecure. It allows you to set DDL permissions very easily, as
well as audit all changes modifications made to a database and even rollback
changes or recover deleted objects.
You can download the application from this location:
http://www.sql-labs.com/downloads/SQLCodeSecure.zip
HTH.
"Manny Chohan" <MannyChohan@.discussions.microsoft.com> wrote in message
news:5E4EAE5B-E79D-408C-98AC-946C8EC0566E@.microsoft.com...
> Hello,
> I am trying to set up permissions to secure databases in our Developement.
> I
> have set up "View any Database" to Deny at the Database level.
> when i login as admin i see all the database while the rest see Master and
> Temp DB. What do i have to do at the database level so i can make other
> databases visible to appropriate personnel and also give them control such
> as
> Create Tables, Update SP etc.
> Thanks
> Manny

permissions issue?

I am having a problem running this statement:
GRANT VIEW DEFINITION ON OBJECT::MyDatabase.MyStoredProcedure TO MyUser
I get this error:
Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'MyStoredProcedure', because it does not exist
or you do not have permission.
MyStoredProcedure definitely DOES exist in the database, and I'm
running the statement as a sysadmin user. I even tried "sa" just in
case. But no dice. This seems like a very straightforward matter.
If I use the interface, not command line, I am able to grant
permissions easily. But there's something it doesn't like about my
above statement. Any ideas appreciated!
Thanks
> If I use the interface, not command line, I am able to grant
> permissions easily.
Script the TSQL command submitted by the GUI and you will see the difference. Do you really have a
schema named MyDatabase in your database?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<tootsuite@.gmail.com> wrote in message news:1158610855.925197.188970@.i42g2000cwa.googlegr oups.com...
>I am having a problem running this statement:
> GRANT VIEW DEFINITION ON OBJECT::MyDatabase.MyStoredProcedure TO MyUser
> I get this error:
> Msg 15151, Level 16, State 1, Line 1
> Cannot find the object 'MyStoredProcedure', because it does not exist
> or you do not have permission.
> MyStoredProcedure definitely DOES exist in the database, and I'm
> running the statement as a sysadmin user. I even tried "sa" just in
> case. But no dice. This seems like a very straightforward matter.
> If I use the interface, not command line, I am able to grant
> permissions easily. But there's something it doesn't like about my
> above statement. Any ideas appreciated!
> Thanks
>
|||Here's the syntax, from Books Online:
GRANT <permission> ON
[ OBJECT :: ][ schema_name ]. object_name TO <database_principal>
So in your example:
schema_name = MyDatabase
object_name = MyStoredProcedure
database_principal = MyUser
Note that the name of the database should not be included, only the schema
name within the database.
HTH
Kalen Delaney, SQL Server MVP
<tootsuite@.gmail.com> wrote in message
news:1158610855.925197.188970@.i42g2000cwa.googlegr oups.com...
>I am having a problem running this statement:
> GRANT VIEW DEFINITION ON OBJECT::MyDatabase.MyStoredProcedure TO MyUser
> I get this error:
> Msg 15151, Level 16, State 1, Line 1
> Cannot find the object 'MyStoredProcedure', because it does not exist
> or you do not have permission.
> MyStoredProcedure definitely DOES exist in the database, and I'm
> running the statement as a sysadmin user. I even tried "sa" just in
> case. But no dice. This seems like a very straightforward matter.
> If I use the interface, not command line, I am able to grant
> permissions easily. But there's something it doesn't like about my
> above statement. Any ideas appreciated!
> Thanks
>
|||
> Script the TSQL command submitted by the GUI and you will see the difference. Do you really have a
> schema named MyDatabase in your database?
No, of course not. But I don't like posting private company information
on the net :-)
[vbcol=seagreen]
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <tootsuite@.gmail.com> wrote in message news:1158610855.925197.188970@.i42g2000cwa.googlegr oups.com...
|||Removing the db name worked, thanks
Kalen Delaney wrote:[vbcol=seagreen]
> Here's the syntax, from Books Online:
> GRANT <permission> ON
> [ OBJECT :: ][ schema_name ]. object_name TO <database_principal>
> So in your example:
> schema_name = MyDatabase
> object_name = MyStoredProcedure
> database_principal = MyUser
> Note that the name of the database should not be included, only the schema
> name within the database.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> <tootsuite@.gmail.com> wrote in message
> news:1158610855.925197.188970@.i42g2000cwa.googlegr oups.com...
|||I think Tibor was actually asking the same question I was. Is whatever you
used instead of MyDatabase a database name or a schema name?
Our guess is that you were confusing the two. If you had used the schema
name, it would have worked.
HTH
Kalen Delaney, SQL Server MVP
<tootsuite@.gmail.com> wrote in message
news:1158614641.882490.40330@.m7g2000cwm.googlegrou ps.com...
>
> No, of course not. But I don't like posting private company information
> on the net :-)
>
|||>I think Tibor was actually asking the same question I was.
Indeed. Thanks for clarifying Kalen. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:umdKy632GHA.3464@.TK2MSFTNGP03.phx.gbl...
>I think Tibor was actually asking the same question I was. Is whatever you used instead of
>MyDatabase a database name or a schema name?
> Our guess is that you were confusing the two. If you had used the schema name, it would have
> worked.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> <tootsuite@.gmail.com> wrote in message news:1158614641.882490.40330@.m7g2000cwm.googlegrou ps.com...
>
|||And it seems from his answer to me that his "MyDatabase" was a database
name, not a schema name.
As trainers, I can see we have our work cut out for us getting people to
understand this new concept!
Kalen Delaney, SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OTyVtdA3GHA.1252@.TK2MSFTNGP04.phx.gbl...
> Indeed. Thanks for clarifying Kalen. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:umdKy632GHA.3464@.TK2MSFTNGP03.phx.gbl...
>
|||On Tue, 19 Sep 2006 09:55:10 -0700, "Kalen Delaney"
<replies@.public_newsgroups.com> wrote:

>As trainers, I can see we have our work cut out for us getting people to
>understand this new concept!
Think of it as job security. 8-)
Roy

permissions issue?

I am having a problem running this statement:
GRANT VIEW DEFINITION ON OBJECT::MyDatabase.MyStoredProcedure TO MyUser
I get this error:
Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'MyStoredProcedure', because it does not exist
or you do not have permission.
MyStoredProcedure definitely DOES exist in the database, and I'm
running the statement as a sysadmin user. I even tried "sa" just in
case. But no dice. This seems like a very straightforward matter.
If I use the interface, not command line, I am able to grant
permissions easily. But there's something it doesn't like about my
above statement. Any ideas appreciated!
Thanks> If I use the interface, not command line, I am able to grant
> permissions easily.
Script the TSQL command submitted by the GUI and you will see the difference
. Do you really have a
schema named MyDatabase in your database?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<tootsuite@.gmail.com> wrote in message news:1158610855.925197.188970@.i42g2000cwa.googlegroup
s.com...
>I am having a problem running this statement:
> GRANT VIEW DEFINITION ON OBJECT::MyDatabase.MyStoredProcedure TO MyUser
> I get this error:
> Msg 15151, Level 16, State 1, Line 1
> Cannot find the object 'MyStoredProcedure', because it does not exist
> or you do not have permission.
> MyStoredProcedure definitely DOES exist in the database, and I'm
> running the statement as a sysadmin user. I even tried "sa" just in
> case. But no dice. This seems like a very straightforward matter.
> If I use the interface, not command line, I am able to grant
> permissions easily. But there's something it doesn't like about my
> above statement. Any ideas appreciated!
> Thanks
>|||Here's the syntax, from Books Online:
GRANT <permission> ON
[ OBJECT :: ][ schema_name ]. object_name TO <database_principal
>
So in your example:
schema_name = MyDatabase
object_name = MyStoredProcedure
database_principal = MyUser
Note that the name of the database should not be included, only the schema
name within the database.
HTH
Kalen Delaney, SQL Server MVP
<tootsuite@.gmail.com> wrote in message
news:1158610855.925197.188970@.i42g2000cwa.googlegroups.com...
>I am having a problem running this statement:
> GRANT VIEW DEFINITION ON OBJECT::MyDatabase.MyStoredProcedure TO MyUser
> I get this error:
> Msg 15151, Level 16, State 1, Line 1
> Cannot find the object 'MyStoredProcedure', because it does not exist
> or you do not have permission.
> MyStoredProcedure definitely DOES exist in the database, and I'm
> running the statement as a sysadmin user. I even tried "sa" just in
> case. But no dice. This seems like a very straightforward matter.
> If I use the interface, not command line, I am able to grant
> permissions easily. But there's something it doesn't like about my
> above statement. Any ideas appreciated!
> Thanks
>|||
> Script the TSQL command submitted by the GUI and you will see the differen
ce. Do you really have a
> schema named MyDatabase in your database?
No, of course not. But I don't like posting private company information
on the net :-)
[vbcol=seagreen]
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <tootsuite@.gmail.com> wrote in message news:1158610855.925197.188970@.i42g2
000cwa.googlegroups.com...|||Removing the db name worked, thanks
Kalen Delaney wrote:[vbcol=seagreen]
> Here's the syntax, from Books Online:
> GRANT <permission> ON
> [ OBJECT :: ][ schema_name ]. object_name TO <database_pri
ncipal>
> So in your example:
> schema_name = MyDatabase
> object_name = MyStoredProcedure
> database_principal = MyUser
> Note that the name of the database should not be included, only the schema
> name within the database.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> <tootsuite@.gmail.com> wrote in message
> news:1158610855.925197.188970@.i42g2000cwa.googlegroups.com...|||I think Tibor was actually asking the same question I was. Is whatever you
used instead of MyDatabase a database name or a schema name?
Our guess is that you were confusing the two. If you had used the schema
name, it would have worked.
HTH
Kalen Delaney, SQL Server MVP
<tootsuite@.gmail.com> wrote in message
news:1158614641.882490.40330@.m7g2000cwm.googlegroups.com...
>
> No, of course not. But I don't like posting private company information
> on the net :-)
>
>|||>I think Tibor was actually asking the same question I was.
Indeed. Thanks for clarifying Kalen. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:umdKy632GHA.3464@.TK2MSFTNGP03.phx.gbl...
>I think Tibor was actually asking the same question I was. Is whatever you
used instead of
>MyDatabase a database name or a schema name?
> Our guess is that you were confusing the two. If you had used the schema n
ame, it would have
> worked.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> <tootsuite@.gmail.com> wrote in message news:1158614641.882490.40330@.m7g200
0cwm.googlegroups.com...
>|||And it seems from his answer to me that his "MyDatabase" was a database
name, not a schema name.
As trainers, I can see we have our work cut out for us getting people to
understand this new concept!
Kalen Delaney, SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OTyVtdA3GHA.1252@.TK2MSFTNGP04.phx.gbl...
> Indeed. Thanks for clarifying Kalen. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:umdKy632GHA.3464@.TK2MSFTNGP03.phx.gbl...
>|||On Tue, 19 Sep 2006 09:55:10 -0700, "Kalen Delaney"
<replies@.public_newsgroups.com> wrote:

>As trainers, I can see we have our work cut out for us getting people to
>understand this new concept!
Think of it as job security. 8-)
Roy

permissions issue?

I am having a problem running this statement:
GRANT VIEW DEFINITION ON OBJECT::MyDatabase.MyStoredProcedure TO MyUser
I get this error:
Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'MyStoredProcedure', because it does not exist
or you do not have permission.
MyStoredProcedure definitely DOES exist in the database, and I'm
running the statement as a sysadmin user. I even tried "sa" just in
case. But no dice. This seems like a very straightforward matter.
If I use the interface, not command line, I am able to grant
permissions easily. But there's something it doesn't like about my
above statement. Any ideas appreciated!
Thanks> If I use the interface, not command line, I am able to grant
> permissions easily.
Script the TSQL command submitted by the GUI and you will see the difference. Do you really have a
schema named MyDatabase in your database?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<tootsuite@.gmail.com> wrote in message news:1158610855.925197.188970@.i42g2000cwa.googlegroups.com...
>I am having a problem running this statement:
> GRANT VIEW DEFINITION ON OBJECT::MyDatabase.MyStoredProcedure TO MyUser
> I get this error:
> Msg 15151, Level 16, State 1, Line 1
> Cannot find the object 'MyStoredProcedure', because it does not exist
> or you do not have permission.
> MyStoredProcedure definitely DOES exist in the database, and I'm
> running the statement as a sysadmin user. I even tried "sa" just in
> case. But no dice. This seems like a very straightforward matter.
> If I use the interface, not command line, I am able to grant
> permissions easily. But there's something it doesn't like about my
> above statement. Any ideas appreciated!
> Thanks
>|||Here's the syntax, from Books Online:
GRANT <permission> ON
[ OBJECT :: ][ schema_name ]. object_name TO <database_principal>
So in your example:
schema_name = MyDatabase
object_name = MyStoredProcedure
database_principal = MyUser
Note that the name of the database should not be included, only the schema
name within the database.
--
HTH
Kalen Delaney, SQL Server MVP
<tootsuite@.gmail.com> wrote in message
news:1158610855.925197.188970@.i42g2000cwa.googlegroups.com...
>I am having a problem running this statement:
> GRANT VIEW DEFINITION ON OBJECT::MyDatabase.MyStoredProcedure TO MyUser
> I get this error:
> Msg 15151, Level 16, State 1, Line 1
> Cannot find the object 'MyStoredProcedure', because it does not exist
> or you do not have permission.
> MyStoredProcedure definitely DOES exist in the database, and I'm
> running the statement as a sysadmin user. I even tried "sa" just in
> case. But no dice. This seems like a very straightforward matter.
> If I use the interface, not command line, I am able to grant
> permissions easily. But there's something it doesn't like about my
> above statement. Any ideas appreciated!
> Thanks
>|||> Script the TSQL command submitted by the GUI and you will see the difference. Do you really have a
> schema named MyDatabase in your database?
No, of course not. But I don't like posting private company information
on the net :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <tootsuite@.gmail.com> wrote in message news:1158610855.925197.188970@.i42g2000cwa.googlegroups.com...
> >I am having a problem running this statement:
> >
> > GRANT VIEW DEFINITION ON OBJECT::MyDatabase.MyStoredProcedure TO MyUser
> >
> > I get this error:
> >
> > Msg 15151, Level 16, State 1, Line 1
> > Cannot find the object 'MyStoredProcedure', because it does not exist
> > or you do not have permission.
> >
> > MyStoredProcedure definitely DOES exist in the database, and I'm
> > running the statement as a sysadmin user. I even tried "sa" just in
> > case. But no dice. This seems like a very straightforward matter.
> >
> > If I use the interface, not command line, I am able to grant
> > permissions easily. But there's something it doesn't like about my
> > above statement. Any ideas appreciated!
> >
> > Thanks
> >|||Removing the db name worked, thanks
Kalen Delaney wrote:
> Here's the syntax, from Books Online:
> GRANT <permission> ON
> [ OBJECT :: ][ schema_name ]. object_name TO <database_principal>
> So in your example:
> schema_name = MyDatabase
> object_name = MyStoredProcedure
> database_principal = MyUser
> Note that the name of the database should not be included, only the schema
> name within the database.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> <tootsuite@.gmail.com> wrote in message
> news:1158610855.925197.188970@.i42g2000cwa.googlegroups.com...
> >I am having a problem running this statement:
> >
> > GRANT VIEW DEFINITION ON OBJECT::MyDatabase.MyStoredProcedure TO MyUser
> >
> > I get this error:
> >
> > Msg 15151, Level 16, State 1, Line 1
> > Cannot find the object 'MyStoredProcedure', because it does not exist
> > or you do not have permission.
> >
> > MyStoredProcedure definitely DOES exist in the database, and I'm
> > running the statement as a sysadmin user. I even tried "sa" just in
> > case. But no dice. This seems like a very straightforward matter.
> >
> > If I use the interface, not command line, I am able to grant
> > permissions easily. But there's something it doesn't like about my
> > above statement. Any ideas appreciated!
> >
> > Thanks
> >|||I think Tibor was actually asking the same question I was. Is whatever you
used instead of MyDatabase a database name or a schema name?
Our guess is that you were confusing the two. If you had used the schema
name, it would have worked.
--
HTH
Kalen Delaney, SQL Server MVP
<tootsuite@.gmail.com> wrote in message
news:1158614641.882490.40330@.m7g2000cwm.googlegroups.com...
>> Script the TSQL command submitted by the GUI and you will see the
>> difference. Do you really have a
>> schema named MyDatabase in your database?
> No, of course not. But I don't like posting private company information
> on the net :-)
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> <tootsuite@.gmail.com> wrote in message
>> news:1158610855.925197.188970@.i42g2000cwa.googlegroups.com...
>> >I am having a problem running this statement:
>> >
>> > GRANT VIEW DEFINITION ON OBJECT::MyDatabase.MyStoredProcedure TO MyUser
>> >
>> > I get this error:
>> >
>> > Msg 15151, Level 16, State 1, Line 1
>> > Cannot find the object 'MyStoredProcedure', because it does not exist
>> > or you do not have permission.
>> >
>> > MyStoredProcedure definitely DOES exist in the database, and I'm
>> > running the statement as a sysadmin user. I even tried "sa" just in
>> > case. But no dice. This seems like a very straightforward matter.
>> >
>> > If I use the interface, not command line, I am able to grant
>> > permissions easily. But there's something it doesn't like about my
>> > above statement. Any ideas appreciated!
>> >
>> > Thanks
>> >
>|||>I think Tibor was actually asking the same question I was.
Indeed. Thanks for clarifying Kalen. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:umdKy632GHA.3464@.TK2MSFTNGP03.phx.gbl...
>I think Tibor was actually asking the same question I was. Is whatever you used instead of
>MyDatabase a database name or a schema name?
> Our guess is that you were confusing the two. If you had used the schema name, it would have
> worked.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> <tootsuite@.gmail.com> wrote in message news:1158614641.882490.40330@.m7g2000cwm.googlegroups.com...
>>
>> Script the TSQL command submitted by the GUI and you will see the difference. Do you really have
>> a
>> schema named MyDatabase in your database?
>> No, of course not. But I don't like posting private company information
>> on the net :-)
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> <tootsuite@.gmail.com> wrote in message
>> news:1158610855.925197.188970@.i42g2000cwa.googlegroups.com...
>> >I am having a problem running this statement:
>> >
>> > GRANT VIEW DEFINITION ON OBJECT::MyDatabase.MyStoredProcedure TO MyUser
>> >
>> > I get this error:
>> >
>> > Msg 15151, Level 16, State 1, Line 1
>> > Cannot find the object 'MyStoredProcedure', because it does not exist
>> > or you do not have permission.
>> >
>> > MyStoredProcedure definitely DOES exist in the database, and I'm
>> > running the statement as a sysadmin user. I even tried "sa" just in
>> > case. But no dice. This seems like a very straightforward matter.
>> >
>> > If I use the interface, not command line, I am able to grant
>> > permissions easily. But there's something it doesn't like about my
>> > above statement. Any ideas appreciated!
>> >
>> > Thanks
>> >
>|||And it seems from his answer to me that his "MyDatabase" was a database
name, not a schema name.
As trainers, I can see we have our work cut out for us getting people to
understand this new concept!
--
Kalen Delaney, SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OTyVtdA3GHA.1252@.TK2MSFTNGP04.phx.gbl...
> >I think Tibor was actually asking the same question I was.
> Indeed. Thanks for clarifying Kalen. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:umdKy632GHA.3464@.TK2MSFTNGP03.phx.gbl...
>>I think Tibor was actually asking the same question I was. Is whatever you
>>used instead of MyDatabase a database name or a schema name?
>> Our guess is that you were confusing the two. If you had used the schema
>> name, it would have worked.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>> <tootsuite@.gmail.com> wrote in message
>> news:1158614641.882490.40330@.m7g2000cwm.googlegroups.com...
>>
>> Script the TSQL command submitted by the GUI and you will see the
>> difference. Do you really have a
>> schema named MyDatabase in your database?
>> No, of course not. But I don't like posting private company information
>> on the net :-)
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> <tootsuite@.gmail.com> wrote in message
>> news:1158610855.925197.188970@.i42g2000cwa.googlegroups.com...
>> >I am having a problem running this statement:
>> >
>> > GRANT VIEW DEFINITION ON OBJECT::MyDatabase.MyStoredProcedure TO
>> > MyUser
>> >
>> > I get this error:
>> >
>> > Msg 15151, Level 16, State 1, Line 1
>> > Cannot find the object 'MyStoredProcedure', because it does not exist
>> > or you do not have permission.
>> >
>> > MyStoredProcedure definitely DOES exist in the database, and I'm
>> > running the statement as a sysadmin user. I even tried "sa" just in
>> > case. But no dice. This seems like a very straightforward matter.
>> >
>> > If I use the interface, not command line, I am able to grant
>> > permissions easily. But there's something it doesn't like about my
>> > above statement. Any ideas appreciated!
>> >
>> > Thanks
>> >
>>
>|||On Tue, 19 Sep 2006 09:55:10 -0700, "Kalen Delaney"
<replies@.public_newsgroups.com> wrote:
>As trainers, I can see we have our work cut out for us getting people to
>understand this new concept!
Think of it as job security. 8-)
Roy