Permission Denied Error: dbo

Showing posts with label dbo. Show all posts

Wednesday, March 28, 2012

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
>

Monday, March 26, 2012

permissions to see design view

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

Simon

Friday, March 23, 2012

Permissions Question For SQL Login Account

I have a SQL login account defined with DBO permissions on a particular
database. When this login attempts to run the sp_updatestats stored
procedure, the following error occurs: "User does not have permission to
perform this action." According to BOL, the DBO has permissions to execute
this stored procedure. Any help would be appreciated.
MACason wrote:
> I have a SQL login account defined with DBO permissions on a
> particular database. When this login attempts to run the
> sp_updatestats stored procedure, the following error occurs: "User
> does not have permission to perform this action." According to BOL,
> the DBO has permissions to execute this stored procedure. Any help
> would be appreciated.
Run this code (from sp_updatestats) and see what it returns. If it fails
then the user is not a sysadmin and not the dbo in the database. There
is only one dbo per database (they cannot be aliased as dbo I don't
think):
DECLARE @.dbsid varbinary(85)
SELECT @.dbsid = sid
FROM master.dbo.sysdatabases
WHERE name = db_name()
select @.dbsid
select suser_sid()
/*Check the user sysadmin*/
IF NOT is_srvrolemember('sysadmin') = 1 AND suser_sid() <> @.dbsid
BEGIN
RAISERROR(15247,-1,-1)
RETURN (1)
END
David Gugick
Imceda Software
www.imceda.com
|||The Books Online states:
<Excerpt href="http://links.10026.com/?link=tsqlref.chm::/ts_sp_ua-uz_14kz.htm">
Only the DBO and members of the sysadmin fixed server role can execute this
procedure.
</Excerpt>
Note that db_owner role members are not *the* 'dbo'. The database owner is
the login that owns the database.
Hope this helps.
Dan Guzman
SQL Server MVP
"MACason" <MACason@.discussions.microsoft.com> wrote in message
news:47413145-85BC-4F7C-AC60-245E011F324D@.microsoft.com...
>I have a SQL login account defined with DBO permissions on a particular
> database. When this login attempts to run the sp_updatestats stored
> procedure, the following error occurs: "User does not have permission
> to
> perform this action." According to BOL, the DBO has permissions to execute
> this stored procedure. Any help would be appreciated.

Permissions Question For SQL Login Account

I have a SQL login account defined with DBO permissions on a particular
database. When this login attempts to run the sp_updatestats stored
procedure, the following error occurs: "User does not have permission to
perform this action." According to BOL, the DBO has permissions to execute
this stored procedure. Any help would be appreciated.MACason wrote:
> I have a SQL login account defined with DBO permissions on a
> particular database. When this login attempts to run the
> sp_updatestats stored procedure, the following error occurs: "User
> does not have permission to perform this action." According to BOL,
> the DBO has permissions to execute this stored procedure. Any help
> would be appreciated.
Run this code (from sp_updatestats) and see what it returns. If it fails
then the user is not a sysadmin and not the dbo in the database. There
is only one dbo per database (they cannot be aliased as dbo I don't
think):
DECLARE @.dbsid varbinary(85)
SELECT @.dbsid = sid
FROM master.dbo.sysdatabases
WHERE name = db_name()
select @.dbsid
select suser_sid()
/*Check the user sysadmin*/
IF NOT is_srvrolemember('sysadmin') = 1 AND suser_sid() <> @.dbsid
BEGIN
RAISERROR(15247,-1,-1)
RETURN (1)
END
David Gugick
Imceda Software
www.imceda.com|||The Books Online states:
<Excerpt href="http://links.10026.com/?link=tsqlref.chm::/ts_sp_ua-uz_14kz.htm">
Only the DBO and members of the sysadmin fixed server role can execute this
procedure.
</Excerpt>
Note that db_owner role members are not *the* 'dbo'. The database owner is
the login that owns the database.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"MACason" <MACason@.discussions.microsoft.com> wrote in message
news:47413145-85BC-4F7C-AC60-245E011F324D@.microsoft.com...
>I have a SQL login account defined with DBO permissions on a particular
> database. When this login attempts to run the sp_updatestats stored
> procedure, the following error occurs: "User does not have permission
> to
> perform this action." According to BOL, the DBO has permissions to execute
> this stored procedure. Any help would be appreciated.

permissions problems with trigger script

CREATE TRIGGER trg_audit_version
ON dbo.syscomments
FOR INSERT, UPDATE, DELETE
AS
SELECT * INTO versionaudit
FROM @.@.version;
GO

