I am looking for some patterns in SQL Server. The Patterns and Paractices
did not seem to have what I am looking for, but it would seem to me that it
is a very commmon scenario that must have been covered.
Basically, it is the problem of aggregating data for overviews.
Assume the following:
Table Services
[SID][ServiceName][ServiceCategoryID][PI
D] [AmmountDue]
Table Payer
[PID][PayerCategory][PayerName]
Table Payments
[PayID][SID][Date][Amount][PID] ** Occasionally a third party might pay
for someone else
Table ServiceCategory
[ServiceCategoryID][ServiceCategoryName]
Table PayerCategory
[PayerCategoryID][PayerCategoryName]
Now, I want to get a summary of the data very quickly that breaks things
down like:
By ServiceCategory
TotalAmountPaid TotalDue AmountDueFrom30DaysAgo ADF31-60DaysAgo
adf61-90Days Ago
Then break these down by PayerCategory
This would seem like a common type of thing, and Ican think of ways to do
this but that take a lot of time, if there are millions of rows, and I can
imagine that triggers might be useful here to keep up to date, but I am
unfamiliar with them.
If you can give me any guidance on this it owuld be helpful. For extra
points, what about being able to dynamically change the periods from say
0-30days to 0-15 days)
Thanks a lot
BBFor starters, you need to post some ddl, sample data and expected results.
Not just a narrative.
I can tell you this though - without dates in your services table or
payments table to know when the service and payments took place, what you're
looking for is impossible.
"bobbyballgame" wrote:
> I am looking for some patterns in SQL Server. The Patterns and Paractices
> did not seem to have what I am looking for, but it would seem to me that i
t
> is a very commmon scenario that must have been covered.
> Basically, it is the problem of aggregating data for overviews.
> Assume the following:
> Table Services
> [SID][ServiceName][ServiceCategoryID][PI
D] [AmmountDue]
> Table Payer
> [PID][PayerCategory][PayerName]
> Table Payments
> [PayID][SID][Date][Amount][PID] ** Occasionally a third party might pay
> for someone else
> Table ServiceCategory
> [ServiceCategoryID][ServiceCategoryName]
> Table PayerCategory
> [PayerCategoryID][PayerCategoryName]
>
> Now, I want to get a summary of the data very quickly that breaks things
> down like:
> By ServiceCategory
> TotalAmountPaid TotalDue AmountDueFrom30DaysAgo ADF31-60DaysAgo
> adf61-90Days Ago
> Then break these down by PayerCategory
>
> This would seem like a common type of thing, and Ican think of ways to do
> this but that take a lot of time, if there are millions of rows, and I can
> imagine that triggers might be useful here to keep up to date, but I am
> unfamiliar with them.
> If you can give me any guidance on this it owuld be helpful. For extra
> points, what about being able to dynamically change the periods from say
> 0-30days to 0-15 days)
> Thanks a lot
> BB
>
>
>|||Steve,
Thanks. The tables are internal ( I would not be allowed to post them) and a
lot more complicated. For example the Payments Table has 31 fields in it, so
I was trying to simplify.
The Service does have a Date field. Sorry about the ommission. Really, I am
looking for a general pattern for the problem of needing aggregate data from
many, amny rows quickly, so I thought a narrative would be more useful.
I will work on a model that is a little more simple, and for what is worth,
I need the data in XML format from SQL 2000.
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:070D02CF-7367-4593-91F4-6533588D830E@.microsoft.com...
> For starters, you need to post some ddl, sample data and expected results.
> Not just a narrative.
> I can tell you this though - without dates in your services table or
> payments table to know when the service and payments took place, what
> you're
> looking for is impossible.
>
> "bobbyballgame" wrote:
>
Showing posts with label tables. Show all posts
Showing posts with label tables. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
Persistence Of Temporary Tables
Is it possible to create a temporary table in a 'parent' stored procedure and then access it from a 'child' or nested stored procedure? Bearing in mind that the child proc will definitely be called by the parent proc.
Look at the article from Erland:
http://www.sommarskog.se/share_data.html
HTH, jens Suessmeyer.
sqlPermssion denied for dbo
Hi,
My NT and my Ad user account are both shown as dbo on a database.
However, when I create tables using either account they are shown as not
being owned by dbo.
Then, when I try to insert or update these tables, it says permission denied
.
How can this be? I'm a dbo? Even if I wern't a dbo, as I own the table I
shoyuld be able to modify its data. Whats going on here?
ThanksHi
Because these accounts are not member of sysadmin server role
Try do
CREATE TABLE dbo.Mytabale
(
blalala
)
"Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
news:5C0E66A8-2327-456A-8605-A9B13D82C158@.microsoft.com...
> Hi,
> My NT and my Ad user account are both shown as dbo on a database.
> However, when I create tables using either account they are shown as not
> being owned by dbo.
> Then, when I try to insert or update these tables, it says permission
denied.
> How can this be? I'm a dbo? Even if I wern't a dbo, as I own the table I
> shoyuld be able to modify its data. Whats going on here?
> Thanks|||Yes, I'm sure that would help create the tables as owned by dbo, but that
that is not my issue.
I still can't update or insert into these tables even though I am logging on
as a dbo.
Any ideas why not?
"Uri Dimant" wrote:
> Hi
> Because these accounts are not member of sysadmin server role
> Try do
> CREATE TABLE dbo.Mytabale
> (
> blalala
> )
> "Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
> news:5C0E66A8-2327-456A-8605-A9B13D82C158@.microsoft.com...
> denied.
>
>|||"Firestarter" schrieb:
> Yes, I'm sure that would help create the tables as owned by dbo, but that
> that is not my issue.
> I still can't update or insert into these tables even though I am logging
on
> as a dbo.
> Any ideas why not?
You can update the tables as dbo! You just have to add the ownername before
the objectname (as the dbo is not the owner of the object).
Objects that belong to the dbo can always be accessed by everybody without
the owner's name, because 'dbo' is the default owner ...|||> My NT and my Ad user account are both shown as dbo on a database.
Are these Windows or SQL Server logins?
Since you say that two different logins are "dbo" in a database, you are say
ing that both are
sysadmin? Right? (You cannot have two logins being the same user in a databa
se).
How do you determine that both are dbo? What tools/commands do you use to de
termine this?
Or are you saying that both are in the db_owner role in the database? That i
s a different thing from
being the dbo of a database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
news:5C0E66A8-2327-456A-8605-A9B13D82C158@.microsoft.com...
> Hi,
> My NT and my Ad user account are both shown as dbo on a database.
> However, when I create tables using either account they are shown as not
> being owned by dbo.
> Then, when I try to insert or update these tables, it says permission deni
ed.
> How can this be? I'm a dbo? Even if I wern't a dbo, as I own the table I
> shoyuld be able to modify its data. Whats going on here?
> Thanks|||No, I can't! Thats my point. Wether I put the owner name or not, I canot
update the table.
That is the issue I am trying to resolve.
"Christian Donner" wrote:
> "Firestarter" schrieb:
> You can update the tables as dbo! You just have to add the ownername befor
e
> the objectname (as the dbo is not the owner of the object).
> Objects that belong to the dbo can always be accessed by everybody without
> the owner's name, because 'dbo' is the default owner ...|||These are windows logins.
And I mean that both are in the db_owner role. Apologies for the lack of
prescsion in my post.
I am using EM to determine this,
"Tibor Karaszi" wrote:
> Are these Windows or SQL Server logins?
> Since you say that two different logins are "dbo" in a database, you are s
aying that both are
> sysadmin? Right? (You cannot have two logins being the same user in a data
base).
> How do you determine that both are dbo? What tools/commands do you use to
determine this?
> Or are you saying that both are in the db_owner role in the database? That
is a different thing from
> being the dbo of a database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
> news:5C0E66A8-2327-456A-8605-A9B13D82C158@.microsoft.com...
>|||When a db_owner (who isn't dbo) creates an object, it will not be owned by d
bo. It will be owned by
that persons user name in the database. You cannot change that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
news:D4179915-0813-403B-B075-A20DAD8C312D@.microsoft.com...[vbcol=seagreen]
> These are windows logins.
> And I mean that both are in the db_owner role. Apologies for the lack of
> prescsion in my post.
> I am using EM to determine this,
> "Tibor Karaszi" wrote:
>|||Firestarter wrote:
> No, I can't! Thats my point. Wether I put the owner name or not, I
> canot update the table.
> That is the issue I am trying to resolve.
>
Are you specifying the dbo owner name in the create statement as Uri
suggested? If not, try it that way.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||In an attempt to avoid the confusion I am creating, this is what I've got:
--AD server, table created using an AD account which is a member of db_owner
fixed db role
create table felix1 (test varchar(10) null)
-- Shows in EM as being owned by londonfire\hedleyf, as expected
create table dbo.felix1 (test varchar(10) null)
-- Shows in EM as being owned by dbo, as expected
--
insert felix1 (test)
values (2)
--results
Server: Msg 229, Level 14, State 5, Line 1
INSERT permission denied on object 'felix1', database 'CFS_HFSRA_V3_test',
owner 'LONDONFIRE\HEDLEYF'.
insert dbo.felix1 (test)
values (2)
--results
Server: Msg 229, Level 14, State 5, Line 1
INSERT permission denied on object 'felix1', database 'CFS_HFSRA_V3_test',
owner 'dbo'.
So I am in the db_owner role, and seem unable to update a table.
What is going on?
Thanks for your continued patience...
"David Gugick" wrote:
> Firestarter wrote:
> Are you specifying the dbo owner name in the create statement as Uri
> suggested? If not, try it that way.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
My NT and my Ad user account are both shown as dbo on a database.
However, when I create tables using either account they are shown as not
being owned by dbo.
Then, when I try to insert or update these tables, it says permission denied
.
How can this be? I'm a dbo? Even if I wern't a dbo, as I own the table I
shoyuld be able to modify its data. Whats going on here?
ThanksHi
Because these accounts are not member of sysadmin server role
Try do
CREATE TABLE dbo.Mytabale
(
blalala
)
"Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
news:5C0E66A8-2327-456A-8605-A9B13D82C158@.microsoft.com...
> Hi,
> My NT and my Ad user account are both shown as dbo on a database.
> However, when I create tables using either account they are shown as not
> being owned by dbo.
> Then, when I try to insert or update these tables, it says permission
denied.
> How can this be? I'm a dbo? Even if I wern't a dbo, as I own the table I
> shoyuld be able to modify its data. Whats going on here?
> Thanks|||Yes, I'm sure that would help create the tables as owned by dbo, but that
that is not my issue.
I still can't update or insert into these tables even though I am logging on
as a dbo.
Any ideas why not?
"Uri Dimant" wrote:
> Hi
> Because these accounts are not member of sysadmin server role
> Try do
> CREATE TABLE dbo.Mytabale
> (
> blalala
> )
> "Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
> news:5C0E66A8-2327-456A-8605-A9B13D82C158@.microsoft.com...
> denied.
>
>|||"Firestarter" schrieb:
> Yes, I'm sure that would help create the tables as owned by dbo, but that
> that is not my issue.
> I still can't update or insert into these tables even though I am logging
on
> as a dbo.
> Any ideas why not?
You can update the tables as dbo! You just have to add the ownername before
the objectname (as the dbo is not the owner of the object).
Objects that belong to the dbo can always be accessed by everybody without
the owner's name, because 'dbo' is the default owner ...|||> My NT and my Ad user account are both shown as dbo on a database.
Are these Windows or SQL Server logins?
Since you say that two different logins are "dbo" in a database, you are say
ing that both are
sysadmin? Right? (You cannot have two logins being the same user in a databa
se).
How do you determine that both are dbo? What tools/commands do you use to de
termine this?
Or are you saying that both are in the db_owner role in the database? That i
s a different thing from
being the dbo of a database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
news:5C0E66A8-2327-456A-8605-A9B13D82C158@.microsoft.com...
> Hi,
> My NT and my Ad user account are both shown as dbo on a database.
> However, when I create tables using either account they are shown as not
> being owned by dbo.
> Then, when I try to insert or update these tables, it says permission deni
ed.
> How can this be? I'm a dbo? Even if I wern't a dbo, as I own the table I
> shoyuld be able to modify its data. Whats going on here?
> Thanks|||No, I can't! Thats my point. Wether I put the owner name or not, I canot
update the table.
That is the issue I am trying to resolve.
"Christian Donner" wrote:
> "Firestarter" schrieb:
> You can update the tables as dbo! You just have to add the ownername befor
e
> the objectname (as the dbo is not the owner of the object).
> Objects that belong to the dbo can always be accessed by everybody without
> the owner's name, because 'dbo' is the default owner ...|||These are windows logins.
And I mean that both are in the db_owner role. Apologies for the lack of
prescsion in my post.
I am using EM to determine this,
"Tibor Karaszi" wrote:
> Are these Windows or SQL Server logins?
> Since you say that two different logins are "dbo" in a database, you are s
aying that both are
> sysadmin? Right? (You cannot have two logins being the same user in a data
base).
> How do you determine that both are dbo? What tools/commands do you use to
determine this?
> Or are you saying that both are in the db_owner role in the database? That
is a different thing from
> being the dbo of a database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
> news:5C0E66A8-2327-456A-8605-A9B13D82C158@.microsoft.com...
>|||When a db_owner (who isn't dbo) creates an object, it will not be owned by d
bo. It will be owned by
that persons user name in the database. You cannot change that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
news:D4179915-0813-403B-B075-A20DAD8C312D@.microsoft.com...[vbcol=seagreen]
> These are windows logins.
> And I mean that both are in the db_owner role. Apologies for the lack of
> prescsion in my post.
> I am using EM to determine this,
> "Tibor Karaszi" wrote:
>|||Firestarter wrote:
> No, I can't! Thats my point. Wether I put the owner name or not, I
> canot update the table.
> That is the issue I am trying to resolve.
>
Are you specifying the dbo owner name in the create statement as Uri
suggested? If not, try it that way.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||In an attempt to avoid the confusion I am creating, this is what I've got:
--AD server, table created using an AD account which is a member of db_owner
fixed db role
create table felix1 (test varchar(10) null)
-- Shows in EM as being owned by londonfire\hedleyf, as expected
create table dbo.felix1 (test varchar(10) null)
-- Shows in EM as being owned by dbo, as expected
--
insert felix1 (test)
values (2)
--results
Server: Msg 229, Level 14, State 5, Line 1
INSERT permission denied on object 'felix1', database 'CFS_HFSRA_V3_test',
owner 'LONDONFIRE\HEDLEYF'.
insert dbo.felix1 (test)
values (2)
--results
Server: Msg 229, Level 14, State 5, Line 1
INSERT permission denied on object 'felix1', database 'CFS_HFSRA_V3_test',
owner 'dbo'.
So I am in the db_owner role, and seem unable to update a table.
What is going on?
Thanks for your continued patience...
"David Gugick" wrote:
> Firestarter wrote:
> Are you specifying the dbo owner name in the create statement as Uri
> suggested? If not, try it that way.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
Permssion denied for dbo
Hi,
My NT and my Ad user account are both shown as dbo on a database.
However, when I create tables using either account they are shown as not
being owned by dbo.
Then, when I try to insert or update these tables, it says permission denied.
How can this be? I'm a dbo? Even if I wern't a dbo, as I own the table I
shoyuld be able to modify its data. Whats going on here?
Thanks
Hi
Because these accounts are not member of sysadmin server role
Try do
CREATE TABLE dbo.Mytabale
(
blalala
)
"Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
news:5C0E66A8-2327-456A-8605-A9B13D82C158@.microsoft.com...
> Hi,
> My NT and my Ad user account are both shown as dbo on a database.
> However, when I create tables using either account they are shown as not
> being owned by dbo.
> Then, when I try to insert or update these tables, it says permission
denied.
> How can this be? I'm a dbo? Even if I wern't a dbo, as I own the table I
> shoyuld be able to modify its data. Whats going on here?
> Thanks
|||Yes, I'm sure that would help create the tables as owned by dbo, but that
that is not my issue.
I still can't update or insert into these tables even though I am logging on
as a dbo.
Any ideas why not?
"Uri Dimant" wrote:
> Hi
> Because these accounts are not member of sysadmin server role
> Try do
> CREATE TABLE dbo.Mytabale
> (
> blalala
> )
> "Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
> news:5C0E66A8-2327-456A-8605-A9B13D82C158@.microsoft.com...
> denied.
>
>
|||"Firestarter" schrieb:
> Yes, I'm sure that would help create the tables as owned by dbo, but that
> that is not my issue.
> I still can't update or insert into these tables even though I am logging on
> as a dbo.
> Any ideas why not?
You can update the tables as dbo! You just have to add the ownername before
the objectname (as the dbo is not the owner of the object).
Objects that belong to the dbo can always be accessed by everybody without
the owner's name, because 'dbo' is the default owner ...
|||> My NT and my Ad user account are both shown as dbo on a database.
Are these Windows or SQL Server logins?
Since you say that two different logins are "dbo" in a database, you are saying that both are
sysadmin? Right? (You cannot have two logins being the same user in a database).
How do you determine that both are dbo? What tools/commands do you use to determine this?
Or are you saying that both are in the db_owner role in the database? That is a different thing from
being the dbo of a database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
news:5C0E66A8-2327-456A-8605-A9B13D82C158@.microsoft.com...
> Hi,
> My NT and my Ad user account are both shown as dbo on a database.
> However, when I create tables using either account they are shown as not
> being owned by dbo.
> Then, when I try to insert or update these tables, it says permission denied.
> How can this be? I'm a dbo? Even if I wern't a dbo, as I own the table I
> shoyuld be able to modify its data. Whats going on here?
> Thanks
|||No, I can't! Thats my point. Wether I put the owner name or not, I canot
update the table.
That is the issue I am trying to resolve.
"Christian Donner" wrote:
> "Firestarter" schrieb:
> You can update the tables as dbo! You just have to add the ownername before
> the objectname (as the dbo is not the owner of the object).
> Objects that belong to the dbo can always be accessed by everybody without
> the owner's name, because 'dbo' is the default owner ...
|||These are windows logins.
And I mean that both are in the db_owner role. Apologies for the lack of
prescsion in my post.
I am using EM to determine this,
"Tibor Karaszi" wrote:
> Are these Windows or SQL Server logins?
> Since you say that two different logins are "dbo" in a database, you are saying that both are
> sysadmin? Right? (You cannot have two logins being the same user in a database).
> How do you determine that both are dbo? What tools/commands do you use to determine this?
> Or are you saying that both are in the db_owner role in the database? That is a different thing from
> being the dbo of a database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
> news:5C0E66A8-2327-456A-8605-A9B13D82C158@.microsoft.com...
>
|||When a db_owner (who isn't dbo) creates an object, it will not be owned by dbo. It will be owned by
that persons user name in the database. You cannot change that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
news:D4179915-0813-403B-B075-A20DAD8C312D@.microsoft.com...[vbcol=seagreen]
> These are windows logins.
> And I mean that both are in the db_owner role. Apologies for the lack of
> prescsion in my post.
> I am using EM to determine this,
> "Tibor Karaszi" wrote:
|||Firestarter wrote:
> No, I can't! Thats my point. Wether I put the owner name or not, I
> canot update the table.
> That is the issue I am trying to resolve.
>
Are you specifying the dbo owner name in the create statement as Uri
suggested? If not, try it that way.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||In an attempt to avoid the confusion I am creating, this is what I've got:
--AD server, table created using an AD account which is a member of db_owner
fixed db role
create table felix1 (test varchar(10) null)
-- Shows in EM as being owned by londonfire\hedleyf, as expected
create table dbo.felix1 (test varchar(10) null)
-- Shows in EM as being owned by dbo, as expected
insert felix1 (test)
values (2)
--results
Server: Msg 229, Level 14, State 5, Line 1
INSERT permission denied on object 'felix1', database 'CFS_HFSRA_V3_test',
owner 'LONDONFIRE\HEDLEYF'.
insert dbo.felix1 (test)
values (2)
--results
Server: Msg 229, Level 14, State 5, Line 1
INSERT permission denied on object 'felix1', database 'CFS_HFSRA_V3_test',
owner 'dbo'.
So I am in the db_owner role, and seem unable to update a table.
What is going on?
Thanks for your continued patience...
"David Gugick" wrote:
> Firestarter wrote:
> Are you specifying the dbo owner name in the create statement as Uri
> suggested? If not, try it that way.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
sql
My NT and my Ad user account are both shown as dbo on a database.
However, when I create tables using either account they are shown as not
being owned by dbo.
Then, when I try to insert or update these tables, it says permission denied.
How can this be? I'm a dbo? Even if I wern't a dbo, as I own the table I
shoyuld be able to modify its data. Whats going on here?
Thanks
Hi
Because these accounts are not member of sysadmin server role
Try do
CREATE TABLE dbo.Mytabale
(
blalala
)
"Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
news:5C0E66A8-2327-456A-8605-A9B13D82C158@.microsoft.com...
> Hi,
> My NT and my Ad user account are both shown as dbo on a database.
> However, when I create tables using either account they are shown as not
> being owned by dbo.
> Then, when I try to insert or update these tables, it says permission
denied.
> How can this be? I'm a dbo? Even if I wern't a dbo, as I own the table I
> shoyuld be able to modify its data. Whats going on here?
> Thanks
|||Yes, I'm sure that would help create the tables as owned by dbo, but that
that is not my issue.
I still can't update or insert into these tables even though I am logging on
as a dbo.
Any ideas why not?
"Uri Dimant" wrote:
> Hi
> Because these accounts are not member of sysadmin server role
> Try do
> CREATE TABLE dbo.Mytabale
> (
> blalala
> )
> "Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
> news:5C0E66A8-2327-456A-8605-A9B13D82C158@.microsoft.com...
> denied.
>
>
|||"Firestarter" schrieb:
> Yes, I'm sure that would help create the tables as owned by dbo, but that
> that is not my issue.
> I still can't update or insert into these tables even though I am logging on
> as a dbo.
> Any ideas why not?
You can update the tables as dbo! You just have to add the ownername before
the objectname (as the dbo is not the owner of the object).
Objects that belong to the dbo can always be accessed by everybody without
the owner's name, because 'dbo' is the default owner ...
|||> My NT and my Ad user account are both shown as dbo on a database.
Are these Windows or SQL Server logins?
Since you say that two different logins are "dbo" in a database, you are saying that both are
sysadmin? Right? (You cannot have two logins being the same user in a database).
How do you determine that both are dbo? What tools/commands do you use to determine this?
Or are you saying that both are in the db_owner role in the database? That is a different thing from
being the dbo of a database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
news:5C0E66A8-2327-456A-8605-A9B13D82C158@.microsoft.com...
> Hi,
> My NT and my Ad user account are both shown as dbo on a database.
> However, when I create tables using either account they are shown as not
> being owned by dbo.
> Then, when I try to insert or update these tables, it says permission denied.
> How can this be? I'm a dbo? Even if I wern't a dbo, as I own the table I
> shoyuld be able to modify its data. Whats going on here?
> Thanks
|||No, I can't! Thats my point. Wether I put the owner name or not, I canot
update the table.
That is the issue I am trying to resolve.
"Christian Donner" wrote:
> "Firestarter" schrieb:
> You can update the tables as dbo! You just have to add the ownername before
> the objectname (as the dbo is not the owner of the object).
> Objects that belong to the dbo can always be accessed by everybody without
> the owner's name, because 'dbo' is the default owner ...
|||These are windows logins.
And I mean that both are in the db_owner role. Apologies for the lack of
prescsion in my post.
I am using EM to determine this,
"Tibor Karaszi" wrote:
> Are these Windows or SQL Server logins?
> Since you say that two different logins are "dbo" in a database, you are saying that both are
> sysadmin? Right? (You cannot have two logins being the same user in a database).
> How do you determine that both are dbo? What tools/commands do you use to determine this?
> Or are you saying that both are in the db_owner role in the database? That is a different thing from
> being the dbo of a database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
> news:5C0E66A8-2327-456A-8605-A9B13D82C158@.microsoft.com...
>
|||When a db_owner (who isn't dbo) creates an object, it will not be owned by dbo. It will be owned by
that persons user name in the database. You cannot change that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
news:D4179915-0813-403B-B075-A20DAD8C312D@.microsoft.com...[vbcol=seagreen]
> These are windows logins.
> And I mean that both are in the db_owner role. Apologies for the lack of
> prescsion in my post.
> I am using EM to determine this,
> "Tibor Karaszi" wrote:
|||Firestarter wrote:
> No, I can't! Thats my point. Wether I put the owner name or not, I
> canot update the table.
> That is the issue I am trying to resolve.
>
Are you specifying the dbo owner name in the create statement as Uri
suggested? If not, try it that way.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||In an attempt to avoid the confusion I am creating, this is what I've got:
--AD server, table created using an AD account which is a member of db_owner
fixed db role
create table felix1 (test varchar(10) null)
-- Shows in EM as being owned by londonfire\hedleyf, as expected
create table dbo.felix1 (test varchar(10) null)
-- Shows in EM as being owned by dbo, as expected
insert felix1 (test)
values (2)
--results
Server: Msg 229, Level 14, State 5, Line 1
INSERT permission denied on object 'felix1', database 'CFS_HFSRA_V3_test',
owner 'LONDONFIRE\HEDLEYF'.
insert dbo.felix1 (test)
values (2)
--results
Server: Msg 229, Level 14, State 5, Line 1
INSERT permission denied on object 'felix1', database 'CFS_HFSRA_V3_test',
owner 'dbo'.
So I am in the db_owner role, and seem unable to update a table.
What is going on?
Thanks for your continued patience...
"David Gugick" wrote:
> Firestarter wrote:
> Are you specifying the dbo owner name in the create statement as Uri
> suggested? If not, try it that way.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
sql
Permssion denied for dbo
Hi,
My NT and my Ad user account are both shown as dbo on a database.
However, when I create tables using either account they are shown as not
being owned by dbo.
Then, when I try to insert or update these tables, it says permission denied.
How can this be? I'm a dbo? Even if I wern't a dbo, as I own the table I
shoyuld be able to modify its data. Whats going on here?
ThanksHi
Because these accounts are not member of sysadmin server role
Try do
CREATE TABLE dbo.Mytabale
(
blalala
)
"Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
news:5C0E66A8-2327-456A-8605-A9B13D82C158@.microsoft.com...
> Hi,
> My NT and my Ad user account are both shown as dbo on a database.
> However, when I create tables using either account they are shown as not
> being owned by dbo.
> Then, when I try to insert or update these tables, it says permission
denied.
> How can this be? I'm a dbo? Even if I wern't a dbo, as I own the table I
> shoyuld be able to modify its data. Whats going on here?
> Thanks|||Yes, I'm sure that would help create the tables as owned by dbo, but that
that is not my issue.
I still can't update or insert into these tables even though I am logging on
as a dbo.
Any ideas why not?
"Uri Dimant" wrote:
> Hi
> Because these accounts are not member of sysadmin server role
> Try do
> CREATE TABLE dbo.Mytabale
> (
> blalala
> )
> "Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
> news:5C0E66A8-2327-456A-8605-A9B13D82C158@.microsoft.com...
> > Hi,
> >
> > My NT and my Ad user account are both shown as dbo on a database.
> > However, when I create tables using either account they are shown as not
> > being owned by dbo.
> > Then, when I try to insert or update these tables, it says permission
> denied.
> >
> > How can this be? I'm a dbo? Even if I wern't a dbo, as I own the table I
> > shoyuld be able to modify its data. Whats going on here?
> >
> > Thanks
>
>|||"Firestarter" schrieb:
> Yes, I'm sure that would help create the tables as owned by dbo, but that
> that is not my issue.
> I still can't update or insert into these tables even though I am logging on
> as a dbo.
> Any ideas why not?
You can update the tables as dbo! You just have to add the ownername before
the objectname (as the dbo is not the owner of the object).
Objects that belong to the dbo can always be accessed by everybody without
the owner's name, because 'dbo' is the default owner ...|||> My NT and my Ad user account are both shown as dbo on a database.
Are these Windows or SQL Server logins?
Since you say that two different logins are "dbo" in a database, you are saying that both are
sysadmin? Right? (You cannot have two logins being the same user in a database).
How do you determine that both are dbo? What tools/commands do you use to determine this?
Or are you saying that both are in the db_owner role in the database? That is a different thing from
being the dbo of a database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
news:5C0E66A8-2327-456A-8605-A9B13D82C158@.microsoft.com...
> Hi,
> My NT and my Ad user account are both shown as dbo on a database.
> However, when I create tables using either account they are shown as not
> being owned by dbo.
> Then, when I try to insert or update these tables, it says permission denied.
> How can this be? I'm a dbo? Even if I wern't a dbo, as I own the table I
> shoyuld be able to modify its data. Whats going on here?
> Thanks|||No, I can't! Thats my point. Wether I put the owner name or not, I canot
update the table.
That is the issue I am trying to resolve.
"Christian Donner" wrote:
> "Firestarter" schrieb:
> > Yes, I'm sure that would help create the tables as owned by dbo, but that
> > that is not my issue.
> > I still can't update or insert into these tables even though I am logging on
> > as a dbo.
> > Any ideas why not?
> You can update the tables as dbo! You just have to add the ownername before
> the objectname (as the dbo is not the owner of the object).
> Objects that belong to the dbo can always be accessed by everybody without
> the owner's name, because 'dbo' is the default owner ...|||These are windows logins.
And I mean that both are in the db_owner role. Apologies for the lack of
prescsion in my post.
I am using EM to determine this,
"Tibor Karaszi" wrote:
> > My NT and my Ad user account are both shown as dbo on a database.
> Are these Windows or SQL Server logins?
> Since you say that two different logins are "dbo" in a database, you are saying that both are
> sysadmin? Right? (You cannot have two logins being the same user in a database).
> How do you determine that both are dbo? What tools/commands do you use to determine this?
> Or are you saying that both are in the db_owner role in the database? That is a different thing from
> being the dbo of a database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
> news:5C0E66A8-2327-456A-8605-A9B13D82C158@.microsoft.com...
> > Hi,
> >
> > My NT and my Ad user account are both shown as dbo on a database.
> > However, when I create tables using either account they are shown as not
> > being owned by dbo.
> > Then, when I try to insert or update these tables, it says permission denied.
> >
> > How can this be? I'm a dbo? Even if I wern't a dbo, as I own the table I
> > shoyuld be able to modify its data. Whats going on here?
> >
> > Thanks
>|||When a db_owner (who isn't dbo) creates an object, it will not be owned by dbo. It will be owned by
that persons user name in the database. You cannot change that.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
news:D4179915-0813-403B-B075-A20DAD8C312D@.microsoft.com...
> These are windows logins.
> And I mean that both are in the db_owner role. Apologies for the lack of
> prescsion in my post.
> I am using EM to determine this,
> "Tibor Karaszi" wrote:
>> > My NT and my Ad user account are both shown as dbo on a database.
>> Are these Windows or SQL Server logins?
>> Since you say that two different logins are "dbo" in a database, you are saying that both are
>> sysadmin? Right? (You cannot have two logins being the same user in a database).
>> How do you determine that both are dbo? What tools/commands do you use to determine this?
>> Or are you saying that both are in the db_owner role in the database? That is a different thing
>> from
>> being the dbo of a database.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
>> news:5C0E66A8-2327-456A-8605-A9B13D82C158@.microsoft.com...
>> > Hi,
>> >
>> > My NT and my Ad user account are both shown as dbo on a database.
>> > However, when I create tables using either account they are shown as not
>> > being owned by dbo.
>> > Then, when I try to insert or update these tables, it says permission denied.
>> >
>> > How can this be? I'm a dbo? Even if I wern't a dbo, as I own the table I
>> > shoyuld be able to modify its data. Whats going on here?
>> >
>> > Thanks
>>|||Firestarter wrote:
> No, I can't! Thats my point. Wether I put the owner name or not, I
> canot update the table.
> That is the issue I am trying to resolve.
>
Are you specifying the dbo owner name in the create statement as Uri
suggested? If not, try it that way.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||In an attempt to avoid the confusion I am creating, this is what I've got:
--AD server, table created using an AD account which is a member of db_owner
fixed db role
create table felix1 (test varchar(10) null)
-- Shows in EM as being owned by londonfire\hedleyf, as expected
create table dbo.felix1 (test varchar(10) null)
-- Shows in EM as being owned by dbo, as expected
--
insert felix1 (test)
values (2)
--results
Server: Msg 229, Level 14, State 5, Line 1
INSERT permission denied on object 'felix1', database 'CFS_HFSRA_V3_test',
owner 'LONDONFIRE\HEDLEYF'.
insert dbo.felix1 (test)
values (2)
--results
Server: Msg 229, Level 14, State 5, Line 1
INSERT permission denied on object 'felix1', database 'CFS_HFSRA_V3_test',
owner 'dbo'.
So I am in the db_owner role, and seem unable to update a table.
What is going on?
Thanks for your continued patience...
"David Gugick" wrote:
> Firestarter wrote:
> > No, I can't! Thats my point. Wether I put the owner name or not, I
> > canot update the table.
> > That is the issue I am trying to resolve.
> >
> Are you specifying the dbo owner name in the create statement as Uri
> suggested? If not, try it that way.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Firestarter wrote:
> In an attempt to avoid the confusion I am creating, this is what I've
> got:
To avoid confusion, you should _always_ include the owner name is DDL,
DML, and SELECT statements. Try granting yourself rights to the table.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Tried that, didn't work. But I shouldn't have anyway, should I?
"David Gugick" wrote:
> Firestarter wrote:
> > In an attempt to avoid the confusion I am creating, this is what I've
> > got:
> To avoid confusion, you should _always_ include the owner name is DDL,
> DML, and SELECT statements. Try granting yourself rights to the table.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
My NT and my Ad user account are both shown as dbo on a database.
However, when I create tables using either account they are shown as not
being owned by dbo.
Then, when I try to insert or update these tables, it says permission denied.
How can this be? I'm a dbo? Even if I wern't a dbo, as I own the table I
shoyuld be able to modify its data. Whats going on here?
ThanksHi
Because these accounts are not member of sysadmin server role
Try do
CREATE TABLE dbo.Mytabale
(
blalala
)
"Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
news:5C0E66A8-2327-456A-8605-A9B13D82C158@.microsoft.com...
> Hi,
> My NT and my Ad user account are both shown as dbo on a database.
> However, when I create tables using either account they are shown as not
> being owned by dbo.
> Then, when I try to insert or update these tables, it says permission
denied.
> How can this be? I'm a dbo? Even if I wern't a dbo, as I own the table I
> shoyuld be able to modify its data. Whats going on here?
> Thanks|||Yes, I'm sure that would help create the tables as owned by dbo, but that
that is not my issue.
I still can't update or insert into these tables even though I am logging on
as a dbo.
Any ideas why not?
"Uri Dimant" wrote:
> Hi
> Because these accounts are not member of sysadmin server role
> Try do
> CREATE TABLE dbo.Mytabale
> (
> blalala
> )
> "Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
> news:5C0E66A8-2327-456A-8605-A9B13D82C158@.microsoft.com...
> > Hi,
> >
> > My NT and my Ad user account are both shown as dbo on a database.
> > However, when I create tables using either account they are shown as not
> > being owned by dbo.
> > Then, when I try to insert or update these tables, it says permission
> denied.
> >
> > How can this be? I'm a dbo? Even if I wern't a dbo, as I own the table I
> > shoyuld be able to modify its data. Whats going on here?
> >
> > Thanks
>
>|||"Firestarter" schrieb:
> Yes, I'm sure that would help create the tables as owned by dbo, but that
> that is not my issue.
> I still can't update or insert into these tables even though I am logging on
> as a dbo.
> Any ideas why not?
You can update the tables as dbo! You just have to add the ownername before
the objectname (as the dbo is not the owner of the object).
Objects that belong to the dbo can always be accessed by everybody without
the owner's name, because 'dbo' is the default owner ...|||> My NT and my Ad user account are both shown as dbo on a database.
Are these Windows or SQL Server logins?
Since you say that two different logins are "dbo" in a database, you are saying that both are
sysadmin? Right? (You cannot have two logins being the same user in a database).
How do you determine that both are dbo? What tools/commands do you use to determine this?
Or are you saying that both are in the db_owner role in the database? That is a different thing from
being the dbo of a database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
news:5C0E66A8-2327-456A-8605-A9B13D82C158@.microsoft.com...
> Hi,
> My NT and my Ad user account are both shown as dbo on a database.
> However, when I create tables using either account they are shown as not
> being owned by dbo.
> Then, when I try to insert or update these tables, it says permission denied.
> How can this be? I'm a dbo? Even if I wern't a dbo, as I own the table I
> shoyuld be able to modify its data. Whats going on here?
> Thanks|||No, I can't! Thats my point. Wether I put the owner name or not, I canot
update the table.
That is the issue I am trying to resolve.
"Christian Donner" wrote:
> "Firestarter" schrieb:
> > Yes, I'm sure that would help create the tables as owned by dbo, but that
> > that is not my issue.
> > I still can't update or insert into these tables even though I am logging on
> > as a dbo.
> > Any ideas why not?
> You can update the tables as dbo! You just have to add the ownername before
> the objectname (as the dbo is not the owner of the object).
> Objects that belong to the dbo can always be accessed by everybody without
> the owner's name, because 'dbo' is the default owner ...|||These are windows logins.
And I mean that both are in the db_owner role. Apologies for the lack of
prescsion in my post.
I am using EM to determine this,
"Tibor Karaszi" wrote:
> > My NT and my Ad user account are both shown as dbo on a database.
> Are these Windows or SQL Server logins?
> Since you say that two different logins are "dbo" in a database, you are saying that both are
> sysadmin? Right? (You cannot have two logins being the same user in a database).
> How do you determine that both are dbo? What tools/commands do you use to determine this?
> Or are you saying that both are in the db_owner role in the database? That is a different thing from
> being the dbo of a database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
> news:5C0E66A8-2327-456A-8605-A9B13D82C158@.microsoft.com...
> > Hi,
> >
> > My NT and my Ad user account are both shown as dbo on a database.
> > However, when I create tables using either account they are shown as not
> > being owned by dbo.
> > Then, when I try to insert or update these tables, it says permission denied.
> >
> > How can this be? I'm a dbo? Even if I wern't a dbo, as I own the table I
> > shoyuld be able to modify its data. Whats going on here?
> >
> > Thanks
>|||When a db_owner (who isn't dbo) creates an object, it will not be owned by dbo. It will be owned by
that persons user name in the database. You cannot change that.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
news:D4179915-0813-403B-B075-A20DAD8C312D@.microsoft.com...
> These are windows logins.
> And I mean that both are in the db_owner role. Apologies for the lack of
> prescsion in my post.
> I am using EM to determine this,
> "Tibor Karaszi" wrote:
>> > My NT and my Ad user account are both shown as dbo on a database.
>> Are these Windows or SQL Server logins?
>> Since you say that two different logins are "dbo" in a database, you are saying that both are
>> sysadmin? Right? (You cannot have two logins being the same user in a database).
>> How do you determine that both are dbo? What tools/commands do you use to determine this?
>> Or are you saying that both are in the db_owner role in the database? That is a different thing
>> from
>> being the dbo of a database.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
>> news:5C0E66A8-2327-456A-8605-A9B13D82C158@.microsoft.com...
>> > Hi,
>> >
>> > My NT and my Ad user account are both shown as dbo on a database.
>> > However, when I create tables using either account they are shown as not
>> > being owned by dbo.
>> > Then, when I try to insert or update these tables, it says permission denied.
>> >
>> > How can this be? I'm a dbo? Even if I wern't a dbo, as I own the table I
>> > shoyuld be able to modify its data. Whats going on here?
>> >
>> > Thanks
>>|||Firestarter wrote:
> No, I can't! Thats my point. Wether I put the owner name or not, I
> canot update the table.
> That is the issue I am trying to resolve.
>
Are you specifying the dbo owner name in the create statement as Uri
suggested? If not, try it that way.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||In an attempt to avoid the confusion I am creating, this is what I've got:
--AD server, table created using an AD account which is a member of db_owner
fixed db role
create table felix1 (test varchar(10) null)
-- Shows in EM as being owned by londonfire\hedleyf, as expected
create table dbo.felix1 (test varchar(10) null)
-- Shows in EM as being owned by dbo, as expected
--
insert felix1 (test)
values (2)
--results
Server: Msg 229, Level 14, State 5, Line 1
INSERT permission denied on object 'felix1', database 'CFS_HFSRA_V3_test',
owner 'LONDONFIRE\HEDLEYF'.
insert dbo.felix1 (test)
values (2)
--results
Server: Msg 229, Level 14, State 5, Line 1
INSERT permission denied on object 'felix1', database 'CFS_HFSRA_V3_test',
owner 'dbo'.
So I am in the db_owner role, and seem unable to update a table.
What is going on?
Thanks for your continued patience...
"David Gugick" wrote:
> Firestarter wrote:
> > No, I can't! Thats my point. Wether I put the owner name or not, I
> > canot update the table.
> > That is the issue I am trying to resolve.
> >
> Are you specifying the dbo owner name in the create statement as Uri
> suggested? If not, try it that way.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Firestarter wrote:
> In an attempt to avoid the confusion I am creating, this is what I've
> got:
To avoid confusion, you should _always_ include the owner name is DDL,
DML, and SELECT statements. Try granting yourself rights to the table.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Tried that, didn't work. But I shouldn't have anyway, should I?
"David Gugick" wrote:
> Firestarter wrote:
> > In an attempt to avoid the confusion I am creating, this is what I've
> > got:
> To avoid confusion, you should _always_ include the owner name is DDL,
> DML, and SELECT statements. Try granting yourself rights to the table.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
Perms on Tempdb?
Hey, All,
We need to create temp tables but when we do, we get an error that the user
doesn't have permission to the tempdb database. We are using:
CREATE TABLE #TEMP1
(COL1 INT)
...
If we do this in, say, the Northwind database, the error says something like
'Unable to create table in tempdb' and something about permission denied.
There are no permissions granted in Northwind or tempdb. We can fix the
error problem by granting "create table" on tempdb. Whenever SS restarts
all tempdb perms are lost.
Is there a reason why this happens, as well as a fix?
We're using SS2K and SP3.Temporary tables are just that. Temporary.
The user should have Public access to the database.
From Books Online:
tempdb is re-created every time SQL Server is started so the system starts
with a clean copy of the database. Because temporary tables and stored
procedures are dropped automatically on disconnect, and no connections are
active when the system is shut down, there is never anything in tempdb to
be saved from one session of SQL Server to another.
Temporary tables are automatically dropped when they go out of scope,
unless explicitly dropped using DROP TABLE:
A local temporary table created in a stored procedure is dropped
automatically when the stored procedure completes. The table can be
referenced by any nested stored procedures executed by the stored procedure
that created the table. The table cannot be referenced by the process which
called the stored procedure that created the table.
All other local temporary tables are dropped automatically at the end of
the current session.
Global temporary tables are automatically dropped when the session that
created the table ends and all other tasks have stopped referencing them.
The association between a task and a table is maintained only for the life
of a single Transact-SQL statement. This means that a global temporary
table is dropped at the completion of the last Transact-SQL statement that
was actively referencing the table when the creating session ended.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||We understood all of that.
It doesn't explain why we're getting a permissions error.
Anyone?
"Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> wrote in message
news:L5mdhtG5DHA.1988@.cpmsftngxa07.phx.gbl...
procedure
which
Crazy questions:
1. Is there a startup stored procedure that (for example) removes 'unwanted'
rights from tempdb (and other databases)?
2. Has your copy of the 'model' database been altered?
Russell Fields
"Rick" <b@.bt.net> wrote in message
news:401667d8$0$49107$8f4e7992@.newsreade
r.goldengate.net...
We need to create temp tables but when we do, we get an error that the user
doesn't have permission to the tempdb database. We are using:
CREATE TABLE #TEMP1
(COL1 INT)
...
If we do this in, say, the Northwind database, the error says something like
'Unable to create table in tempdb' and something about permission denied.
There are no permissions granted in Northwind or tempdb. We can fix the
error problem by granting "create table" on tempdb. Whenever SS restarts
all tempdb perms are lost.
Is there a reason why this happens, as well as a fix?
We're using SS2K and SP3.Temporary tables are just that. Temporary.
The user should have Public access to the database.
From Books Online:
tempdb is re-created every time SQL Server is started so the system starts
with a clean copy of the database. Because temporary tables and stored
procedures are dropped automatically on disconnect, and no connections are
active when the system is shut down, there is never anything in tempdb to
be saved from one session of SQL Server to another.
Temporary tables are automatically dropped when they go out of scope,
unless explicitly dropped using DROP TABLE:
A local temporary table created in a stored procedure is dropped
automatically when the stored procedure completes. The table can be
referenced by any nested stored procedures executed by the stored procedure
that created the table. The table cannot be referenced by the process which
called the stored procedure that created the table.
All other local temporary tables are dropped automatically at the end of
the current session.
Global temporary tables are automatically dropped when the session that
created the table ends and all other tasks have stopped referencing them.
The association between a task and a table is maintained only for the life
of a single Transact-SQL statement. This means that a global temporary
table is dropped at the completion of the last Transact-SQL statement that
was actively referencing the table when the creating session ended.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||We understood all of that.
It doesn't explain why we're getting a permissions error.
Anyone?
"Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> wrote in message
news:L5mdhtG5DHA.1988@.cpmsftngxa07.phx.gbl...
quote:
> Temporary tables are just that. Temporary.
> The user should have Public access to the database.
> From Books Online:
> tempdb is re-created every time SQL Server is started so the system starts
> with a clean copy of the database. Because temporary tables and stored
> procedures are dropped automatically on disconnect, and no connections are
> active when the system is shut down, there is never anything in tempdb to
> be saved from one session of SQL Server to another.
> Temporary tables are automatically dropped when they go out of scope,
> unless explicitly dropped using DROP TABLE:
> A local temporary table created in a stored procedure is dropped
> automatically when the stored procedure completes. The table can be
> referenced by any nested stored procedures executed by the stored
procedure
quote:
> that created the table. The table cannot be referenced by the process
which
quote:|||Rick,
> called the stored procedure that created the table.
>
> All other local temporary tables are dropped automatically at the end of
> the current session.
>
> Global temporary tables are automatically dropped when the session that
> created the table ends and all other tasks have stopped referencing them.
> The association between a task and a table is maintained only for the life
> of a single Transact-SQL statement. This means that a global temporary
> table is dropped at the completion of the last Transact-SQL statement that
> was actively referencing the table when the creating session ended.
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>
Crazy questions:
1. Is there a startup stored procedure that (for example) removes 'unwanted'
rights from tempdb (and other databases)?
2. Has your copy of the 'model' database been altered?
Russell Fields
"Rick" <b@.bt.net> wrote in message
news:401667d8$0$49107$8f4e7992@.newsreade
r.goldengate.net...
quote:
> We understood all of that.
> It doesn't explain why we're getting a permissions error.
> Anyone?
>
> "Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> wrote in message
> news:L5mdhtG5DHA.1988@.cpmsftngxa07.phx.gbl...
starts[QUOTE]
are[QUOTE]
to[QUOTE]
> procedure
> which
them.[QUOTE]
life[QUOTE]
that[QUOTE]
rights.[QUOTE]
>
Monday, March 26, 2012
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:
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 with sql server tables and access
Hello,
I need some help with implenting the following:
I recently migrated from access to sql server and i now i want to use
maintainable permissions on my tables, views, etc. The access database will
serve as a front-end.
I've created for testing purposes an testaccount with only a public role to
access to my database.
Now the hard part is when i want users to select and manipulate the data
through views and stored procedures.I want only permissions set on views and
stored procedures. The reason for this is because i don't want users to get
the data directly from tables by means of linking or importing them to
access
or other databases. Only views and stored procedures can be used.
Unfortunelately it doesn't work how i wanted to. When i open a view which is
linked in access as a table, i'm getting a message that the underlying table
has not the appropiate permissions.
Now there should be a way to apply a maintainable security, so if i could
have some advice and maybe an example on this matter i would be very
thankful.Create the view with the WITH VIEW_METADATA option, which will allow
users to use the view to update data. Without it, permissions on the
base tables are required. See the CREATE VIEW topic in SQL BooksOnline
for more information. If you put a Profiler trace on the Access-SQLS
app, you can see the exact calls that are being made. This will help
you troubleshoot future issues.
--Mary
On Fri, 13 Aug 2004 20:19:28 +0200, "Ezekil" <ezekil@.lycios.nl>
wrote:
>Hello,
>I need some help with implenting the following:
>I recently migrated from access to sql server and i now i want to use
>maintainable permissions on my tables, views, etc. The access database will
>serve as a front-end.
>I've created for testing purposes an testaccount with only a public role to
>access to my database.
>Now the hard part is when i want users to select and manipulate the data
>through views and stored procedures.I want only permissions set on views an
d
>stored procedures. The reason for this is because i don't want users to get
>the data directly from tables by means of linking or importing them to
>access
>or other databases. Only views and stored procedures can be used.
>Unfortunelately it doesn't work how i wanted to. When i open a view which i
s
>linked in access as a table, i'm getting a message that the underlying tabl
e
>has not the appropiate permissions.
>Now there should be a way to apply a maintainable security, so if i could
>have some advice and maybe an example on this matter i would be very
>thankful.
>|||Do you have an example? BOL is not very clear to me.
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:k37sh0hnmit3dfkgj0hk7tn7pi8cdem6g9@.
4ax.com...
> Create the view with the WITH VIEW_METADATA option, which will allow
> users to use the view to update data. Without it, permissions on the
> base tables are required. See the CREATE VIEW topic in SQL BooksOnline
> for more information. If you put a Profiler trace on the Access-SQLS
> app, you can see the exact calls that are being made. This will help
> you troubleshoot future issues.
> --Mary
> On Fri, 13 Aug 2004 20:19:28 +0200, "Ezekil" <ezekil@.lycios.nl>
> wrote:
>
will[vbcol=seagreen]
to[vbcol=seagreen]
and[vbcol=seagreen]
get[vbcol=seagreen]
is[vbcol=seagreen]
table[vbcol=seagreen]
>|||You've asked this same question in comp.databases.ms-sqlserver. Please
don't post the same question independently to multiple groups as this causes
duplication of effort.
Here's the example I posted to that thread:
CREATE TABLE dbo.MyTable
(
Col1 int NOT NULL,
Col2 int NOT NULL
)
GO
CREATE VIEW dbo.MyView
WITH VIEW_METADATA
AS
SELECT Col1
FROM dbo.MyTable
GO
GRANT SELECT ON MyView TO MyRole
GO
Hope this helps.
Dan Guzman
SQL Server MVP
I need some help with implenting the following:
I recently migrated from access to sql server and i now i want to use
maintainable permissions on my tables, views, etc. The access database will
serve as a front-end.
I've created for testing purposes an testaccount with only a public role to
access to my database.
Now the hard part is when i want users to select and manipulate the data
through views and stored procedures.I want only permissions set on views and
stored procedures. The reason for this is because i don't want users to get
the data directly from tables by means of linking or importing them to
access
or other databases. Only views and stored procedures can be used.
Unfortunelately it doesn't work how i wanted to. When i open a view which is
linked in access as a table, i'm getting a message that the underlying table
has not the appropiate permissions.
Now there should be a way to apply a maintainable security, so if i could
have some advice and maybe an example on this matter i would be very
thankful.Create the view with the WITH VIEW_METADATA option, which will allow
users to use the view to update data. Without it, permissions on the
base tables are required. See the CREATE VIEW topic in SQL BooksOnline
for more information. If you put a Profiler trace on the Access-SQLS
app, you can see the exact calls that are being made. This will help
you troubleshoot future issues.
--Mary
On Fri, 13 Aug 2004 20:19:28 +0200, "Ezekil" <ezekil@.lycios.nl>
wrote:
>Hello,
>I need some help with implenting the following:
>I recently migrated from access to sql server and i now i want to use
>maintainable permissions on my tables, views, etc. The access database will
>serve as a front-end.
>I've created for testing purposes an testaccount with only a public role to
>access to my database.
>Now the hard part is when i want users to select and manipulate the data
>through views and stored procedures.I want only permissions set on views an
d
>stored procedures. The reason for this is because i don't want users to get
>the data directly from tables by means of linking or importing them to
>access
>or other databases. Only views and stored procedures can be used.
>Unfortunelately it doesn't work how i wanted to. When i open a view which i
s
>linked in access as a table, i'm getting a message that the underlying tabl
e
>has not the appropiate permissions.
>Now there should be a way to apply a maintainable security, so if i could
>have some advice and maybe an example on this matter i would be very
>thankful.
>|||Do you have an example? BOL is not very clear to me.
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:k37sh0hnmit3dfkgj0hk7tn7pi8cdem6g9@.
4ax.com...
> Create the view with the WITH VIEW_METADATA option, which will allow
> users to use the view to update data. Without it, permissions on the
> base tables are required. See the CREATE VIEW topic in SQL BooksOnline
> for more information. If you put a Profiler trace on the Access-SQLS
> app, you can see the exact calls that are being made. This will help
> you troubleshoot future issues.
> --Mary
> On Fri, 13 Aug 2004 20:19:28 +0200, "Ezekil" <ezekil@.lycios.nl>
> wrote:
>
will[vbcol=seagreen]
to[vbcol=seagreen]
and[vbcol=seagreen]
get[vbcol=seagreen]
is[vbcol=seagreen]
table[vbcol=seagreen]
>|||You've asked this same question in comp.databases.ms-sqlserver. Please
don't post the same question independently to multiple groups as this causes
duplication of effort.
Here's the example I posted to that thread:
CREATE TABLE dbo.MyTable
(
Col1 int NOT NULL,
Col2 int NOT NULL
)
GO
CREATE VIEW dbo.MyView
WITH VIEW_METADATA
AS
SELECT Col1
FROM dbo.MyTable
GO
GRANT SELECT ON MyView TO MyRole
GO
Hope this helps.
Dan Guzman
SQL Server MVP
permissions with sql server tables
Hello,
I need some help with implenting the following:
I recently migrated from access to sql server and i now i want to use
maintainable permissions on my tables, views, etc. The access database will
serve as a front-end.
I've created for testing purposes an testaccount with only a public role to
access to my database.
Now the hard part is when i want users to select and manipulate the data
through views and stored procedures.I want only permissions set on views and
stored procedures. The reason for this is because i don't want users to get
the data directly from tables by means of linking or importing them to
access
or other databases. Only views and stored procedures can be used.
Unfortunelately it doesn't work how i wanted to. When i open a view which is
linked in access as a table, i'm getting a message that the underlying table
has not the appropiate permissions.
Now there should be a way to apply a maintainable security, so if i could
have some advice and maybe an example on this matter i would be very
thankful.Try creating the view with the VIEW_METADATA option. This way, Access will
use view meta data instead of meta data from the underlying base tables.
See CREATE VIEW in the Books Online for more information.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ezekil" <ezekil@.lycos.com> wrote in message
news:411d05d7$0$195$cd19a363@.news.wanadoo.nl...
> Hello,
> I need some help with implenting the following:
> I recently migrated from access to sql server and i now i want to use
> maintainable permissions on my tables, views, etc. The access database
will
> serve as a front-end.
> I've created for testing purposes an testaccount with only a public role
to
> access to my database.
> Now the hard part is when i want users to select and manipulate the data
> through views and stored procedures.I want only permissions set on views
and
> stored procedures. The reason for this is because i don't want users to
get
> the data directly from tables by means of linking or importing them to
> access
> or other databases. Only views and stored procedures can be used.
> Unfortunelately it doesn't work how i wanted to. When i open a view which
is
> linked in access as a table, i'm getting a message that the underlying
table
> has not the appropiate permissions.
> Now there should be a way to apply a maintainable security, so if i could
> have some advice and maybe an example on this matter i would be very
> thankful.|||Hi Dan,
I've looked it up in BOL but it is not very clear. Could you provide me an
example?
Thnx
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:wxfTc.20463$9Y6.12982@.newsread1.news.pas.eart hlink.net...
> Try creating the view with the VIEW_METADATA option. This way, Access
will
> use view meta data instead of meta data from the underlying base tables.
> See CREATE VIEW in the Books Online for more information.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ezekil" <ezekil@.lycos.com> wrote in message
> news:411d05d7$0$195$cd19a363@.news.wanadoo.nl...
> > Hello,
> > I need some help with implenting the following:
> > I recently migrated from access to sql server and i now i want to use
> > maintainable permissions on my tables, views, etc. The access database
> will
> > serve as a front-end.
> > I've created for testing purposes an testaccount with only a public role
> to
> > access to my database.
> > Now the hard part is when i want users to select and manipulate the data
> > through views and stored procedures.I want only permissions set on views
> and
> > stored procedures. The reason for this is because i don't want users to
> get
> > the data directly from tables by means of linking or importing them to
> > access
> > or other databases. Only views and stored procedures can be used.
> > Unfortunelately it doesn't work how i wanted to. When i open a view
which
> is
> > linked in access as a table, i'm getting a message that the underlying
> table
> > has not the appropiate permissions.
> > Now there should be a way to apply a maintainable security, so if i
could
> > have some advice and maybe an example on this matter i would be very
> > thankful.|||Here's a simple example:
CREATE TABLE dbo.MyTable
(
Col1 int NOT NULL,
Col2 int NOT NULL
)
GO
CREATE VIEW dbo.MyView
WITH VIEW_METADATA
AS
SELECT Col1
FROM dbo.MyTable
GO
GRANT SELECT ON MyView TO MyRole
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ezekil" <ezekil@.lycos.com> wrote in message
news:411df325$0$80325$a344fe98@.news.wanadoo.nl...
> Hi Dan,
> I've looked it up in BOL but it is not very clear. Could you provide me
an
> example?
> Thnx
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:wxfTc.20463$9Y6.12982@.newsread1.news.pas.eart hlink.net...
> > Try creating the view with the VIEW_METADATA option. This way, Access
> will
> > use view meta data instead of meta data from the underlying base tables.
> > See CREATE VIEW in the Books Online for more information.
> > --
> > Hope this helps.
> > Dan Guzman
> > SQL Server MVP
> > "Ezekil" <ezekil@.lycos.com> wrote in message
> > news:411d05d7$0$195$cd19a363@.news.wanadoo.nl...
> > > Hello,
> > > > I need some help with implenting the following:
> > > > I recently migrated from access to sql server and i now i want to use
> > > maintainable permissions on my tables, views, etc. The access database
> > will
> > > serve as a front-end.
> > > > I've created for testing purposes an testaccount with only a public
role
> > to
> > > access to my database.
> > > > Now the hard part is when i want users to select and manipulate the
data
> > > through views and stored procedures.I want only permissions set on
views
> > and
> > > stored procedures. The reason for this is because i don't want users
to
> > get
> > > the data directly from tables by means of linking or importing them to
> > > access
> > > or other databases. Only views and stored procedures can be used.
> > > > Unfortunelately it doesn't work how i wanted to. When i open a view
> which
> > is
> > > linked in access as a table, i'm getting a message that the underlying
> > table
> > > has not the appropiate permissions.
> > > > Now there should be a way to apply a maintainable security, so if i
> could
> > > have some advice and maybe an example on this matter i would be very
> > > thankful.
> >
Permissions with sp
Hi,
I programmed a Sp, which I gave permissions to some users to execute,
nevertheless, inside the code makes inserts and updates to tables where they
only have select permissions. So whenever they execute them a error message
is produced. How can i turnaround this. I want the sp to actually write in
some tables which they only have select permissions. Is there a solution.
Thanks
--
Carlos DiasHi,
Execute permission on the SP for that user should be fine to Insert or
Delete or Update.
GRANT EXEC ON SPNAME TO Username
Thanks
Hari
SQL Server MVP
"Carlos Dias" <CarlosDias@.discussions.microsoft.com> wrote in message
news:1801CAC0-5691-4146-B63C-7E2E99E87405@.microsoft.com...
> Hi,
> I programmed a Sp, which I gave permissions to some users to execute,
> nevertheless, inside the code makes inserts and updates to tables where
> they
> only have select permissions. So whenever they execute them a error
> message
> is produced. How can i turnaround this. I want the sp to actually write in
> some tables which they only have select permissions. Is there a solution.
> Thanks
> --
> Carlos Dias|||It would help us better assist you if you could include table DDL, and the
entire stored procedure code. Without this effort from you, we are just
playing guessing games.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Carlos Dias" <CarlosDias@.discussions.microsoft.com> wrote in message
news:1801CAC0-5691-4146-B63C-7E2E99E87405@.microsoft.com...
> Hi,
> I programmed a Sp, which I gave permissions to some users to execute,
> nevertheless, inside the code makes inserts and updates to tables where
> they
> only have select permissions. So whenever they execute them a error
> message
> is produced. How can i turnaround this. I want the sp to actually write in
> some tables which they only have select permissions. Is there a solution.
> Thanks
> --
> Carlos Dias|||Users do not need any permissions on tables used in a stored procedure as
long as:
1) all objects are owned by the same user (SQL 2000) or have same schema
owner (SQL 2005)
2) you do not use dynamic SQL
This behavior is known as ownership chaining. See the Books Online for more
information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Carlos Dias" <CarlosDias@.discussions.microsoft.com> wrote in message
news:1801CAC0-5691-4146-B63C-7E2E99E87405@.microsoft.com...
> Hi,
> I programmed a Sp, which I gave permissions to some users to execute,
> nevertheless, inside the code makes inserts and updates to tables where
> they
> only have select permissions. So whenever they execute them a error
> message
> is produced. How can i turnaround this. I want the sp to actually write in
> some tables which they only have select permissions. Is there a solution.
> Thanks
> --
> Carlos Dias
I programmed a Sp, which I gave permissions to some users to execute,
nevertheless, inside the code makes inserts and updates to tables where they
only have select permissions. So whenever they execute them a error message
is produced. How can i turnaround this. I want the sp to actually write in
some tables which they only have select permissions. Is there a solution.
Thanks
--
Carlos DiasHi,
Execute permission on the SP for that user should be fine to Insert or
Delete or Update.
GRANT EXEC ON SPNAME TO Username
Thanks
Hari
SQL Server MVP
"Carlos Dias" <CarlosDias@.discussions.microsoft.com> wrote in message
news:1801CAC0-5691-4146-B63C-7E2E99E87405@.microsoft.com...
> Hi,
> I programmed a Sp, which I gave permissions to some users to execute,
> nevertheless, inside the code makes inserts and updates to tables where
> they
> only have select permissions. So whenever they execute them a error
> message
> is produced. How can i turnaround this. I want the sp to actually write in
> some tables which they only have select permissions. Is there a solution.
> Thanks
> --
> Carlos Dias|||It would help us better assist you if you could include table DDL, and the
entire stored procedure code. Without this effort from you, we are just
playing guessing games.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Carlos Dias" <CarlosDias@.discussions.microsoft.com> wrote in message
news:1801CAC0-5691-4146-B63C-7E2E99E87405@.microsoft.com...
> Hi,
> I programmed a Sp, which I gave permissions to some users to execute,
> nevertheless, inside the code makes inserts and updates to tables where
> they
> only have select permissions. So whenever they execute them a error
> message
> is produced. How can i turnaround this. I want the sp to actually write in
> some tables which they only have select permissions. Is there a solution.
> Thanks
> --
> Carlos Dias|||Users do not need any permissions on tables used in a stored procedure as
long as:
1) all objects are owned by the same user (SQL 2000) or have same schema
owner (SQL 2005)
2) you do not use dynamic SQL
This behavior is known as ownership chaining. See the Books Online for more
information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Carlos Dias" <CarlosDias@.discussions.microsoft.com> wrote in message
news:1801CAC0-5691-4146-B63C-7E2E99E87405@.microsoft.com...
> Hi,
> I programmed a Sp, which I gave permissions to some users to execute,
> nevertheless, inside the code makes inserts and updates to tables where
> they
> only have select permissions. So whenever they execute them a error
> message
> is produced. How can i turnaround this. I want the sp to actually write in
> some tables which they only have select permissions. Is there a solution.
> Thanks
> --
> Carlos Dias
Friday, March 23, 2012
Permissions set to Roles disappeared
We setup a number of roles with access rights to tables in the DB. This week for some unknown reason, rights on these roles disappeared.
We had to run a restore to reset the roles in the database. After the restore, we could not reproduce the problem.
Are there scenarios to avoid that would cause rights to drop from roles and users? (These rights were gone not just hidden)
Tim.
Other than someone dropping the roles and then recreating them, I don't see an accidental way for this to happen. Dropping a role would drop all permissions associated with the role.
Thanks
Laurentiu
|||This happened to me today. Last week I'd setup specific permissions limiting a SQL server account to specific tables/procedures in tempdb. The account is used for maintaining an asp.net application's state. The permissions set are below. Today those permissions were gone. Any idea why?
use tempdb;
go
sp_grantdbaccess MyPeakASPState;
GRANT SELECT on ASPStateTempApplications to MyPeakASPState;
GRANT INSERT on ASPStateTempApplications to MyPeakASPState;
GRANT SELECT on ASPStateTempSessions to MyPeakASPState;
GRANT INSERT on ASPStateTempSessions to MyPeakASPState;
GRANT UPDATE on ASPStateTempSessions to MyPeakASPState;
GO
use aspstate
go
GRANT EXEC ON TempGetStateItem TO MyPeakASPState;
GO
GRANT EXEC ON TempGetStateItem2 TO MyPeakASPState;
GO
GRANT EXEC ON TempGetStateItemExclusive TO MyPeakASPState;
GO
GRANT EXEC ON TempGetStateItemExclusive2 TO MyPeakASPState;
GO
GRANT EXEC ON TempReleaseStateItemExclusive TO MyPeakASPState;
GO
GRANT EXEC ON TempInsertStateItemShort TO MyPeakASPState;
GO
GRANT EXEC ON TempInsertStateItemLong TO MyPeakASPState;
GO
GRANT EXEC ON TempUpdateStateItemShort TO MyPeakASPState;
GO
GRANT EXEC ON TempUpdateStateItemShortNullLong TO MyPeakASPState;
GO
GRANT EXEC ON TempUpdateStateItemLong TO MyPeakASPState;
GO
GRANT EXEC ON TempUpdateStateItemLongNullShort TO MyPeakASPState;
GO
GRANT EXEC ON TempRemoveStateItem TO MyPeakASPState;
GO
GRANT EXEC ON TempResetTimeout TO MyPeakASPState;
GO
GRANT EXEC ON DeleteExpiredSessions TO MyPeakASPState;
GO
GRANT EXEC ON DropTempTables TO MyPeakASPState;
GO
GRANT EXEC ON GetMajorVersion TO MyPeakASPState;
GO
GRANT EXEC ON CreateTempTables TO MyPeakASPState;
GO
GRANT EXEC ON ResetData TO MyPeakASPState;
GO
GRANT EXEC ON TempGetAppID TO MyPeakASPState
|||I wish I could help you. I'm interested that it happened to someone else.
The only advice I can give you - becareful not to change logins when changing security.
My problem may have occurred because I was testing security on a user.
Tim.
|||I think I know what may be happening, please correct me if my assumption is incorrect. The privileges that get lost are the ones related to tempdb, correct?
use tempdb;
go
sp_grantdbaccess MyPeakASPState;
GRANT SELECT on ASPStateTempApplications to MyPeakASPState;
GRANT INSERT on ASPStateTempApplications to MyPeakASPState;
GRANT SELECT on ASPStateTempSessions to MyPeakASPState;
GRANT INSERT on ASPStateTempSessions to MyPeakASPState;
GRANT UPDATE on ASPStateTempSessions to MyPeakASPState;
GO
Tempdb is recreated every time the server is restarted, therefore any information stored there should be consider volatile. Every time SQL Server is restarted all of the permissions listed above will be lost.
I hope this information helps,
-Raul Garcia
SDE/T
SQL Server Engine
|||Thanks, I didn't know that.
Permissions set to Roles disappeared
We setup a number of roles with access rights to tables in the DB. This week for some unknown reason, rights on these roles disappeared.
We had to run a restore to reset the roles in the database. After the restore, we could not reproduce the problem.
Are there scenarios to avoid that would cause rights to drop from roles and users? (These rights were gone not just hidden)
Tim.
Other than someone dropping the roles and then recreating them, I don't see an accidental way for this to happen. Dropping a role would drop all permissions associated with the role.
Thanks
Laurentiu
|||This happened to me today. Last week I'd setup specific permissions limiting a SQL server account to specific tables/procedures in tempdb. The account is used for maintaining an asp.net application's state. The permissions set are below. Today those permissions were gone. Any idea why?
use tempdb;
go
sp_grantdbaccess MyPeakASPState;
GRANT SELECT on ASPStateTempApplications to MyPeakASPState;
GRANT INSERT on ASPStateTempApplications to MyPeakASPState;
GRANT SELECT on ASPStateTempSessions to MyPeakASPState;
GRANT INSERT on ASPStateTempSessions to MyPeakASPState;
GRANT UPDATE on ASPStateTempSessions to MyPeakASPState;
GO
use aspstate
go
GRANT EXEC ON TempGetStateItem TO MyPeakASPState;
GO
GRANT EXEC ON TempGetStateItem2 TO MyPeakASPState;
GO
GRANT EXEC ON TempGetStateItemExclusive TO MyPeakASPState;
GO
GRANT EXEC ON TempGetStateItemExclusive2 TO MyPeakASPState;
GO
GRANT EXEC ON TempReleaseStateItemExclusive TO MyPeakASPState;
GO
GRANT EXEC ON TempInsertStateItemShort TO MyPeakASPState;
GO
GRANT EXEC ON TempInsertStateItemLong TO MyPeakASPState;
GO
GRANT EXEC ON TempUpdateStateItemShort TO MyPeakASPState;
GO
GRANT EXEC ON TempUpdateStateItemShortNullLong TO MyPeakASPState;
GO
GRANT EXEC ON TempUpdateStateItemLong TO MyPeakASPState;
GO
GRANT EXEC ON TempUpdateStateItemLongNullShort TO MyPeakASPState;
GO
GRANT EXEC ON TempRemoveStateItem TO MyPeakASPState;
GO
GRANT EXEC ON TempResetTimeout TO MyPeakASPState;
GO
GRANT EXEC ON DeleteExpiredSessions TO MyPeakASPState;
GO
GRANT EXEC ON DropTempTables TO MyPeakASPState;
GO
GRANT EXEC ON GetMajorVersion TO MyPeakASPState;
GO
GRANT EXEC ON CreateTempTables TO MyPeakASPState;
GO
GRANT EXEC ON ResetData TO MyPeakASPState;
GO
GRANT EXEC ON TempGetAppID TO MyPeakASPState
|||I wish I could help you. I'm interested that it happened to someone else.
The only advice I can give you - becareful not to change logins when changing security.
My problem may have occurred because I was testing security on a user.
Tim.
|||I think I know what may be happening, please correct me if my assumption is incorrect. The privileges that get lost are the ones related to tempdb, correct?
use tempdb;
go
sp_grantdbaccess MyPeakASPState;
GRANT SELECT on ASPStateTempApplications to MyPeakASPState;
GRANT INSERT on ASPStateTempApplications to MyPeakASPState;
GRANT SELECT on ASPStateTempSessions to MyPeakASPState;
GRANT INSERT on ASPStateTempSessions to MyPeakASPState;
GRANT UPDATE on ASPStateTempSessions to MyPeakASPState;
GO
Tempdb is recreated every time the server is restarted, therefore any information stored there should be consider volatile. Every time SQL Server is restarted all of the permissions listed above will be lost.
I hope this information helps,
-Raul Garcia
SDE/T
SQL Server Engine
|||Thanks, I didn't know that.
Permissions set to Roles disappeared
We setup a number of roles with access rights to tables in the DB. This week for some unknown reason, rights on these roles disappeared.
We had to run a restore to reset the roles in the database. After the restore, we could not reproduce the problem.
Are there scenarios to avoid that would cause rights to drop from roles and users? (These rights were gone not just hidden)
Tim.
Other than someone dropping the roles and then recreating them, I don't see an accidental way for this to happen. Dropping a role would drop all permissions associated with the role.
Thanks
Laurentiu
|||This happened to me today. Last week I'd setup specific permissions limiting a SQL server account to specific tables/procedures in tempdb. The account is used for maintaining an asp.net application's state. The permissions set are below. Today those permissions were gone. Any idea why?
use tempdb;
go
sp_grantdbaccess MyPeakASPState;
GRANT SELECT on ASPStateTempApplications to MyPeakASPState;
GRANT INSERT on ASPStateTempApplications to MyPeakASPState;
GRANT SELECT on ASPStateTempSessions to MyPeakASPState;
GRANT INSERT on ASPStateTempSessions to MyPeakASPState;
GRANT UPDATE on ASPStateTempSessions to MyPeakASPState;
GO
use aspstate
go
GRANT EXEC ON TempGetStateItem TO MyPeakASPState;
GO
GRANT EXEC ON TempGetStateItem2 TO MyPeakASPState;
GO
GRANT EXEC ON TempGetStateItemExclusive TO MyPeakASPState;
GO
GRANT EXEC ON TempGetStateItemExclusive2 TO MyPeakASPState;
GO
GRANT EXEC ON TempReleaseStateItemExclusive TO MyPeakASPState;
GO
GRANT EXEC ON TempInsertStateItemShort TO MyPeakASPState;
GO
GRANT EXEC ON TempInsertStateItemLong TO MyPeakASPState;
GO
GRANT EXEC ON TempUpdateStateItemShort TO MyPeakASPState;
GO
GRANT EXEC ON TempUpdateStateItemShortNullLong TO MyPeakASPState;
GO
GRANT EXEC ON TempUpdateStateItemLong TO MyPeakASPState;
GO
GRANT EXEC ON TempUpdateStateItemLongNullShort TO MyPeakASPState;
GO
GRANT EXEC ON TempRemoveStateItem TO MyPeakASPState;
GO
GRANT EXEC ON TempResetTimeout TO MyPeakASPState;
GO
GRANT EXEC ON DeleteExpiredSessions TO MyPeakASPState;
GO
GRANT EXEC ON DropTempTables TO MyPeakASPState;
GO
GRANT EXEC ON GetMajorVersion TO MyPeakASPState;
GO
GRANT EXEC ON CreateTempTables TO MyPeakASPState;
GO
GRANT EXEC ON ResetData TO MyPeakASPState;
GO
GRANT EXEC ON TempGetAppID TO MyPeakASPState
|||I wish I could help you. I'm interested that it happened to someone else.
The only advice I can give you - becareful not to change logins when changing security.
My problem may have occurred because I was testing security on a user.
Tim.
|||I think I know what may be happening, please correct me if my assumption is incorrect. The privileges that get lost are the ones related to tempdb, correct?
use tempdb;
go
sp_grantdbaccess MyPeakASPState;
GRANT SELECT on ASPStateTempApplications to MyPeakASPState;
GRANT INSERT on ASPStateTempApplications to MyPeakASPState;
GRANT SELECT on ASPStateTempSessions to MyPeakASPState;
GRANT INSERT on ASPStateTempSessions to MyPeakASPState;
GRANT UPDATE on ASPStateTempSessions to MyPeakASPState;
GO
Tempdb is recreated every time the server is restarted, therefore any information stored there should be consider volatile. Every time SQL Server is restarted all of the permissions listed above will be lost.
I hope this information helps,
-Raul Garcia
SDE/T
SQL Server Engine
|||Thanks, I didn't know that.
sqlPermissions Question
I've got a permission question for you. In SQL Server 2000, if a user
is given a read-only role (which is select to all the tables in a db)
AND another role that allows updates to those same tables, what rights
will prevail?
Toni
*** Sent via Developersdex http://www.codecomments.com ***Rights are addtive, so additional "allow" rulwes which ensure that the user
will have a greater access to the data, except the deny roles which prevent
him regardless in how many roles he is in to allow him doing something.
HTH, Jens Suessmeyer.
"Toni" wrote:
> I've got a permission question for you. In SQL Server 2000, if a user
> is given a read-only role (which is select to all the tables in a db)
> AND another role that allows updates to those same tables, what rights
> will prevail?
> Toni
> *** Sent via Developersdex http://www.codecomments.com ***
>|||Toni
Both, unless you give them DENY permission.
"Toni" <teibner@.SQLallina.com> wrote in message
news:OQNBKc5hFHA.1248@.TK2MSFTNGP12.phx.gbl...
> I've got a permission question for you. In SQL Server 2000, if a user
> is given a read-only role (which is select to all the tables in a db)
> AND another role that allows updates to those same tables, what rights
> will prevail?
> Toni
> *** Sent via Developersdex http://www.codecomments.com ***|||Thank you for the quick response. I don't have any denies so that tells
me what I need to know.
Thanks!
Toni
*** Sent via Developersdex http://www.codecomments.com ***
is given a read-only role (which is select to all the tables in a db)
AND another role that allows updates to those same tables, what rights
will prevail?
Toni
*** Sent via Developersdex http://www.codecomments.com ***Rights are addtive, so additional "allow" rulwes which ensure that the user
will have a greater access to the data, except the deny roles which prevent
him regardless in how many roles he is in to allow him doing something.
HTH, Jens Suessmeyer.
"Toni" wrote:
> I've got a permission question for you. In SQL Server 2000, if a user
> is given a read-only role (which is select to all the tables in a db)
> AND another role that allows updates to those same tables, what rights
> will prevail?
> Toni
> *** Sent via Developersdex http://www.codecomments.com ***
>|||Toni
Both, unless you give them DENY permission.
"Toni" <teibner@.SQLallina.com> wrote in message
news:OQNBKc5hFHA.1248@.TK2MSFTNGP12.phx.gbl...
> I've got a permission question for you. In SQL Server 2000, if a user
> is given a read-only role (which is select to all the tables in a db)
> AND another role that allows updates to those same tables, what rights
> will prevail?
> Toni
> *** Sent via Developersdex http://www.codecomments.com ***|||Thank you for the quick response. I don't have any denies so that tells
me what I need to know.
Thanks!
Toni
*** Sent via Developersdex http://www.codecomments.com ***
Permissions Question
I've got a permission question for you. In SQL Server 2000, if a user
is given a read-only role (which is select to all the tables in a db)
AND another role that allows updates to those same tables, what rights
will prevail?
Toni
*** Sent via Developersdex http://www.codecomments.com ***
Rights are addtive, so additional "allow" rulwes which ensure that the user
will have a greater access to the data, except the deny roles which prevent
him regardless in how many roles he is in to allow him doing something.
HTH, Jens Suessmeyer.
"Toni" wrote:
> I've got a permission question for you. In SQL Server 2000, if a user
> is given a read-only role (which is select to all the tables in a db)
> AND another role that allows updates to those same tables, what rights
> will prevail?
> Toni
> *** Sent via Developersdex http://www.codecomments.com ***
>
|||Toni
Both, unless you give them DENY permission.
"Toni" <teibner@.SQLallina.com> wrote in message
news:OQNBKc5hFHA.1248@.TK2MSFTNGP12.phx.gbl...
> I've got a permission question for you. In SQL Server 2000, if a user
> is given a read-only role (which is select to all the tables in a db)
> AND another role that allows updates to those same tables, what rights
> will prevail?
> Toni
> *** Sent via Developersdex http://www.codecomments.com ***
|||Thank you for the quick response. I don't have any denies so that tells
me what I need to know.
Thanks!
Toni
*** Sent via Developersdex http://www.codecomments.com ***
is given a read-only role (which is select to all the tables in a db)
AND another role that allows updates to those same tables, what rights
will prevail?
Toni
*** Sent via Developersdex http://www.codecomments.com ***
Rights are addtive, so additional "allow" rulwes which ensure that the user
will have a greater access to the data, except the deny roles which prevent
him regardless in how many roles he is in to allow him doing something.
HTH, Jens Suessmeyer.
"Toni" wrote:
> I've got a permission question for you. In SQL Server 2000, if a user
> is given a read-only role (which is select to all the tables in a db)
> AND another role that allows updates to those same tables, what rights
> will prevail?
> Toni
> *** Sent via Developersdex http://www.codecomments.com ***
>
|||Toni
Both, unless you give them DENY permission.
"Toni" <teibner@.SQLallina.com> wrote in message
news:OQNBKc5hFHA.1248@.TK2MSFTNGP12.phx.gbl...
> I've got a permission question for you. In SQL Server 2000, if a user
> is given a read-only role (which is select to all the tables in a db)
> AND another role that allows updates to those same tables, what rights
> will prevail?
> Toni
> *** Sent via Developersdex http://www.codecomments.com ***
|||Thank you for the quick response. I don't have any denies so that tells
me what I need to know.
Thanks!
Toni
*** Sent via Developersdex http://www.codecomments.com ***
Permissions Question
I've got a permission question for you. In SQL Server 2000, if a user
is given a read-only role (which is select to all the tables in a db)
AND another role that allows updates to those same tables, what rights
will prevail?
Toni
*** Sent via Developersdex http://www.developersdex.com ***Rights are addtive, so additional "allow" rulwes which ensure that the user
will have a greater access to the data, except the deny roles which prevent
him regardless in how many roles he is in to allow him doing something.
HTH, Jens Suessmeyer.
"Toni" wrote:
> I've got a permission question for you. In SQL Server 2000, if a user
> is given a read-only role (which is select to all the tables in a db)
> AND another role that allows updates to those same tables, what rights
> will prevail?
> Toni
> *** Sent via Developersdex http://www.developersdex.com ***
>|||Toni
Both, unless you give them DENY permission.
"Toni" <teibner@.SQLallina.com> wrote in message
news:OQNBKc5hFHA.1248@.TK2MSFTNGP12.phx.gbl...
> I've got a permission question for you. In SQL Server 2000, if a user
> is given a read-only role (which is select to all the tables in a db)
> AND another role that allows updates to those same tables, what rights
> will prevail?
> Toni
> *** Sent via Developersdex http://www.developersdex.com ***
is given a read-only role (which is select to all the tables in a db)
AND another role that allows updates to those same tables, what rights
will prevail?
Toni
*** Sent via Developersdex http://www.developersdex.com ***Rights are addtive, so additional "allow" rulwes which ensure that the user
will have a greater access to the data, except the deny roles which prevent
him regardless in how many roles he is in to allow him doing something.
HTH, Jens Suessmeyer.
"Toni" wrote:
> I've got a permission question for you. In SQL Server 2000, if a user
> is given a read-only role (which is select to all the tables in a db)
> AND another role that allows updates to those same tables, what rights
> will prevail?
> Toni
> *** Sent via Developersdex http://www.developersdex.com ***
>|||Toni
Both, unless you give them DENY permission.
"Toni" <teibner@.SQLallina.com> wrote in message
news:OQNBKc5hFHA.1248@.TK2MSFTNGP12.phx.gbl...
> I've got a permission question for you. In SQL Server 2000, if a user
> is given a read-only role (which is select to all the tables in a db)
> AND another role that allows updates to those same tables, what rights
> will prevail?
> Toni
> *** Sent via Developersdex http://www.developersdex.com ***
Wednesday, March 21, 2012
Permissions Problem
I have got a peculiar problem. I am not able to query my tables without db username prefix like username.tablename. Worse, I have logged in as username.
Any ideas on what could be the problem?
Now I have to give queries like select * from celia.orders instead of just select * from orders.
Thank you.
Celiacan't u use the
use databasename
select * from table
if it was permission, you probably won't be able to see the tables.
Any ideas on what could be the problem?
Now I have to give queries like select * from celia.orders instead of just select * from orders.
Thank you.
Celiacan't u use the
use databasename
select * from table
if it was permission, you probably won't be able to see the tables.
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
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 stored procedures & tables
At one client site, the DB server has 2 databases, that of my application
and that for another application. The client's consultant has added a
stored procedure which I am to access.
Signed onto my application's DB, I run the query:
Exec OtherDB.dbo.MyStoredProc Arg1, Arg2, Arg3
I did get an error saying my user wasn't valid on the other DB so I added it
& granted it Execute permissions on MyStoredProc. Now I get error messages
saying:
SELECT permission denied on object 'OTHERTABLE', database 'OtherDB', owner
'OtherUser'
I thought having the right to execute the stored procedure, I shouldn't need
explicit rights to the tables from which it selects.
What do I need to do here? Short of granting myself rights to a bunch of
tables in the other DB?
Thanks.
Daniel Wilson
Senior Software Solutions Developer
Embtrak Development Team
http://www.Embtrak.com
DVBrown CompanyI'm not sure what the error with OtherOwner is or if you
ownership chains are intact. Even if they are, with SP3,
cross db ownership chains were introduced. They are off by
default for user databases. You can find more information in
the following article:
INF: Cross-Database Ownership Chaining Behavior Changes in
SQL Server 2000 Service Pack 3
http://support.microsoft.com/?id=810474
There is also information in the updated version of books
online under:
Cross DB Ownership Chaining
Using Ownership Chains
or online at:
http://msdn.microsoft.com/library/e...config_8d7m.asp
http://msdn.microsoft.com/library/e...curity_4iyb.asp
-Sue
On Wed, 23 Feb 2005 18:53:53 -0500, "Daniel Wilson"
<d.wilson@.embtrak.com> wrote:
>At one client site, the DB server has 2 databases, that of my application
>and that for another application. The client's consultant has added a
>stored procedure which I am to access.
>Signed onto my application's DB, I run the query:
>Exec OtherDB.dbo.MyStoredProc Arg1, Arg2, Arg3
>I did get an error saying my user wasn't valid on the other DB so I added i
t
>& granted it Execute permissions on MyStoredProc. Now I get error messages
>saying:
>SELECT permission denied on object 'OTHERTABLE', database 'OtherDB', owner
>'OtherUser'
>I thought having the right to execute the stored procedure, I shouldn't nee
d
>explicit rights to the tables from which it selects.
>What do I need to do here? Short of granting myself rights to a bunch of
>tables in the other DB?
>Thanks.|||Thanks, Sue.
Those, especially the last link, explain the problem. This underscores the
recommendation to let DBO own all objects. Since that's not the case in this
DB, I have to give my user explicit permissions on each view & table.
dwilson
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:14gq1111ktsv5nmuli5oi013ahtnqbvudr@.
4ax.com...
> I'm not sure what the error with OtherOwner is or if you
> ownership chains are intact. Even if they are, with SP3,
> cross db ownership chains were introduced. They are off by
> default for user databases. You can find more information in
> the following article:
> INF: Cross-Database Ownership Chaining Behavior Changes in
> SQL Server 2000 Service Pack 3
> http://support.microsoft.com/?id=810474
> There is also information in the updated version of books
> online under:
> Cross DB Ownership Chaining
> Using Ownership Chains
> or online at:
> http://msdn.microsoft.com/library/e...config_8d7m.asp
> http://msdn.microsoft.com/library/e...curity_4iyb.asp
> -Sue
> On Wed, 23 Feb 2005 18:53:53 -0500, "Daniel Wilson"
> <d.wilson@.embtrak.com> wrote:
>
it[vbcol=seagreen]
messages[vbcol=seagreen]
owner[vbcol=seagreen]
need[vbcol=seagreen]
>|||I feel that the procedure you execute first have only static query and the
second query has dynamic query, and the user you are using is supplied with
execute permission alone at this senario if you are trying to execute the
procedre with dynamic sql it will not work and will thro a error as SELECT
permission denied on object 'table name', database 'database name', owner
'owner name'. So, get the Select permission will solve the problem or modify
the sp by avoiding dynamic query.
"Daniel Wilson" wrote:
> Thanks, Sue.
> Those, especially the last link, explain the problem. This underscores th
e
> recommendation to let DBO own all objects. Since that's not the case in th
is
> DB, I have to give my user explicit permissions on each view & table.
> dwilson
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:14gq1111ktsv5nmuli5oi013ahtnqbvudr@.
4ax.com...
> it
> messages
> owner
> need
>
>
and that for another application. The client's consultant has added a
stored procedure which I am to access.
Signed onto my application's DB, I run the query:
Exec OtherDB.dbo.MyStoredProc Arg1, Arg2, Arg3
I did get an error saying my user wasn't valid on the other DB so I added it
& granted it Execute permissions on MyStoredProc. Now I get error messages
saying:
SELECT permission denied on object 'OTHERTABLE', database 'OtherDB', owner
'OtherUser'
I thought having the right to execute the stored procedure, I shouldn't need
explicit rights to the tables from which it selects.
What do I need to do here? Short of granting myself rights to a bunch of
tables in the other DB?
Thanks.
Daniel Wilson
Senior Software Solutions Developer
Embtrak Development Team
http://www.Embtrak.com
DVBrown CompanyI'm not sure what the error with OtherOwner is or if you
ownership chains are intact. Even if they are, with SP3,
cross db ownership chains were introduced. They are off by
default for user databases. You can find more information in
the following article:
INF: Cross-Database Ownership Chaining Behavior Changes in
SQL Server 2000 Service Pack 3
http://support.microsoft.com/?id=810474
There is also information in the updated version of books
online under:
Cross DB Ownership Chaining
Using Ownership Chains
or online at:
http://msdn.microsoft.com/library/e...config_8d7m.asp
http://msdn.microsoft.com/library/e...curity_4iyb.asp
-Sue
On Wed, 23 Feb 2005 18:53:53 -0500, "Daniel Wilson"
<d.wilson@.embtrak.com> wrote:
>At one client site, the DB server has 2 databases, that of my application
>and that for another application. The client's consultant has added a
>stored procedure which I am to access.
>Signed onto my application's DB, I run the query:
>Exec OtherDB.dbo.MyStoredProc Arg1, Arg2, Arg3
>I did get an error saying my user wasn't valid on the other DB so I added i
t
>& granted it Execute permissions on MyStoredProc. Now I get error messages
>saying:
>SELECT permission denied on object 'OTHERTABLE', database 'OtherDB', owner
>'OtherUser'
>I thought having the right to execute the stored procedure, I shouldn't nee
d
>explicit rights to the tables from which it selects.
>What do I need to do here? Short of granting myself rights to a bunch of
>tables in the other DB?
>Thanks.|||Thanks, Sue.
Those, especially the last link, explain the problem. This underscores the
recommendation to let DBO own all objects. Since that's not the case in this
DB, I have to give my user explicit permissions on each view & table.
dwilson
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:14gq1111ktsv5nmuli5oi013ahtnqbvudr@.
4ax.com...
> I'm not sure what the error with OtherOwner is or if you
> ownership chains are intact. Even if they are, with SP3,
> cross db ownership chains were introduced. They are off by
> default for user databases. You can find more information in
> the following article:
> INF: Cross-Database Ownership Chaining Behavior Changes in
> SQL Server 2000 Service Pack 3
> http://support.microsoft.com/?id=810474
> There is also information in the updated version of books
> online under:
> Cross DB Ownership Chaining
> Using Ownership Chains
> or online at:
> http://msdn.microsoft.com/library/e...config_8d7m.asp
> http://msdn.microsoft.com/library/e...curity_4iyb.asp
> -Sue
> On Wed, 23 Feb 2005 18:53:53 -0500, "Daniel Wilson"
> <d.wilson@.embtrak.com> wrote:
>
it[vbcol=seagreen]
messages[vbcol=seagreen]
owner[vbcol=seagreen]
need[vbcol=seagreen]
>|||I feel that the procedure you execute first have only static query and the
second query has dynamic query, and the user you are using is supplied with
execute permission alone at this senario if you are trying to execute the
procedre with dynamic sql it will not work and will thro a error as SELECT
permission denied on object 'table name', database 'database name', owner
'owner name'. So, get the Select permission will solve the problem or modify
the sp by avoiding dynamic query.
"Daniel Wilson" wrote:
> Thanks, Sue.
> Those, especially the last link, explain the problem. This underscores th
e
> recommendation to let DBO own all objects. Since that's not the case in th
is
> DB, I have to give my user explicit permissions on each view & table.
> dwilson
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:14gq1111ktsv5nmuli5oi013ahtnqbvudr@.
4ax.com...
> it
> messages
> owner
> need
>
>
Permissions not Saving
I am having a strange problem with certain databases.
Some of the permissions I set for certain tables are not
saving. I right click on the table,move over "All tasks"
and select "Manage Permissions". I tick the permissions I
want and click ok. When I exit and go back in the
permissions I have set are gone.
Can anyone explain.Hi,
Not sure of the issue in Enterprise manager. Can you try giving the
permissions using GRANT statement from Query analyzer.
How to give prev.
GRANT SELECT on table_name to <user_name>
GRANT INSERT on table_name to <User_name>
GRANT UPDATE on table_name to <user_name>
GRANT DELETE on table_name to <User_name>
GRANT ALL on table_name to <user_name>
GRANT INSERT,SELECT on table_name to <User_name>
Procedures
GRANT EXECUTE on proc_name to <user_name>
Have a look into grant statement in books online for more previlage details
Thanks
Hari
MCDBA
"Nobster" <Norbert_Armstrong@.dub.Invesco.com> wrote in message
news:15a6001c44713$30c84b00$a301280a@.phx
.gbl...
> I am having a strange problem with certain databases.
> Some of the permissions I set for certain tables are not
> saving. I right click on the table,move over "All tasks"
> and select "Manage Permissions". I tick the permissions I
> want and click ok. When I exit and go back in the
> permissions I have set are gone.
> Can anyone explain.|||I have seen something similar before. Try testing the permissions in QA as
they may well have been correctly set. In my case when I used sp_helpprotect
or looked directly at sysprotects the permissions had been entered but EM
didn't display them correctly. Some time ago I did once 'debug' this
behaviour using profiler and posted up my findings in the replication group,
but unfortunately a search doesn't reveal them. Anyway if this corresponds
to your situation and you do profile it, please post up your findings.
Regards,
Paul Ibison
Some of the permissions I set for certain tables are not
saving. I right click on the table,move over "All tasks"
and select "Manage Permissions". I tick the permissions I
want and click ok. When I exit and go back in the
permissions I have set are gone.
Can anyone explain.Hi,
Not sure of the issue in Enterprise manager. Can you try giving the
permissions using GRANT statement from Query analyzer.
How to give prev.
GRANT SELECT on table_name to <user_name>
GRANT INSERT on table_name to <User_name>
GRANT UPDATE on table_name to <user_name>
GRANT DELETE on table_name to <User_name>
GRANT ALL on table_name to <user_name>
GRANT INSERT,SELECT on table_name to <User_name>
Procedures
GRANT EXECUTE on proc_name to <user_name>
Have a look into grant statement in books online for more previlage details
Thanks
Hari
MCDBA
"Nobster" <Norbert_Armstrong@.dub.Invesco.com> wrote in message
news:15a6001c44713$30c84b00$a301280a@.phx
.gbl...
> I am having a strange problem with certain databases.
> Some of the permissions I set for certain tables are not
> saving. I right click on the table,move over "All tasks"
> and select "Manage Permissions". I tick the permissions I
> want and click ok. When I exit and go back in the
> permissions I have set are gone.
> Can anyone explain.|||I have seen something similar before. Try testing the permissions in QA as
they may well have been correctly set. In my case when I used sp_helpprotect
or looked directly at sysprotects the permissions had been entered but EM
didn't display them correctly. Some time ago I did once 'debug' this
behaviour using profiler and posted up my findings in the replication group,
but unfortunately a search doesn't reveal them. Anyway if this corresponds
to your situation and you do profile it, please post up your findings.
Regards,
Paul Ibison
Subscribe to:
Posts (Atom)