SE sqlsvr_audit;
GO
--
CREATE TRIGGER trg_audit1
ON dbo.syscomments
FOR INSERT, UPDATE, DELETE
AS
SELECT * INTO audit1
FROM dbo.syscomments;
GO
--

USE sqlsvr_audit;
GO
--
CREATE TRIGGER trg_auditlogins
ON dbo.syscomments
FOR INSERT, UPDATE, DELETE
AS
SELECT * INTO auditlogins
FROM dbo.syslogins;
GO
--

USE sqlsvr_audit;
GO
--
CREATE TRIGGER trg_audit_sysobjects
ON dbo.sysobjects
FOR INSERT, UPDATE, DELETE
AS
SELECT * INTO auditsysobjects
FROM dbo.sysobjects;
GO
--
USE sqlsvr_audit;
GO
--

CREATE TRIGGER trg_audit_files
ON dbo.sysfiles
FOR INSERT, UPDATE, DELETE
AS
SELECT * INTO auditfiles
FROM dbo.sysfiles;
GO
--

USE sqlsvr_audit;
GO
--

CREATE TRIGGER trg_audit_users
ON dbo.sysusers
FOR INSERT, UPDATE, DELETE
AS
SELECT * INTO auditusers
FROM dbo.sysusers;
GO
--

USE sqlsvr_audit;
GO

I keep getting these syntax and permissions errors with MS SQL Server 2000:

Server: Msg 170, Level 15, State 1, Procedure trg_audit_version, Line 7
Line 7: Incorrect syntax near '@.@.version'.
Server: Msg 229, Level 14, State 5, Procedure trg_audit1, Line 6
CREATE TRIGGER permission denied on object 'syscomments', database 'sqlsvr_audit', owner 'dbo'.
Server: Msg 229, Level 14, State 5, Procedure trg_auditlogins, Line 6
CREATE TRIGGER permission denied on object 'syscomments', database 'sqlsvr_audit', owner 'dbo'.
Server: Msg 229, Level 14, State 5, Procedure trg_audit_sysobjects, Line 6
CREATE TRIGGER permission denied on object 'sysobjects', database 'sqlsvr_audit', owner 'dbo'.
Server: Msg 229, Level 14, State 5, Procedure trg_audit_files, Line 7
CREATE TRIGGER permission denied on object 'sysfiles', database 'sqlsvr_audit', owner 'dbo'.
Server: Msg 229, Level 14, State 5, Procedure trg_audit_users, Line 7

Can anyone help me out here and how to fix these problems with my script? Thanks!Here's what BOL says..

Note Because SQL Server does not support user-defined triggers on system tables, it is recommended that no user-defined triggers be created on system tables.|||Thanks. Besides using the SQL Profiler trace utility is there a method to custom script in T-SQL changes made to these tables ? Oracle allows one to do so and since I am fairly new to SQL Server would be great if a custom way to do this on a periodic basis for monitor security of the databases. Thanks|||SQL Address that in it's next release with the service broker

http://www.informit.com/articles/article.asp?p=327394&seqNum=5

right now the only thing you can do is to restrict access and do compares of 2 database catalogs....

Wednesday, March 21, 2012

Permissions on a Database

I have a user that need to create stored procedures but as the dbo account and not his own account so that the stored procedure is called dbo.storedprocedure and not domain\user.storedprocedure. He is a database owner but in order to have this happen I have to have him in the local Server Administrator group. What have I done wrong?

Also, he need to be able to run Enterprise Manager and SQL Ananlysis manager but I do not want him to be a local administrator but they will not start if he is just a local user. How can I accomplish it.

Thanks,

Stryder :confused:First of all, uninstall Enterprise Manager from his workstation.

All the user will need is db_owner permissions on the database. If he uses scripts, he can issue the create procedure command with the proper name (including owner) of the procedure:

create procedure [dbo].[someproc]
as
...

I do not know if this is possible in Enterprise Manager, as I never use EM to create procedures.

As for Analysis Manager, this is a bit thornier. There is a special local group on the Analysis Services Machine called Olap Administrators. Only members of this group can use Analysis Manger. The catch is that it is pretty much a binary permission. Either you are an Olap Administrator, or you are a simple user. No in between.

Hope this helps.sql

Monday, March 12, 2012

Permissions for running sp_Start_Job and xp_sqlagent_enum_jobs

Anybody know what permissions I need to give a user so
that they can execute msdb.dbo.sp_start_job and
xp_sqlagent_enum_jobs? I don't want to give them Admin
access..The user needs to own the job if you don't want them to be a
sysadmin.
-Sue
On Wed, 3 Sep 2003 08:21:05 -0700, "Rich"
<rslack@.asda.co.uk> wrote:
>Anybody know what permissions I need to give a user so
>that they can execute msdb.dbo.sp_start_job and
>xp_sqlagent_enum_jobs? I don't want to give them Admin
>access..

Wednesday, March 7, 2012

permission to create dbo.proc but not drop table

Hi group,
is there a way to create a role, assign permission to it so that it can
create procedures with dbo as owner, and not able to drop tables?
QuentinAlthough you can't create a user role for this, you can add a user to the
db_ddladmin fixed database role and then DENY those statement permissions
you don't want the user to have For example:
EXEC sp_addrolemember 'db_ddladmin' ,'MyUser'
DENY CREATE TABLE TO MyUser1
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Quentin Ran" <ab@.who.com> wrote in message
news:OxJQ8o4MEHA.1312@.TK2MSFTNGP12.phx.gbl...
> Hi group,
> is there a way to create a role, assign permission to it so that it can
> create procedures with dbo as owner, and not able to drop tables?
> Quentin
>|||Dan,
thanks for the response. I have been there -- you can still drop tables.
Quentin
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:#7xngl9MEHA.2736@.TK2MSFTNGP11.phx.gbl...
> Although you can't create a user role for this, you can add a user to the
> db_ddladmin fixed database role and then DENY those statement permissions
> you don't want the user to have For example:
> EXEC sp_addrolemember 'db_ddladmin' ,'MyUser'
> DENY CREATE TABLE TO MyUser1
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Quentin Ran" <ab@.who.com> wrote in message
> news:OxJQ8o4MEHA.1312@.TK2MSFTNGP12.phx.gbl...
> > Hi group,
> >
> > is there a way to create a role, assign permission to it so that it can
> > create procedures with dbo as owner, and not able to drop tables?
> >
> > Quentin
> >
> >
>|||> thanks for the response. I have been there -- you can still drop tables.
Sorry about that. I tested my suggestion with CREATE TABLE but not DROP
TABLE. I suggest you send this to sqlwish@.microsoft.com to present your
case for including this functionality in a future SQL Server version.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Quentin Ran" <ab@.who.com> wrote in message
news:uVpgV1DNEHA.2388@.TK2MSFTNGP09.phx.gbl...
> Dan,
> thanks for the response. I have been there -- you can still drop tables.
> Quentin
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:#7xngl9MEHA.2736@.TK2MSFTNGP11.phx.gbl...
> > Although you can't create a user role for this, you can add a user to
the
> > db_ddladmin fixed database role and then DENY those statement
permissions
> > you don't want the user to have For example:
> >
> > EXEC sp_addrolemember 'db_ddladmin' ,'MyUser'
> > DENY CREATE TABLE TO MyUser1
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Quentin Ran" <ab@.who.com> wrote in message
> > news:OxJQ8o4MEHA.1312@.TK2MSFTNGP12.phx.gbl...
> > > Hi group,
> > >
> > > is there a way to create a role, assign permission to it so that it
can
> > > create procedures with dbo as owner, and not able to drop tables?
> > >
> > > Quentin
> > >
> > >
> >
> >
>|||> I suggest you send this to sqlwish@.microsoft.com to present your
> case for including this functionality in a future SQL Server version.
Thanks Dan. Did that.
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OexgQ8DNEHA.3380@.TK2MSFTNGP11.phx.gbl...
> > thanks for the response. I have been there -- you can still drop
tables.
> Sorry about that. I tested my suggestion with CREATE TABLE but not DROP
> TABLE. I suggest you send this to sqlwish@.microsoft.com to present your
> case for including this functionality in a future SQL Server version.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Quentin Ran" <ab@.who.com> wrote in message
> news:uVpgV1DNEHA.2388@.TK2MSFTNGP09.phx.gbl...
> > Dan,
> >
> > thanks for the response. I have been there -- you can still drop
tables.
> >
> > Quentin
> >
> > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > news:#7xngl9MEHA.2736@.TK2MSFTNGP11.phx.gbl...
> > > Although you can't create a user role for this, you can add a user to
> the
> > > db_ddladmin fixed database role and then DENY those statement
> permissions
> > > you don't want the user to have For example:
> > >
> > > EXEC sp_addrolemember 'db_ddladmin' ,'MyUser'
> > > DENY CREATE TABLE TO MyUser1
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > "Quentin Ran" <ab@.who.com> wrote in message
> > > news:OxJQ8o4MEHA.1312@.TK2MSFTNGP12.phx.gbl...
> > > > Hi group,
> > > >
> > > > is there a way to create a role, assign permission to it so that it
> can
> > > > create procedures with dbo as owner, and not able to drop tables?
> > > >
> > > > Quentin
> > > >
> > > >
> > >
> > >
> >
> >
>

Saturday, February 25, 2012

Permission problem

Hi all,
I have two sp's, spA and spB and two Databases DB1 and DB2.
DB1 has spA and DB2 has spB
In spA I have a sentence exec DB2.dbo.spB
In spB, in one point, I have the sentece delete from DB1.dbo.table1
I have a user who has access to execute in both databases, DB1 and DB2 in
both sp's spA and spB, respectivly
When I run spA in DB1 I got the error message DELETE PERMISSION DENIED IN
OBJECT TABLE1
I think it is not necesary to give direct permissions to the tabla in order
to delete from the sp, because of the access to execute the sp's
So, I had to grant permissions to DELETE to the login so I can solve the
problem,
Anybody knows about that? Is it wrong my idea?
Thanks in advance!!
Permissions will be checked if an SP tries to access an object outside it's
database. So when you access a table in the other database the user will need
permissions on that object (unless you allow database object permission
chaining).
It doesn't matter that the sp is called from an sp in the other database -
it can't give permission on objects in the other db.
"Javier Rosas" wrote:

> Hi all,
> I have two sp's, spA and spB and two Databases DB1 and DB2.
> DB1 has spA and DB2 has spB
> In spA I have a sentence exec DB2.dbo.spB
> In spB, in one point, I have the sentece delete from DB1.dbo.table1
> I have a user who has access to execute in both databases, DB1 and DB2 in
> both sp's spA and spB, respectivly
> When I run spA in DB1 I got the error message DELETE PERMISSION DENIED IN
> OBJECT TABLE1
> I think it is not necesary to give direct permissions to the tabla in order
> to delete from the sp, because of the access to execute the sp's
> So, I had to grant permissions to DELETE to the login so I can solve the
> problem,
> Anybody knows about that? Is it wrong my idea?
> Thanks in advance!!

Permission problem

Hi all,
I have two sp's, spA and spB and two Databases DB1 and DB2.
DB1 has spA and DB2 has spB
In spA I have a sentence exec DB2.dbo.spB
In spB, in one point, I have the sentece delete from DB1.dbo.table1
I have a user who has access to execute in both databases, DB1 and DB2 in
both sp's spA and spB, respectivly
When I run spA in DB1 I got the error message DELETE PERMISSION DENIED IN
OBJECT TABLE1
I think it is not necesary to give direct permissions to the tabla in order
to delete from the sp, because of the access to execute the sp's
So, I had to grant permissions to DELETE to the login so I can solve the
problem,
Anybody knows about that? Is it wrong my idea?
Thanks in advance!!Permissions will be checked if an SP tries to access an object outside it's
database. So when you access a table in the other database the user will need
permissions on that object (unless you allow database object permission
chaining).
It doesn't matter that the sp is called from an sp in the other database -
it can't give permission on objects in the other db.
"Javier Rosas" wrote:
> Hi all,
> I have two sp's, spA and spB and two Databases DB1 and DB2.
> DB1 has spA and DB2 has spB
> In spA I have a sentence exec DB2.dbo.spB
> In spB, in one point, I have the sentece delete from DB1.dbo.table1
> I have a user who has access to execute in both databases, DB1 and DB2 in
> both sp's spA and spB, respectivly
> When I run spA in DB1 I got the error message DELETE PERMISSION DENIED IN
> OBJECT TABLE1
> I think it is not necesary to give direct permissions to the tabla in order
> to delete from the sp, because of the access to execute the sp's
> So, I had to grant permissions to DELETE to the login so I can solve the
> problem,
> Anybody knows about that? Is it wrong my idea?
> Thanks in advance!!

Permission problem

Hi all,
I have two sp's, spA and spB and two Databases DB1 and DB2.
DB1 has spA and DB2 has spB
In spA I have a sentence exec DB2.dbo.spB
In spB, in one point, I have the sentece delete from DB1.dbo.table1
I have a user who has access to execute in both databases, DB1 and DB2 in
both sp's spA and spB, respectivly
When I run spA in DB1 I got the error message DELETE PERMISSION DENIED IN
OBJECT TABLE1
I think it is not necesary to give direct permissions to the tabla in order
to delete from the sp, because of the access to execute the sp's
So, I had to grant permissions to DELETE to the login so I can solve the
problem,
Anybody knows about that? Is it wrong my idea?
Thanks in advance!!Permissions will be checked if an SP tries to access an object outside it's
database. So when you access a table in the other database the user will nee
d
permissions on that object (unless you allow database object permission
chaining).
It doesn't matter that the sp is called from an sp in the other database -
it can't give permission on objects in the other db.
"Javier Rosas" wrote:

> Hi all,
> I have two sp's, spA and spB and two Databases DB1 and DB2.
> DB1 has spA and DB2 has spB
> In spA I have a sentence exec DB2.dbo.spB
> In spB, in one point, I have the sentece delete from DB1.dbo.table1
> I have a user who has access to execute in both databases, DB1 and DB2 in
> both sp's spA and spB, respectivly
> When I run spA in DB1 I got the error message DELETE PERMISSION DENIED IN
> OBJECT TABLE1
> I think it is not necesary to give direct permissions to the tabla in orde
r
> to delete from the sp, because of the access to execute the sp's
> So, I had to grant permissions to DELETE to the login so I can solve the
> problem,
> Anybody knows about that? Is it wrong my idea?
> Thanks in advance!!

permission issue with tempdb works fine in SQL2000 but not SQL2005

the following SQL works fine in SQL2000 but gets a permissions error when run on SQL2005:

IF not exists (SELECT * FROM tempdb.dbo.sysindexes WHERE NAME = 'PK_tblGuidContractMove')

BEGIN

IF @.DEBUG = 1 PRINT 'airsp_CopyContracts.PK_tblGuidContractMove'

EXECUTE('ALTER TABLE #tblGuidContractMove ALTER COLUMN guidSource GUID NOT NULL')

EXECUTE('ALTER TABLE #tblGuidContractMove ALTER COLUMN guidDestination GUID NOT NULL')

EXECUTE('ALTER TABLE #tblGuidContractMove ALTER COLUMN guidContractMove GUID NOT NULL')

EXECUTE('ALTER TABLE #tblGuidContractMove WITH NOCHECK ADD

CONSTRAINT [PK_tblGuidContractMove] PRIMARY KEY CLUSTERED

(

[guidSource],

[guidDestination],

[guidContractMove]

) ON [PRIMARY]')

END

The user permissions are set the same in both 2000 and 2005 can you please explain what changed and what are the minimum permissions need for the user to be able to make these changes to the temporary table which the user created.

Moving to Transact-SQL from SSIS.

permission issue with temp db

the following SQL works fine in SQL2000 but gets a permissions error when run on SQL2005:

IF not exists (SELECT * FROM tempdb.dbo.sysindexes WHERE NAME = 'PK_tblGuidContractMove')

BEGIN

IF @.DEBUG = 1 PRINT 'airsp_CopyContracts.PK_tblGuidContractMove'

EXECUTE('ALTER TABLE #tblGuidContractMove ALTER COLUMN guidSource GUID NOT NULL')

EXECUTE('ALTER TABLE #tblGuidContractMove ALTER COLUMN guidDestination GUID NOT NULL')

EXECUTE('ALTER TABLE #tblGuidContractMove ALTER COLUMN guidContractMove GUID NOT NULL')

EXECUTE('ALTER TABLE #tblGuidContractMove WITH NOCHECK ADD

CONSTRAINT [PK_tblGuidContractMove] PRIMARY KEY CLUSTERED

(

[guidSource],

[guidDestination],

[guidContractMove]

) ON [PRIMARY]')

END

Can you post a repro and the exact error message. 'alter table' right must be granted on the table for the user to change the meta data.

Permission issue

Hello,
Some facts:
- I am using SQL Server 2000 on W2K3
- I have a SQL Login called 'Rlogic' with dbo permission rights on every
database. It is also a member of the Systems Administrators Group.
In Query Analyzer, when I run the following:-
use Rlogic_Training
go
select *
from users
I get this error:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'users'.
However, when I append 'Rlogic.' before the dbname, it works fine: -
use Rlogic_Training
go
select *
from Rlogic.users
Is there a way for me NOT to append 'Rlogic.' everytime?
Thanks,
Sameer
Message posted via http://www.droptable.com
Hello,
In SQL 2005 you can create a SYNONYM and query the Synonym. But for SQL
2005, you need to either login as object owner or you need to change the
object owner to DBO using
SP_CHANGEOBJECTOWNER system stored procedure. If the object owner is DBO you
do not want to prefix the object owner even if you login as a different
user.
Thanks
Hari
"spremji via droptable.com" <u4996@.uwe> wrote in message
news:6fd4c5051b6c0@.uwe...
> Hello,
> Some facts:
> - I am using SQL Server 2000 on W2K3
> - I have a SQL Login called 'Rlogic' with dbo permission rights on every
> database. It is also a member of the Systems Administrators Group.
> In Query Analyzer, when I run the following:-
> use Rlogic_Training
> go
> select *
> from users
>
> I get this error:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'users'.
>
> However, when I append 'Rlogic.' before the dbname, it works fine: -
> use Rlogic_Training
> go
> select *
> from Rlogic.users
> Is there a way for me NOT to append 'Rlogic.' everytime?
> Thanks,
> Sameer
> --
> Message posted via http://www.droptable.com
>
|||Hello Hari,
I tried sp_changeobjectowner but got this error:
Server: Msg 15001, Level 16, State 1, Procedure sp_changeobjectowner, Line 38
Object 'USERS' does not exist or is not a valid object for this operation.
When I see the tables in the 'Object Browser' left pane, I see them starting
with prefix 'Rlogic.' and there are literally hundreds of these tables and
there are few tables that start with 'dbo.' which I can query them without
any problem.
Is there a way to reflect this change to all these tables?
Thanks.
Message posted via http://www.droptable.com
|||Sorry, ignore the above error but the exact error I got was: -
Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 47
The proposed new database owner is already a user in the database.
Message posted via http://www.droptable.com
|||Hi
Probably you cereated this table before the login was added to SysAdmin
server role, right?
Well , create a new table ,move the data from the old one to the new one,
drop the old table then rename the new table with original name
"spremji via droptable.com" <u4996@.uwe> wrote in message
news:6fd4c5051b6c0@.uwe...
> Hello,
> Some facts:
> - I am using SQL Server 2000 on W2K3
> - I have a SQL Login called 'Rlogic' with dbo permission rights on every
> database. It is also a member of the Systems Administrators Group.
> In Query Analyzer, when I run the following:-
> use Rlogic_Training
> go
> select *
> from users
>
> I get this error:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'users'.
>
> However, when I append 'Rlogic.' before the dbname, it works fine: -
> use Rlogic_Training
> go
> select *
> from Rlogic.users
> Is there a way for me NOT to append 'Rlogic.' everytime?
> Thanks,
> Sameer
> --
> Message posted via http://www.droptable.com
>
|||Hello,
PLease do not execute SP_CHANGEDBOWNER. You may need to use
SP_CHANGEOBJECTOWNER system procedure to change the object owner to DBO.
Use the below sample:-
Use <DBNAME>
GO
exec sp_changeobjectowner 'Rlogic.Users', 'dbo'
Thanks
Hari
"spremji via droptable.com" <u4996@.uwe> wrote in message
news:6fd64dd4931f0@.uwe...
> Sorry, ignore the above error but the exact error I got was: -
> Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 47
> The proposed new database owner is already a user in the database.
> --
> Message posted via http://www.droptable.com
>
|||Uri,
No, the tables have been previously created by the Software installer and I
don't have the luxury to recreate the tables.
I have a db user 'Rlogic' which is associated with SQL Login 'Rlogic' as well
and they are both associated fine.
For some reason, the user 'Rlogic' is not able to query the tables despite
being the db owner.
Any help is appreciated as clients are waiting to work and no application is
able to login.
Thanks,
Sameer
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1
|||OK....phew, problem was solved because the 'Rlogic' login was a member of
'Systems Administrators' group.
Hence, this login would log into the database with 'dbo' rights rather than
"user" named 'Rlogic'.
Thanks for the responses

Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1

Permission issue

Hello,
Some facts:
- I am using SQL Server 2000 on W2K3
- I have a SQL Login called 'Rlogic' with dbo permission rights on every
database. It is also a member of the Systems Administrators Group.
In Query Analyzer, when I run the following:-
use Rlogic_Training
go
select *
from users
I get this error:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'users'.
---
However, when I append 'Rlogic.' before the dbname, it works fine: -
use Rlogic_Training
go
select *
from Rlogic.users
Is there a way for me NOT to append 'Rlogic.' everytime?
Thanks,
Sameer
--
Message posted via http://www.sqlmonster.comHello,
In SQL 2005 you can create a SYNONYM and query the Synonym. But for SQL
2005, you need to either login as object owner or you need to change the
object owner to DBO using
SP_CHANGEOBJECTOWNER system stored procedure. If the object owner is DBO you
do not want to prefix the object owner even if you login as a different
user.
Thanks
Hari
"spremji via SQLMonster.com" <u4996@.uwe> wrote in message
news:6fd4c5051b6c0@.uwe...
> Hello,
> Some facts:
> - I am using SQL Server 2000 on W2K3
> - I have a SQL Login called 'Rlogic' with dbo permission rights on every
> database. It is also a member of the Systems Administrators Group.
> In Query Analyzer, when I run the following:-
> use Rlogic_Training
> go
> select *
> from users
>
> I get this error:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'users'.
> ---
>
> However, when I append 'Rlogic.' before the dbname, it works fine: -
> use Rlogic_Training
> go
> select *
> from Rlogic.users
> Is there a way for me NOT to append 'Rlogic.' everytime?
> Thanks,
> Sameer
> --
> Message posted via http://www.sqlmonster.com
>|||Hello Hari,
I tried sp_changeobjectowner but got this error:
Server: Msg 15001, Level 16, State 1, Procedure sp_changeobjectowner, Line 38
Object 'USERS' does not exist or is not a valid object for this operation.
When I see the tables in the 'Object Browser' left pane, I see them starting
with prefix 'Rlogic.' and there are literally hundreds of these tables and
there are few tables that start with 'dbo.' which I can query them without
any problem.
Is there a way to reflect this change to all these tables?
Thanks.
--
Message posted via http://www.sqlmonster.com|||Sorry, ignore the above error but the exact error I got was: -
Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 47
The proposed new database owner is already a user in the database.
--
Message posted via http://www.sqlmonster.com|||Hi
Probably you cereated this table before the login was added to SysAdmin
server role, right?
Well , create a new table ,move the data from the old one to the new one,
drop the old table then rename the new table with original name
"spremji via SQLMonster.com" <u4996@.uwe> wrote in message
news:6fd4c5051b6c0@.uwe...
> Hello,
> Some facts:
> - I am using SQL Server 2000 on W2K3
> - I have a SQL Login called 'Rlogic' with dbo permission rights on every
> database. It is also a member of the Systems Administrators Group.
> In Query Analyzer, when I run the following:-
> use Rlogic_Training
> go
> select *
> from users
>
> I get this error:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'users'.
> ---
>
> However, when I append 'Rlogic.' before the dbname, it works fine: -
> use Rlogic_Training
> go
> select *
> from Rlogic.users
> Is there a way for me NOT to append 'Rlogic.' everytime?
> Thanks,
> Sameer
> --
> Message posted via http://www.sqlmonster.com
>|||Hello,
PLease do not execute SP_CHANGEDBOWNER. You may need to use
SP_CHANGEOBJECTOWNER system procedure to change the object owner to DBO.
Use the below sample:-
Use <DBNAME>
GO
exec sp_changeobjectowner 'Rlogic.Users', 'dbo'
Thanks
Hari
"spremji via SQLMonster.com" <u4996@.uwe> wrote in message
news:6fd64dd4931f0@.uwe...
> Sorry, ignore the above error but the exact error I got was: -
> Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 47
> The proposed new database owner is already a user in the database.
> --
> Message posted via http://www.sqlmonster.com
>|||Uri,
No, the tables have been previously created by the Software installer and I
don't have the luxury to recreate the tables.
I have a db user 'Rlogic' which is associated with SQL Login 'Rlogic' as well
and they are both associated fine.
For some reason, the user 'Rlogic' is not able to query the tables despite
being the db owner.
Any help is appreciated as clients are waiting to work and no application is
able to login.
Thanks,
Sameer
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1|||OK....phew, problem was solved because the 'Rlogic' login was a member of
'Systems Administrators' group.
Hence, this login would log into the database with 'dbo' rights rather than
"user" named 'Rlogic'.
Thanks for the responses :)
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1

Permission issue

Hello,
Some facts:
- I am using SQL Server 2000 on W2K3
- I have a SQL Login called 'Rlogic' with dbo permission rights on every
database. It is also a member of the Systems Administrators Group.
In Query Analyzer, when I run the following:-
use Rlogic_Training
go
select *
from users
I get this error:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'users'.
---
However, when I append 'Rlogic.' before the dbname, it works fine: -
use Rlogic_Training
go
select *
from Rlogic.users
Is there a way for me NOT to append 'Rlogic.' everytime?
Thanks,
Sameer
Message posted via http://www.droptable.comHello,
In SQL 2005 you can create a SYNONYM and query the Synonym. But for SQL
2005, you need to either login as object owner or you need to change the
object owner to DBO using
SP_CHANGEOBJECTOWNER system stored procedure. If the object owner is DBO you
do not want to prefix the object owner even if you login as a different
user.
Thanks
Hari
"spremji via droptable.com" <u4996@.uwe> wrote in message
news:6fd4c5051b6c0@.uwe...
> Hello,
> Some facts:
> - I am using SQL Server 2000 on W2K3
> - I have a SQL Login called 'Rlogic' with dbo permission rights on every
> database. It is also a member of the Systems Administrators Group.
> In Query Analyzer, when I run the following:-
> use Rlogic_Training
> go
> select *
> from users
>
> I get this error:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'users'.
> ---
>
> However, when I append 'Rlogic.' before the dbname, it works fine: -
> use Rlogic_Training
> go
> select *
> from Rlogic.users
> Is there a way for me NOT to append 'Rlogic.' everytime?
> Thanks,
> Sameer
> --
> Message posted via http://www.droptable.com
>|||Hello Hari,
I tried sp_changeobjectowner but got this error:
Server: Msg 15001, Level 16, State 1, Procedure sp_changeobjectowner, Line 3
8
Object 'USERS' does not exist or is not a valid object for this operation.
When I see the tables in the 'Object Browser' left pane, I see them starting
with prefix 'Rlogic.' and there are literally hundreds of these tables and
there are few tables that start with 'dbo.' which I can query them without
any problem.
Is there a way to reflect this change to all these tables?
Thanks.
Message posted via http://www.droptable.com|||Sorry, ignore the above error but the exact error I got was: -
Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 47
The proposed new database owner is already a user in the database.
Message posted via http://www.droptable.com|||Hi
Probably you cereated this table before the login was added to SysAdmin
server role, right?
Well , create a new table ,move the data from the old one to the new one,
drop the old table then rename the new table with original name
"spremji via droptable.com" <u4996@.uwe> wrote in message
news:6fd4c5051b6c0@.uwe...
> Hello,
> Some facts:
> - I am using SQL Server 2000 on W2K3
> - I have a SQL Login called 'Rlogic' with dbo permission rights on every
> database. It is also a member of the Systems Administrators Group.
> In Query Analyzer, when I run the following:-
> use Rlogic_Training
> go
> select *
> from users
>
> I get this error:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'users'.
> ---
>
> However, when I append 'Rlogic.' before the dbname, it works fine: -
> use Rlogic_Training
> go
> select *
> from Rlogic.users
> Is there a way for me NOT to append 'Rlogic.' everytime?
> Thanks,
> Sameer
> --
> Message posted via http://www.droptable.com
>|||Hello,
PLease do not execute SP_CHANGEDBOWNER. You may need to use
SP_CHANGEOBJECTOWNER system procedure to change the object owner to DBO.
Use the below sample:-
Use <DBNAME>
GO
exec sp_changeobjectowner 'Rlogic.Users', 'dbo'
Thanks
Hari
"spremji via droptable.com" <u4996@.uwe> wrote in message
news:6fd64dd4931f0@.uwe...
> Sorry, ignore the above error but the exact error I got was: -
> Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 47
> The proposed new database owner is already a user in the database.
> --
> Message posted via http://www.droptable.com
>|||Uri,
No, the tables have been previously created by the Software installer and I
don't have the luxury to recreate the tables.
I have a db user 'Rlogic' which is associated with SQL Login 'Rlogic' as wel
l
and they are both associated fine.
For some reason, the user 'Rlogic' is not able to query the tables despite
being the db owner.
Any help is appreciated as clients are waiting to work and no application is
able to login.
Thanks,
Sameer
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200703/1|||OK....phew, problem was solved because the 'Rlogic' login was a member of
'Systems Administrators' group.
Hence, this login would log into the database with 'dbo' rights rather than
"user" named 'Rlogic'.
Thanks for the responses

Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200703/1

Permission Denied Error

Wednesday, March 28, 2012

Permssion denied for dbo

Permssion denied for dbo

Permssion denied for dbo

Monday, March 26, 2012

permissions to see design view

Friday, March 23, 2012

Permissions Question For SQL Login Account

Permissions Question For SQL Login Account

permissions problems with trigger script

Wednesday, March 21, 2012

Permissions on a Database

Monday, March 12, 2012

Permissions for running sp_Start_Job and xp_sqlagent_enum_jobs

Wednesday, March 7, 2012

permission to create dbo.proc but not drop table

permission to create dbo.proc but not drop table

permission to create dbo.proc but not drop table

Saturday, February 25, 2012

Permission problem

Permission problem

Permission problem

permission issue with tempdb works fine in SQL2000 but not SQL2005

permission issue with temp db

Permission issue

Permission issue

Permission issue

Followers

Blog Archive

About Me