Here is the situation:
I create a database - I am dbo. In this database I have 1 table and 1
updateable view. dbo shows as the owner of both the table and the view. I
create a new database user and do not add them to any database or server
roles. I grant the new user select, update, insert and delete permissions o
n
the view.
The user can view all data through the view, however, they cannot add or
update. When they attempt to add or update an error is generated indicating
that they do not have insert permissions on the table (for an add) or that
they do not have select and update persissions on the table (for an update).
My understanding is that since I have an unbroken ownership chain that SQL
Server should not even be checking the permissions on the table.
What am I missing?Some tools/APIs require that you create the view with the VIEW_METADATA
option. Otherwise, the base tables are accessed directly rather than via
the view. You can use a Profiler trace to see if this is the case.
Hope this helps.
Dan Guzman
SQL Server MVP
"Scott Shearer" <ScottShearer@.discussions.microsoft.com> wrote in message
news:B12E2447-6AAD-4722-B53C-EC84517FD80E@.microsoft.com...
> Here is the situation:
> I create a database - I am dbo. In this database I have 1 table and 1
> updateable view. dbo shows as the owner of both the table and the view.
> I
> create a new database user and do not add them to any database or server
> roles. I grant the new user select, update, insert and delete permissions
> on
> the view.
> The user can view all data through the view, however, they cannot add or
> update. When they attempt to add or update an error is generated
> indicating
> that they do not have insert permissions on the table (for an add) or that
> they do not have select and update persissions on the table (for an
> update).
> My understanding is that since I have an unbroken ownership chain that SQL
> Server should not even be checking the permissions on the table.
> What am I missing?|||I have noticed this behavior using Enterprise Manager and Microsoft Access
ADP. I also see this behavior with a stored proc in an MS Access ADP.
While I can execute a stored proc that contains an insert or update statemen
t
in the Access ADP, it won't work when using the stored proc as a bound objec
t
unless the end user has appropriate permissions on the underlying table.
Is this behavior as designed?
I'll try creating the view with the VIEW_METADATA option and report back.
Thanks
"Dan Guzman" wrote:
> Some tools/APIs require that you create the view with the VIEW_METADATA
> option. Otherwise, the base tables are accessed directly rather than via
> the view. You can use a Profiler trace to see if this is the case.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Scott Shearer" <ScottShearer@.discussions.microsoft.com> wrote in message
> news:B12E2447-6AAD-4722-B53C-EC84517FD80E@.microsoft.com...
>
>|||VIEW_METADATA is needed in views with Access ADPs so that Access doesn't
access the underlying tables directly. This is by design.
AFAIK, there shouldn't be a problem with stored procedures as long as there
is no dynamic SQL in the proc. Dynamic SQL always breaks the ownership
chain.
Hope this helps.
Dan Guzman
SQL Server MVP
"Scott Shearer" <ScottShearer@.discussions.microsoft.com> wrote in message
news:AD3BD957-D452-4CE5-96F0-B311A797872C@.microsoft.com...[vbcol=seagreen]
>I have noticed this behavior using Enterprise Manager and Microsoft Access
> ADP. I also see this behavior with a stored proc in an MS Access ADP.
> While I can execute a stored proc that contains an insert or update
> statement
> in the Access ADP, it won't work when using the stored proc as a bound
> object
> unless the end user has appropriate permissions on the underlying table.
> Is this behavior as designed?
> I'll try creating the view with the VIEW_METADATA option and report back.
> Thanks
> "Dan Guzman" wrote:
>|||Including the VIEW_META argument in the view definition worked. However,
SQL Server does appear to be checking permissions on the stored proc. I hav
e
a simple select statement in the stored proc that should be updateable when
accessed through an ADP. I get error messages indicating that the
permissions are being checked on the underlying table. However, when I
create a view using a select * against the table with the VIEW_METADATA
argument and reference that view in the SP rather than the underlying table,
it works OK.
By way of background, I am doing this in preparation for a project where row
level security will be required. I will filter data in a view and access th
e
views through stored procs. The users will have no direct access to any dat
a
other than through a view (which filters data appropriately) or an SP that
refernces one of the views.
"Dan Guzman" wrote:
> VIEW_METADATA is needed in views with Access ADPs so that Access doesn't
> access the underlying tables directly. This is by design.
> AFAIK, there shouldn't be a problem with stored procedures as long as ther
e
> is no dynamic SQL in the proc. Dynamic SQL always breaks the ownership
> chain.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Scott Shearer" <ScottShearer@.discussions.microsoft.com> wrote in message
> news:AD3BD957-D452-4CE5-96F0-B311A797872C@.microsoft.com...
>
>|||> I have a simple select statement in the stored proc that should
> be updateable when accessed through an ADP.
Your proc has only a SELECT statement and no INSERT/UPDATE/DELETE
statements. What is actually happening here is that Access is modifying the
base tables directly due to data binding. This necessitates that users have
permissions on the underlying table because the data modifications are not
done via a proc.
It seems views with VIEW_METADATA will allow you to use updatable bound
controls but this won't provide the security you want with stored procedure
resultsets. If you must use procedures for your SELECT, one option is to
create and execute INSERT/UPDATE/DELETE procs instead of relying on data
binding. Another approach is you use application roles from within you app
so that you can use data binding yet still prevent ad-hoc access outside
your application. See
<http://support.microsoft.com/defaul...kb;en-us;318816> for more
information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Scott Shearer" <ScottShearer@.discussions.microsoft.com> wrote in message
news:CCA6F68B-E42D-45EB-85AB-598827765FCC@.microsoft.com...[vbcol=seagreen]
> Including the VIEW_META argument in the view definition worked. However,
> SQL Server does appear to be checking permissions on the stored proc. I
> have
> a simple select statement in the stored proc that should be updateable
> when
> accessed through an ADP. I get error messages indicating that the
> permissions are being checked on the underlying table. However, when I
> create a view using a select * against the table with the VIEW_METADATA
> argument and reference that view in the SP rather than the underlying
> table,
> it works OK.
> By way of background, I am doing this in preparation for a project where
> row
> level security will be required. I will filter data in a view and access
> the
> views through stored procs. The users will have no direct access to any
> data
> other than through a view (which filters data appropriately) or an SP that
> refernces one of the views.
>
> "Dan Guzman" wrote:
>|||When the stored proc that I access through the ADP does a select from a view
created with the view_metadata argument, permissions appear only to be
checked on the stored proc and the view - not on the underlying table. I am
able to dpdates/inserts/deletes.
VIEW_METADATA was the answer - thanks for the help.
"Dan Guzman" wrote:
> Your proc has only a SELECT statement and no INSERT/UPDATE/DELETE
> statements. What is actually happening here is that Access is modifying t
he
> base tables directly due to data binding. This necessitates that users ha
ve
> permissions on the underlying table because the data modifications are not
> done via a proc.
> It seems views with VIEW_METADATA will allow you to use updatable bound
> controls but this won't provide the security you want with stored procedur
e
> resultsets. If you must use procedures for your SELECT, one option is to
> create and execute INSERT/UPDATE/DELETE procs instead of relying on data
> binding. Another approach is you use application roles from within you ap
p
> so that you can use data binding yet still prevent ad-hoc access outside
> your application. See
> <http://support.microsoft.com/defaul...kb;en-us;318816> for more
> information.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Scott Shearer" <ScottShearer@.discussions.microsoft.com> wrote in message
> news:CCA6F68B-E42D-45EB-85AB-598827765FCC@.microsoft.com...
>
>
Showing posts with label owner. Show all posts
Showing posts with label owner. Show all posts
Friday, March 9, 2012
Owner's Access to table
SQL SERVER 2000 Personal Edition
Win 2000
Connect using SqlServer Authentification
Jim **pw**
Run the following to create a new table:-
USE SolutionsNet
GO
CREATE TABLE
Companies
(CompanyId INT PRIMARY KEY IDENTITY(1,1),
Companyname VarChar(50) NOT NULL,
IsaRestaurant BIT,
. . . . .
)
GO
Then the following:-
Use SolutionsNet
GO
SELECT * FROM Companies
REM OR select * from jim.Companies
GO
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet',
owner 'Jim'.
BUT
connect as sa **pw**
USE SolutionsNet
GO
SELECT * FROM Jim.Companies
GO
Displays the empty table Ok
[NB. just Companies without tyhe prefix Jim. does not display the empty
table
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'Companies'. ]
?
Why can I not access the table connected as Jim - it's owner !!!
?
Jim Bunton
Ok - got the answer on sqlserver.tools group -
I'd 'clicked' all roles 'to make sure!!!!" one of them is 'denydatareader' -
> beginners beware!!!
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message
news:gpO4f.135773$RW.48588@.fe2.news.blueyonder.co. uk...
> SQL SERVER 2000 Personal Edition
> Win 2000
> Connect using SqlServer Authentification
> Jim **pw**
> Run the following to create a new table:-
> --
> USE SolutionsNet
> GO
> CREATE TABLE
> Companies
> (CompanyId INT PRIMARY KEY IDENTITY(1,1),
> Companyname VarChar(50) NOT NULL,
> IsaRestaurant BIT,
> . . . . .
> )
> GO
> --
> Then the following:-
> --
> Use SolutionsNet
> GO
> SELECT * FROM Companies
> REM OR select * from jim.Companies
> GO
> --
> Server: Msg 229, Level 14, State 5, Line 1
> SELECT permission denied on object 'Companies', database 'SolutionsNet',
> owner 'Jim'.
> BUT
> connect as sa **pw**
> --
> USE SolutionsNet
> GO
> SELECT * FROM Jim.Companies
> GO
> --
> Displays the empty table Ok
> [NB. just Companies without tyhe prefix Jim. does not display the empty
> table
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'Companies'. ]
> ?
> Why can I not access the table connected as Jim - it's owner !!!
> ?
>
> Jim Bunton
>
|||He must be in some groups which denies seeing table data. Try to
determine this via sp_helpuser 'Username'
HTH, jens Suessmeyer.
Win 2000
Connect using SqlServer Authentification
Jim **pw**
Run the following to create a new table:-
USE SolutionsNet
GO
CREATE TABLE
Companies
(CompanyId INT PRIMARY KEY IDENTITY(1,1),
Companyname VarChar(50) NOT NULL,
IsaRestaurant BIT,
. . . . .
)
GO
Then the following:-
Use SolutionsNet
GO
SELECT * FROM Companies
REM OR select * from jim.Companies
GO
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet',
owner 'Jim'.
BUT
connect as sa **pw**
USE SolutionsNet
GO
SELECT * FROM Jim.Companies
GO
Displays the empty table Ok
[NB. just Companies without tyhe prefix Jim. does not display the empty
table
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'Companies'. ]
?
Why can I not access the table connected as Jim - it's owner !!!
?
Jim Bunton
Ok - got the answer on sqlserver.tools group -
I'd 'clicked' all roles 'to make sure!!!!" one of them is 'denydatareader' -
> beginners beware!!!
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message
news:gpO4f.135773$RW.48588@.fe2.news.blueyonder.co. uk...
> SQL SERVER 2000 Personal Edition
> Win 2000
> Connect using SqlServer Authentification
> Jim **pw**
> Run the following to create a new table:-
> --
> USE SolutionsNet
> GO
> CREATE TABLE
> Companies
> (CompanyId INT PRIMARY KEY IDENTITY(1,1),
> Companyname VarChar(50) NOT NULL,
> IsaRestaurant BIT,
> . . . . .
> )
> GO
> --
> Then the following:-
> --
> Use SolutionsNet
> GO
> SELECT * FROM Companies
> REM OR select * from jim.Companies
> GO
> --
> Server: Msg 229, Level 14, State 5, Line 1
> SELECT permission denied on object 'Companies', database 'SolutionsNet',
> owner 'Jim'.
> BUT
> connect as sa **pw**
> --
> USE SolutionsNet
> GO
> SELECT * FROM Jim.Companies
> GO
> --
> Displays the empty table Ok
> [NB. just Companies without tyhe prefix Jim. does not display the empty
> table
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'Companies'. ]
> ?
> Why can I not access the table connected as Jim - it's owner !!!
> ?
>
> Jim Bunton
>
|||He must be in some groups which denies seeing table data. Try to
determine this via sp_helpuser 'Username'
HTH, jens Suessmeyer.
Owner's Access to table
SQL SERVER 2000 Personal Edition
Win 2000
Connect using SqlServer Authentification
Jim **pw**
Run the following to create a new table:-
--
USE SolutionsNet
GO
CREATE TABLE
Companies
(CompanyId INT PRIMARY KEY IDENTITY(1,1),
Companyname VarChar(50) NOT NULL,
IsaRestaurant BIT,
. . . . .
)
GO
--
Then the following:-
--
Use SolutionsNet
GO
SELECT * FROM Companies
REM OR select * from jim.Companies
GO
--
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet',
owner 'Jim'.
BUT
connect as sa **pw**
--
USE SolutionsNet
GO
SELECT * FROM Jim.Companies
GO
--
Displays the empty table Ok
[NB. just Companies without tyhe prefix Jim. does not display the empty
table
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'Companies'. ]
'
Why can I not access the table connected as Jim - it's owner !!!
?
Jim BuntonOk - got the answer on sqlserver.tools group -
I'd 'clicked' all roles 'to make sure!!!!" one of them is 'denydatareader' -
> beginners beware!!!
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message
news:gpO4f.135773$RW.48588@.fe2.news.blueyonder.co.uk...
> SQL SERVER 2000 Personal Edition
> Win 2000
> Connect using SqlServer Authentification
> Jim **pw**
> Run the following to create a new table:-
> --
> USE SolutionsNet
> GO
> CREATE TABLE
> Companies
> (CompanyId INT PRIMARY KEY IDENTITY(1,1),
> Companyname VarChar(50) NOT NULL,
> IsaRestaurant BIT,
> . . . . .
> )
> GO
> --
> Then the following:-
> --
> Use SolutionsNet
> GO
> SELECT * FROM Companies
> REM OR select * from jim.Companies
> GO
> --
> Server: Msg 229, Level 14, State 5, Line 1
> SELECT permission denied on object 'Companies', database 'SolutionsNet',
> owner 'Jim'.
> BUT
> connect as sa **pw**
> --
> USE SolutionsNet
> GO
> SELECT * FROM Jim.Companies
> GO
> --
> Displays the empty table Ok
> [NB. just Companies without tyhe prefix Jim. does not display the empt
y
> table
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'Companies'. ]
> '
> Why can I not access the table connected as Jim - it's owner !!!
> ?
>
> Jim Bunton
>|||He must be in some groups which denies seeing table data. Try to
determine this via sp_helpuser 'Username'
HTH, jens Suessmeyer.
Win 2000
Connect using SqlServer Authentification
Jim **pw**
Run the following to create a new table:-
--
USE SolutionsNet
GO
CREATE TABLE
Companies
(CompanyId INT PRIMARY KEY IDENTITY(1,1),
Companyname VarChar(50) NOT NULL,
IsaRestaurant BIT,
. . . . .
)
GO
--
Then the following:-
--
Use SolutionsNet
GO
SELECT * FROM Companies
REM OR select * from jim.Companies
GO
--
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet',
owner 'Jim'.
BUT
connect as sa **pw**
--
USE SolutionsNet
GO
SELECT * FROM Jim.Companies
GO
--
Displays the empty table Ok
[NB. just Companies without tyhe prefix Jim. does not display the empty
table
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'Companies'. ]
'
Why can I not access the table connected as Jim - it's owner !!!
?
Jim BuntonOk - got the answer on sqlserver.tools group -
I'd 'clicked' all roles 'to make sure!!!!" one of them is 'denydatareader' -
> beginners beware!!!
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message
news:gpO4f.135773$RW.48588@.fe2.news.blueyonder.co.uk...
> SQL SERVER 2000 Personal Edition
> Win 2000
> Connect using SqlServer Authentification
> Jim **pw**
> Run the following to create a new table:-
> --
> USE SolutionsNet
> GO
> CREATE TABLE
> Companies
> (CompanyId INT PRIMARY KEY IDENTITY(1,1),
> Companyname VarChar(50) NOT NULL,
> IsaRestaurant BIT,
> . . . . .
> )
> GO
> --
> Then the following:-
> --
> Use SolutionsNet
> GO
> SELECT * FROM Companies
> REM OR select * from jim.Companies
> GO
> --
> Server: Msg 229, Level 14, State 5, Line 1
> SELECT permission denied on object 'Companies', database 'SolutionsNet',
> owner 'Jim'.
> BUT
> connect as sa **pw**
> --
> USE SolutionsNet
> GO
> SELECT * FROM Jim.Companies
> GO
> --
> Displays the empty table Ok
> [NB. just Companies without tyhe prefix Jim. does not display the empt
y
> table
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'Companies'. ]
> '
> Why can I not access the table connected as Jim - it's owner !!!
> ?
>
> Jim Bunton
>|||He must be in some groups which denies seeing table data. Try to
determine this via sp_helpuser 'Username'
HTH, jens Suessmeyer.
owner problem...
i have transactional repl setup with an updatable subscriber. when my
initial snapshot is synced to the subscriber, the tables are dropped fine
but recreated under a non-dbo user (in my case one that is setup named
'dev'). what controls how the publisher/distributor drops/creates the tables
at the subscriber end? this must be where the 'dev' owner is coming from.
thanks.
Terry,
in terms of TSQL this is the @.destination_owner value from sp_addarticle. I
don't have the GUI in front of me right now, but take a look at the article
properties option and there should be an entry to specify this in the list
of properties there.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
initial snapshot is synced to the subscriber, the tables are dropped fine
but recreated under a non-dbo user (in my case one that is setup named
'dev'). what controls how the publisher/distributor drops/creates the tables
at the subscriber end? this must be where the 'dev' owner is coming from.
thanks.
Terry,
in terms of TSQL this is the @.destination_owner value from sp_addarticle. I
don't have the GUI in front of me right now, but take a look at the article
properties option and there should be an entry to specify this in the list
of properties there.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
Owner of the table
I've got this question:
Is it possible to assign the 'current owner' using a query like the
following one:
Is it possible to assign the 'current owner' using a query like the
following one:
set current schema OWNER1 (in DB2 sql, this allow me to reference the tables
without an explicit indication of the owner (es: after this query I can
simply write 'Select * from dummy', instead of 'Select * from
OWNER1.dummy')).
Thank you
Federica"Federica T" <fedina_chicca@.N_O_Spam_libero.it> wrote in message
news:ckga73$t06$1@.atlantis.cu.mi.it...
> I've got this question:
> Is it possible to assign the 'current owner' using a query like the
> following one:
> set current schema OWNER1 (in DB2 sql, this allow me to reference the
> tables
> without an explicit indication of the owner (es: after this query I can
> simply write 'Select * from dummy', instead of 'Select * from
> OWNER1.dummy')).
> Thank you
> Federica
Not really - in MSSQL, it's considered good practice to always fully specify
the object owner. There is a SETUSER command which allows a sysadmin to
impersonate another user, but it's deprecated and it doesn't support Windows
authentication which is the preferred authentication method.
Simon|||"Simon Hayes" <sql@.hayes.ch> ha scritto nel messaggio
news:416bc568$1_3@.news.bluewin.ch...
> Not really - in MSSQL, it's considered good practice to always fully
specify
> the object owner. There is a SETUSER command which allows a sysadmin to
> impersonate another user, but it's deprecated and it doesn't support
Windows
> authentication which is the preferred authentication method.
> Simon
Thank you a lot!
Fede
Owner of the database after restore
I restored a database SynComp_TEST from a backup using a login testbinu,
which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
I got below message ..
Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
file1.
Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on file
1.
Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file 1.
Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
Msg 916, Level 14, State 1, Server BOYD, Line 1
Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
Msg 3013, Level 16, State 1, Server BOYD, Line 1
RESTORE DATABASE is terminating abnormally.
Now I think the owner of the database is 'sa ' ?Am I right ?
Is there any way I can restore this database with testbinu as the owner ?
Or at least testbinu as a valid user in the database?
Hi,
First you have to correct the user testbinu. The error has come because the
login testbinu was not there in the master database syslogins table.
You can rectify the problem using the procedure sp_change_users_login (see
sql server books online on usage)
After builing up the Login/ user chain , you can change the database owner
to testbinu if you need
use <dbname>
go
sp_changedbowner testbinu
Thanks
Hari
MCDBA
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:#34ScB$cEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I restored a database SynComp_TEST from a backup using a login testbinu,
> which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
> I got below message ..
> Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
> file1.
> Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on
file
> 1.
> Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file
1.
> Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
> Msg 916, Level 14, State 1, Server BOYD, Line 1
> Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
> Msg 3013, Level 16, State 1, Server BOYD, Line 1
> RESTORE DATABASE is terminating abnormally.
>
> Now I think the owner of the database is 'sa ' ?Am I right ?
> Is there any way I can restore this database with testbinu as the owner ?
> Or at least testbinu as a valid user in the database?
>
which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
I got below message ..
Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
file1.
Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on file
1.
Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file 1.
Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
Msg 916, Level 14, State 1, Server BOYD, Line 1
Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
Msg 3013, Level 16, State 1, Server BOYD, Line 1
RESTORE DATABASE is terminating abnormally.
Now I think the owner of the database is 'sa ' ?Am I right ?
Is there any way I can restore this database with testbinu as the owner ?
Or at least testbinu as a valid user in the database?
Hi,
First you have to correct the user testbinu. The error has come because the
login testbinu was not there in the master database syslogins table.
You can rectify the problem using the procedure sp_change_users_login (see
sql server books online on usage)
After builing up the Login/ user chain , you can change the database owner
to testbinu if you need
use <dbname>
go
sp_changedbowner testbinu
Thanks
Hari
MCDBA
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:#34ScB$cEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I restored a database SynComp_TEST from a backup using a login testbinu,
> which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
> I got below message ..
> Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
> file1.
> Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on
file
> 1.
> Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file
1.
> Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
> Msg 916, Level 14, State 1, Server BOYD, Line 1
> Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
> Msg 3013, Level 16, State 1, Server BOYD, Line 1
> RESTORE DATABASE is terminating abnormally.
>
> Now I think the owner of the database is 'sa ' ?Am I right ?
> Is there any way I can restore this database with testbinu as the owner ?
> Or at least testbinu as a valid user in the database?
>
Owner of the database after restore
I restored a database SynComp_TEST from a backup using a login testbinu,
which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
I got below message ..
Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
file1.
Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on file
1.
Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file 1.
Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
Msg 916, Level 14, State 1, Server BOYD, Line 1
Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
Msg 3013, Level 16, State 1, Server BOYD, Line 1
RESTORE DATABASE is terminating abnormally.
Now I think the owner of the database is 'sa ' ?Am I right ?
Is there any way I can restore this database with testbinu as the owner ?
Or at least testbinu as a valid user in the database?Hi,
First you have to correct the user testbinu. The error has come because the
login testbinu was not there in the master database syslogins table.
You can rectify the problem using the procedure sp_change_users_login (see
sql server books online on usage)
After builing up the Login/ user chain , you can change the database owner
to testbinu if you need
use <dbname>
go
sp_changedbowner testbinu
Thanks
Hari
MCDBA
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:#34ScB$cEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I restored a database SynComp_TEST from a backup using a login testbinu,
> which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
> I got below message ..
> Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
> file1.
> Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on
file
> 1.
> Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file
1.
> Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
> Msg 916, Level 14, State 1, Server BOYD, Line 1
> Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
> Msg 3013, Level 16, State 1, Server BOYD, Line 1
> RESTORE DATABASE is terminating abnormally.
>
> Now I think the owner of the database is 'sa ' ?Am I right ?
> Is there any way I can restore this database with testbinu as the owner ?
> Or at least testbinu as a valid user in the database?
>|||Follow the steps that Hari outlined for you to change the
owner.
To check a db owner, you can execute:
sp_helpdb
-Sue
On Tue, 27 Jul 2004 11:40:11 -0400, "Abraham"
<binu_ca@.yahoo.com> wrote:
>I restored a database SynComp_TEST from a backup using a login testbinu,
>which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
>I got below message ..
>Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
>file1.
>Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on fil
e
>1.
>Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file 1
.
>Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
>Msg 916, Level 14, State 1, Server BOYD, Line 1
>Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
>Msg 3013, Level 16, State 1, Server BOYD, Line 1
>RESTORE DATABASE is terminating abnormally.
>
>Now I think the owner of the database is 'sa ' ?Am I right ?
>Is there any way I can restore this database with testbinu as the owner ?
>Or at least testbinu as a valid user in the database?
>
which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
I got below message ..
Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
file1.
Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on file
1.
Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file 1.
Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
Msg 916, Level 14, State 1, Server BOYD, Line 1
Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
Msg 3013, Level 16, State 1, Server BOYD, Line 1
RESTORE DATABASE is terminating abnormally.
Now I think the owner of the database is 'sa ' ?Am I right ?
Is there any way I can restore this database with testbinu as the owner ?
Or at least testbinu as a valid user in the database?Hi,
First you have to correct the user testbinu. The error has come because the
login testbinu was not there in the master database syslogins table.
You can rectify the problem using the procedure sp_change_users_login (see
sql server books online on usage)
After builing up the Login/ user chain , you can change the database owner
to testbinu if you need
use <dbname>
go
sp_changedbowner testbinu
Thanks
Hari
MCDBA
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:#34ScB$cEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I restored a database SynComp_TEST from a backup using a login testbinu,
> which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
> I got below message ..
> Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
> file1.
> Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on
file
> 1.
> Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file
1.
> Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
> Msg 916, Level 14, State 1, Server BOYD, Line 1
> Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
> Msg 3013, Level 16, State 1, Server BOYD, Line 1
> RESTORE DATABASE is terminating abnormally.
>
> Now I think the owner of the database is 'sa ' ?Am I right ?
> Is there any way I can restore this database with testbinu as the owner ?
> Or at least testbinu as a valid user in the database?
>|||Follow the steps that Hari outlined for you to change the
owner.
To check a db owner, you can execute:
sp_helpdb
-Sue
On Tue, 27 Jul 2004 11:40:11 -0400, "Abraham"
<binu_ca@.yahoo.com> wrote:
>I restored a database SynComp_TEST from a backup using a login testbinu,
>which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
>I got below message ..
>Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
>file1.
>Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on fil
e
>1.
>Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file 1
.
>Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
>Msg 916, Level 14, State 1, Server BOYD, Line 1
>Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
>Msg 3013, Level 16, State 1, Server BOYD, Line 1
>RESTORE DATABASE is terminating abnormally.
>
>Now I think the owner of the database is 'sa ' ?Am I right ?
>Is there any way I can restore this database with testbinu as the owner ?
>Or at least testbinu as a valid user in the database?
>
Owner of the database after restore
I restored a database SynComp_TEST from a backup using a login testbinu,
which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
I got below message ..
Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
file1.
Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on file
1.
Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file 1.
Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
Msg 916, Level 14, State 1, Server BOYD, Line 1
Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
Msg 3013, Level 16, State 1, Server BOYD, Line 1
RESTORE DATABASE is terminating abnormally.
Now I think the owner of the database is 'sa ' ?Am I right ?
Is there any way I can restore this database with testbinu as the owner ?
Or at least testbinu as a valid user in the database?Hi,
First you have to correct the user testbinu. The error has come because the
login testbinu was not there in the master database syslogins table.
You can rectify the problem using the procedure sp_change_users_login (see
sql server books online on usage)
After builing up the Login/ user chain , you can change the database owner
to testbinu if you need
use <dbname>
go
sp_changedbowner testbinu
Thanks
Hari
MCDBA
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:#34ScB$cEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I restored a database SynComp_TEST from a backup using a login testbinu,
> which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
> I got below message ..
> Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
> file1.
> Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on
file
> 1.
> Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file
1.
> Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
> Msg 916, Level 14, State 1, Server BOYD, Line 1
> Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
> Msg 3013, Level 16, State 1, Server BOYD, Line 1
> RESTORE DATABASE is terminating abnormally.
>
> Now I think the owner of the database is 'sa ' ?Am I right ?
> Is there any way I can restore this database with testbinu as the owner ?
> Or at least testbinu as a valid user in the database?
>
which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
I got below message ..
Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
file1.
Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on file
1.
Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file 1.
Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
Msg 916, Level 14, State 1, Server BOYD, Line 1
Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
Msg 3013, Level 16, State 1, Server BOYD, Line 1
RESTORE DATABASE is terminating abnormally.
Now I think the owner of the database is 'sa ' ?Am I right ?
Is there any way I can restore this database with testbinu as the owner ?
Or at least testbinu as a valid user in the database?Hi,
First you have to correct the user testbinu. The error has come because the
login testbinu was not there in the master database syslogins table.
You can rectify the problem using the procedure sp_change_users_login (see
sql server books online on usage)
After builing up the Login/ user chain , you can change the database owner
to testbinu if you need
use <dbname>
go
sp_changedbowner testbinu
Thanks
Hari
MCDBA
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:#34ScB$cEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I restored a database SynComp_TEST from a backup using a login testbinu,
> which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
> I got below message ..
> Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
> file1.
> Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on
file
> 1.
> Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file
1.
> Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
> Msg 916, Level 14, State 1, Server BOYD, Line 1
> Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
> Msg 3013, Level 16, State 1, Server BOYD, Line 1
> RESTORE DATABASE is terminating abnormally.
>
> Now I think the owner of the database is 'sa ' ?Am I right ?
> Is there any way I can restore this database with testbinu as the owner ?
> Or at least testbinu as a valid user in the database?
>
Owner of the database after restore
I restored a database SynComp_TEST from a backup using a login testbinu,
which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
I got below message ..
Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
file1.
Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on file
1.
Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file 1.
Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
Msg 916, Level 14, State 1, Server BOYD, Line 1
Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
Msg 3013, Level 16, State 1, Server BOYD, Line 1
RESTORE DATABASE is terminating abnormally.
Now I think the owner of the database is 'sa ' ?Am I right ?
Is there any way I can restore this database with testbinu as the owner ?
Or at least testbinu as a valid user in the database?Hi,
First you have to correct the user testbinu. The error has come because the
login testbinu was not there in the master database syslogins table.
You can rectify the problem using the procedure sp_change_users_login (see
sql server books online on usage)
After builing up the Login/ user chain , you can change the database owner
to testbinu if you need
use <dbname>
go
sp_changedbowner testbinu
Thanks
Hari
MCDBA
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:#34ScB$cEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I restored a database SynComp_TEST from a backup using a login testbinu,
> which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
> I got below message ..
> Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
> file1.
> Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on
file
> 1.
> Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file
1.
> Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
> Msg 916, Level 14, State 1, Server BOYD, Line 1
> Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
> Msg 3013, Level 16, State 1, Server BOYD, Line 1
> RESTORE DATABASE is terminating abnormally.
>
> Now I think the owner of the database is 'sa ' ?Am I right ?
> Is there any way I can restore this database with testbinu as the owner ?
> Or at least testbinu as a valid user in the database?
>
which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
I got below message ..
Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
file1.
Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on file
1.
Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file 1.
Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
Msg 916, Level 14, State 1, Server BOYD, Line 1
Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
Msg 3013, Level 16, State 1, Server BOYD, Line 1
RESTORE DATABASE is terminating abnormally.
Now I think the owner of the database is 'sa ' ?Am I right ?
Is there any way I can restore this database with testbinu as the owner ?
Or at least testbinu as a valid user in the database?Hi,
First you have to correct the user testbinu. The error has come because the
login testbinu was not there in the master database syslogins table.
You can rectify the problem using the procedure sp_change_users_login (see
sql server books online on usage)
After builing up the Login/ user chain , you can change the database owner
to testbinu if you need
use <dbname>
go
sp_changedbowner testbinu
Thanks
Hari
MCDBA
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:#34ScB$cEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I restored a database SynComp_TEST from a backup using a login testbinu,
> which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
> I got below message ..
> Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
> file1.
> Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on
file
> 1.
> Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file
1.
> Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
> Msg 916, Level 14, State 1, Server BOYD, Line 1
> Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
> Msg 3013, Level 16, State 1, Server BOYD, Line 1
> RESTORE DATABASE is terminating abnormally.
>
> Now I think the owner of the database is 'sa ' ?Am I right ?
> Is there any way I can restore this database with testbinu as the owner ?
> Or at least testbinu as a valid user in the database?
>
Owner of tables
I have a stored procedure, that among other things drops and recreates two
new tables:
drop table dbo.t_de_tracking
drop table dbo.t_MR_TRACKING
SELECT * INTO dbo.T_DE_TRACKING
FROM OPENQUERY(ACS_SLR_DE, 'SELECT * FROM ACS_SLR_DATAENTRY..
T_DE_TRACKING (nolock)')
SELECT * INTO dbo.T_MR_TRACKING
FROM OPENQUERY(ACS_SLR_MR, 'SELECT * FROM ACS_SLR_MAILROOM..
T_MR_TRACKING (nolock)')
When these tables are created the owner is NOT dbo. How can I get the owner
of these tables to become dbo when created by this stored procedure?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200510/1Robert,
Do you really want to drop/create the tables or just TRUNCATE the table and
reload it? Probably not the best approach esp if there are a lot of records
and/or NC indexes. What are the fixed server and fixed database role
memberships of the user creating this proc? Also, you might want to build
some IF logic into testing if the tables exist prior to dropping them.
HTH
Jerry
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:55d48972e2388@.uwe...
>I have a stored procedure, that among other things drops and recreates two
> new tables:
> drop table dbo.t_de_tracking
> drop table dbo.t_MR_TRACKING
> SELECT * INTO dbo.T_DE_TRACKING
> FROM OPENQUERY(ACS_SLR_DE, 'SELECT * FROM ACS_SLR_DATAENTRY..
> T_DE_TRACKING (nolock)')
> SELECT * INTO dbo.T_MR_TRACKING
> FROM OPENQUERY(ACS_SLR_MR, 'SELECT * FROM ACS_SLR_MAILROOM..
> T_MR_TRACKING (nolock)')
> When these tables are created the owner is NOT dbo. How can I get the
> owner
> of these tables to become dbo when created by this stored procedure?
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200510/1|||Try naming the stored procedure dbo.[sproc name]
--
Thomas
"Robert R via droptable.com" wrote:
> I have a stored procedure, that among other things drops and recreates two
> new tables:
> drop table dbo.t_de_tracking
> drop table dbo.t_MR_TRACKING
> SELECT * INTO dbo.T_DE_TRACKING
> FROM OPENQUERY(ACS_SLR_DE, 'SELECT * FROM ACS_SLR_DATAENTRY..
> T_DE_TRACKING (nolock)')
> SELECT * INTO dbo.T_MR_TRACKING
> FROM OPENQUERY(ACS_SLR_MR, 'SELECT * FROM ACS_SLR_MAILROOM..
> T_MR_TRACKING (nolock)')
> When these tables are created the owner is NOT dbo. How can I get the owne
r
> of these tables to become dbo when created by this stored procedure?
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200510/1
>
new tables:
drop table dbo.t_de_tracking
drop table dbo.t_MR_TRACKING
SELECT * INTO dbo.T_DE_TRACKING
FROM OPENQUERY(ACS_SLR_DE, 'SELECT * FROM ACS_SLR_DATAENTRY..
T_DE_TRACKING (nolock)')
SELECT * INTO dbo.T_MR_TRACKING
FROM OPENQUERY(ACS_SLR_MR, 'SELECT * FROM ACS_SLR_MAILROOM..
T_MR_TRACKING (nolock)')
When these tables are created the owner is NOT dbo. How can I get the owner
of these tables to become dbo when created by this stored procedure?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200510/1Robert,
Do you really want to drop/create the tables or just TRUNCATE the table and
reload it? Probably not the best approach esp if there are a lot of records
and/or NC indexes. What are the fixed server and fixed database role
memberships of the user creating this proc? Also, you might want to build
some IF logic into testing if the tables exist prior to dropping them.
HTH
Jerry
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:55d48972e2388@.uwe...
>I have a stored procedure, that among other things drops and recreates two
> new tables:
> drop table dbo.t_de_tracking
> drop table dbo.t_MR_TRACKING
> SELECT * INTO dbo.T_DE_TRACKING
> FROM OPENQUERY(ACS_SLR_DE, 'SELECT * FROM ACS_SLR_DATAENTRY..
> T_DE_TRACKING (nolock)')
> SELECT * INTO dbo.T_MR_TRACKING
> FROM OPENQUERY(ACS_SLR_MR, 'SELECT * FROM ACS_SLR_MAILROOM..
> T_MR_TRACKING (nolock)')
> When these tables are created the owner is NOT dbo. How can I get the
> owner
> of these tables to become dbo when created by this stored procedure?
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200510/1|||Try naming the stored procedure dbo.[sproc name]
--
Thomas
"Robert R via droptable.com" wrote:
> I have a stored procedure, that among other things drops and recreates two
> new tables:
> drop table dbo.t_de_tracking
> drop table dbo.t_MR_TRACKING
> SELECT * INTO dbo.T_DE_TRACKING
> FROM OPENQUERY(ACS_SLR_DE, 'SELECT * FROM ACS_SLR_DATAENTRY..
> T_DE_TRACKING (nolock)')
> SELECT * INTO dbo.T_MR_TRACKING
> FROM OPENQUERY(ACS_SLR_MR, 'SELECT * FROM ACS_SLR_MAILROOM..
> T_MR_TRACKING (nolock)')
> When these tables are created the owner is NOT dbo. How can I get the owne
r
> of these tables to become dbo when created by this stored procedure?
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200510/1
>
Owner of tables
I have a stored procedure, that among other things drops and recreates two
new tables:
drop table dbo.t_de_tracking
drop table dbo.t_MR_TRACKING
SELECT * INTO dbo.T_DE_TRACKING
FROM OPENQUERY(ACS_SLR_DE, 'SELECT * FROM ACS_SLR_DATAENTRY..
T_DE_TRACKING (nolock)')
SELECT * INTO dbo.T_MR_TRACKING
FROM OPENQUERY(ACS_SLR_MR, 'SELECT * FROM ACS_SLR_MAILROOM..
T_MR_TRACKING (nolock)')
When these tables are created the owner is NOT dbo. How can I get the owner
of these tables to become dbo when created by this stored procedure?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200510/1Robert,
Do you really want to drop/create the tables or just TRUNCATE the table and
reload it? Probably not the best approach esp if there are a lot of records
and/or NC indexes. What are the fixed server and fixed database role
memberships of the user creating this proc? Also, you might want to build
some IF logic into testing if the tables exist prior to dropping them.
HTH
Jerry
"Robert R via SQLMonster.com" <u3288@.uwe> wrote in message
news:55d48972e2388@.uwe...
>I have a stored procedure, that among other things drops and recreates two
> new tables:
> drop table dbo.t_de_tracking
> drop table dbo.t_MR_TRACKING
> SELECT * INTO dbo.T_DE_TRACKING
> FROM OPENQUERY(ACS_SLR_DE, 'SELECT * FROM ACS_SLR_DATAENTRY..
> T_DE_TRACKING (nolock)')
> SELECT * INTO dbo.T_MR_TRACKING
> FROM OPENQUERY(ACS_SLR_MR, 'SELECT * FROM ACS_SLR_MAILROOM..
> T_MR_TRACKING (nolock)')
> When these tables are created the owner is NOT dbo. How can I get the
> owner
> of these tables to become dbo when created by this stored procedure?
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200510/1|||Try naming the stored procedure dbo.[sproc name]
--
Thomas
"Robert R via SQLMonster.com" wrote:
> I have a stored procedure, that among other things drops and recreates two
> new tables:
> drop table dbo.t_de_tracking
> drop table dbo.t_MR_TRACKING
> SELECT * INTO dbo.T_DE_TRACKING
> FROM OPENQUERY(ACS_SLR_DE, 'SELECT * FROM ACS_SLR_DATAENTRY..
> T_DE_TRACKING (nolock)')
> SELECT * INTO dbo.T_MR_TRACKING
> FROM OPENQUERY(ACS_SLR_MR, 'SELECT * FROM ACS_SLR_MAILROOM..
> T_MR_TRACKING (nolock)')
> When these tables are created the owner is NOT dbo. How can I get the owner
> of these tables to become dbo when created by this stored procedure?
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200510/1
>
new tables:
drop table dbo.t_de_tracking
drop table dbo.t_MR_TRACKING
SELECT * INTO dbo.T_DE_TRACKING
FROM OPENQUERY(ACS_SLR_DE, 'SELECT * FROM ACS_SLR_DATAENTRY..
T_DE_TRACKING (nolock)')
SELECT * INTO dbo.T_MR_TRACKING
FROM OPENQUERY(ACS_SLR_MR, 'SELECT * FROM ACS_SLR_MAILROOM..
T_MR_TRACKING (nolock)')
When these tables are created the owner is NOT dbo. How can I get the owner
of these tables to become dbo when created by this stored procedure?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200510/1Robert,
Do you really want to drop/create the tables or just TRUNCATE the table and
reload it? Probably not the best approach esp if there are a lot of records
and/or NC indexes. What are the fixed server and fixed database role
memberships of the user creating this proc? Also, you might want to build
some IF logic into testing if the tables exist prior to dropping them.
HTH
Jerry
"Robert R via SQLMonster.com" <u3288@.uwe> wrote in message
news:55d48972e2388@.uwe...
>I have a stored procedure, that among other things drops and recreates two
> new tables:
> drop table dbo.t_de_tracking
> drop table dbo.t_MR_TRACKING
> SELECT * INTO dbo.T_DE_TRACKING
> FROM OPENQUERY(ACS_SLR_DE, 'SELECT * FROM ACS_SLR_DATAENTRY..
> T_DE_TRACKING (nolock)')
> SELECT * INTO dbo.T_MR_TRACKING
> FROM OPENQUERY(ACS_SLR_MR, 'SELECT * FROM ACS_SLR_MAILROOM..
> T_MR_TRACKING (nolock)')
> When these tables are created the owner is NOT dbo. How can I get the
> owner
> of these tables to become dbo when created by this stored procedure?
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200510/1|||Try naming the stored procedure dbo.[sproc name]
--
Thomas
"Robert R via SQLMonster.com" wrote:
> I have a stored procedure, that among other things drops and recreates two
> new tables:
> drop table dbo.t_de_tracking
> drop table dbo.t_MR_TRACKING
> SELECT * INTO dbo.T_DE_TRACKING
> FROM OPENQUERY(ACS_SLR_DE, 'SELECT * FROM ACS_SLR_DATAENTRY..
> T_DE_TRACKING (nolock)')
> SELECT * INTO dbo.T_MR_TRACKING
> FROM OPENQUERY(ACS_SLR_MR, 'SELECT * FROM ACS_SLR_MAILROOM..
> T_MR_TRACKING (nolock)')
> When these tables are created the owner is NOT dbo. How can I get the owner
> of these tables to become dbo when created by this stored procedure?
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200510/1
>
Owner of tables
I have a stored procedure, that among other things drops and recreates two
new tables:
drop table dbo.t_de_tracking
drop table dbo.t_MR_TRACKING
SELECT * INTO dbo.T_DE_TRACKING
FROM OPENQUERY(ACS_SLR_DE, 'SELECT * FROM ACS_SLR_DATAENTRY..
T_DE_TRACKING (nolock)')
SELECT * INTO dbo.T_MR_TRACKING
FROM OPENQUERY(ACS_SLR_MR, 'SELECT * FROM ACS_SLR_MAILROOM..
T_MR_TRACKING (nolock)')
When these tables are created the owner is NOT dbo. How can I get the owner
of these tables to become dbo when created by this stored procedure?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200510/1
Robert,
Do you really want to drop/create the tables or just TRUNCATE the table and
reload it? Probably not the best approach esp if there are a lot of records
and/or NC indexes. What are the fixed server and fixed database role
memberships of the user creating this proc? Also, you might want to build
some IF logic into testing if the tables exist prior to dropping them.
HTH
Jerry
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:55d48972e2388@.uwe...
>I have a stored procedure, that among other things drops and recreates two
> new tables:
> drop table dbo.t_de_tracking
> drop table dbo.t_MR_TRACKING
> SELECT * INTO dbo.T_DE_TRACKING
> FROM OPENQUERY(ACS_SLR_DE, 'SELECT * FROM ACS_SLR_DATAENTRY..
> T_DE_TRACKING (nolock)')
> SELECT * INTO dbo.T_MR_TRACKING
> FROM OPENQUERY(ACS_SLR_MR, 'SELECT * FROM ACS_SLR_MAILROOM..
> T_MR_TRACKING (nolock)')
> When these tables are created the owner is NOT dbo. How can I get the
> owner
> of these tables to become dbo when created by this stored procedure?
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200510/1
|||Try naming the stored procedure dbo.[sproc name]
Thomas
"Robert R via droptable.com" wrote:
> I have a stored procedure, that among other things drops and recreates two
> new tables:
> drop table dbo.t_de_tracking
> drop table dbo.t_MR_TRACKING
> SELECT * INTO dbo.T_DE_TRACKING
> FROM OPENQUERY(ACS_SLR_DE, 'SELECT * FROM ACS_SLR_DATAENTRY..
> T_DE_TRACKING (nolock)')
> SELECT * INTO dbo.T_MR_TRACKING
> FROM OPENQUERY(ACS_SLR_MR, 'SELECT * FROM ACS_SLR_MAILROOM..
> T_MR_TRACKING (nolock)')
> When these tables are created the owner is NOT dbo. How can I get the owner
> of these tables to become dbo when created by this stored procedure?
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200510/1
>
new tables:
drop table dbo.t_de_tracking
drop table dbo.t_MR_TRACKING
SELECT * INTO dbo.T_DE_TRACKING
FROM OPENQUERY(ACS_SLR_DE, 'SELECT * FROM ACS_SLR_DATAENTRY..
T_DE_TRACKING (nolock)')
SELECT * INTO dbo.T_MR_TRACKING
FROM OPENQUERY(ACS_SLR_MR, 'SELECT * FROM ACS_SLR_MAILROOM..
T_MR_TRACKING (nolock)')
When these tables are created the owner is NOT dbo. How can I get the owner
of these tables to become dbo when created by this stored procedure?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200510/1
Robert,
Do you really want to drop/create the tables or just TRUNCATE the table and
reload it? Probably not the best approach esp if there are a lot of records
and/or NC indexes. What are the fixed server and fixed database role
memberships of the user creating this proc? Also, you might want to build
some IF logic into testing if the tables exist prior to dropping them.
HTH
Jerry
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:55d48972e2388@.uwe...
>I have a stored procedure, that among other things drops and recreates two
> new tables:
> drop table dbo.t_de_tracking
> drop table dbo.t_MR_TRACKING
> SELECT * INTO dbo.T_DE_TRACKING
> FROM OPENQUERY(ACS_SLR_DE, 'SELECT * FROM ACS_SLR_DATAENTRY..
> T_DE_TRACKING (nolock)')
> SELECT * INTO dbo.T_MR_TRACKING
> FROM OPENQUERY(ACS_SLR_MR, 'SELECT * FROM ACS_SLR_MAILROOM..
> T_MR_TRACKING (nolock)')
> When these tables are created the owner is NOT dbo. How can I get the
> owner
> of these tables to become dbo when created by this stored procedure?
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200510/1
|||Try naming the stored procedure dbo.[sproc name]
Thomas
"Robert R via droptable.com" wrote:
> I have a stored procedure, that among other things drops and recreates two
> new tables:
> drop table dbo.t_de_tracking
> drop table dbo.t_MR_TRACKING
> SELECT * INTO dbo.T_DE_TRACKING
> FROM OPENQUERY(ACS_SLR_DE, 'SELECT * FROM ACS_SLR_DATAENTRY..
> T_DE_TRACKING (nolock)')
> SELECT * INTO dbo.T_MR_TRACKING
> FROM OPENQUERY(ACS_SLR_MR, 'SELECT * FROM ACS_SLR_MAILROOM..
> T_MR_TRACKING (nolock)')
> When these tables are created the owner is NOT dbo. How can I get the owner
> of these tables to become dbo when created by this stored procedure?
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200510/1
>
Owner of table creation in SP
Hi everyone and Happy Holidays!
I've got a problem with table creation in stored procedures (SQL Server 2000). We've got an application where the user login only has rights to execute stored procedures. The problem is that a stored proc is dynamically creating a table and so the owner of that table is being assigned to whatever login the application is using instead of dbo. It's causing numerous issues. Is there any way that this can be avoided or changed without granting the user sa privileges?
Thanks in advance,
CatI never write application code that creates permanent database objects on the fly. it's bad news more often than not.|||Hi Thras,
They're not actually permanent, but they need to stick around for longer than the stored proc that's creating them.
Cat|||How about this then. Create the table as dbo.<tablename> with the needed columns, a guid as the PK, and a DatePopulated column as an indexed column. When the user needs to insert data, grab a guid and getdate() and put it all in the table. Save the guid for later use.
If the user needs to retrieve the data, use the held guid to access it.
Have a scheduled job that runs periodically (daily, hourly, whatever) that deletes from the table after the desired retention period has expired.
No more tables created by users, schema gets backed up with your backups, everyone is happy!|||????
What happens when the same sproc executes at the same time?|||Each spid gets a different guid ... unique dataset per loser ... err I mean user.
ALso have seen that technique used for delayed paging ... sweep data every 20 minutes or so ro remove stale data.|||no, not your idea to have 1 table...what happens when the sproc is creating a table and it is executed at the same time?
ka boom
why not a temp table?|||I saw a design once where the developer appended the tables he created on the fly with the user name and if there was a table already there he would add a incrementing number after the user name. it was amazingly bad and problematic and junked up the database and the execution plans something fierce because the table were not always removed. I offered another solution. He rejected it because it was too much work. he was my boss. My tenure there was short.|||I saw a design once where the developer appended the tables he created on the fly with the user name and if there was a table already there he would add a incrementing number after the user name. it was amazingly bad and problematic and junked up the database and the execution plans something fierce because the table were not always removed. I offered another solution. He rejected it because it was too much work. he was my boss. My tenure there was short.
Was the boss short?|||no, not your idea to have 1 table...what happens when the sproc is creating a table and it is executed at the same time?
ka boom
why not a temp table?
Sorry Brett ... real live dba creates one and only one table before any proc runs. Proc inserts into the table, and retrieves data as needed. Scheduled job clears the table of stale data.
Proc does not create table, that way no loser tables to clean up!
Was the boss short?
With pointy hair!|||Was the boss short?
no. he was rather tall.|||The table name has an identifier imbedded into it, it works like Tom's suggestion but the data is distributed into separate tables with a look-up for the TableID. Please note, this was not my design so please don't shoot me! :shocked:
There is a nightly process to go through and drop the tables (which is one place where I'm running into the difficulty of having them created by the user login).
Cat|||got some examples?|||It's all very ugly because any stored procs that access these tables have to use dynamic SQL statements like...
declare @.cmd varchar(1000)
set @.cmd = 'select * from tDynBrokerage' + convert(varchar(10), @.tabid)
exec @.cmd
where @.tabid is passed in from the application. I don't like it but I'm stuck with it for the time being. That's what I get for going on maternity leave! So... back to the original question. Any thoughts on that?
Thanks,
Cat|||Why can't these be global temps again?
I've got a problem with table creation in stored procedures (SQL Server 2000). We've got an application where the user login only has rights to execute stored procedures. The problem is that a stored proc is dynamically creating a table and so the owner of that table is being assigned to whatever login the application is using instead of dbo. It's causing numerous issues. Is there any way that this can be avoided or changed without granting the user sa privileges?
Thanks in advance,
CatI never write application code that creates permanent database objects on the fly. it's bad news more often than not.|||Hi Thras,
They're not actually permanent, but they need to stick around for longer than the stored proc that's creating them.
Cat|||How about this then. Create the table as dbo.<tablename> with the needed columns, a guid as the PK, and a DatePopulated column as an indexed column. When the user needs to insert data, grab a guid and getdate() and put it all in the table. Save the guid for later use.
If the user needs to retrieve the data, use the held guid to access it.
Have a scheduled job that runs periodically (daily, hourly, whatever) that deletes from the table after the desired retention period has expired.
No more tables created by users, schema gets backed up with your backups, everyone is happy!|||????
What happens when the same sproc executes at the same time?|||Each spid gets a different guid ... unique dataset per loser ... err I mean user.
ALso have seen that technique used for delayed paging ... sweep data every 20 minutes or so ro remove stale data.|||no, not your idea to have 1 table...what happens when the sproc is creating a table and it is executed at the same time?
ka boom
why not a temp table?|||I saw a design once where the developer appended the tables he created on the fly with the user name and if there was a table already there he would add a incrementing number after the user name. it was amazingly bad and problematic and junked up the database and the execution plans something fierce because the table were not always removed. I offered another solution. He rejected it because it was too much work. he was my boss. My tenure there was short.|||I saw a design once where the developer appended the tables he created on the fly with the user name and if there was a table already there he would add a incrementing number after the user name. it was amazingly bad and problematic and junked up the database and the execution plans something fierce because the table were not always removed. I offered another solution. He rejected it because it was too much work. he was my boss. My tenure there was short.
Was the boss short?|||no, not your idea to have 1 table...what happens when the sproc is creating a table and it is executed at the same time?
ka boom
why not a temp table?
Sorry Brett ... real live dba creates one and only one table before any proc runs. Proc inserts into the table, and retrieves data as needed. Scheduled job clears the table of stale data.
Proc does not create table, that way no loser tables to clean up!
Was the boss short?
With pointy hair!|||Was the boss short?
no. he was rather tall.|||The table name has an identifier imbedded into it, it works like Tom's suggestion but the data is distributed into separate tables with a look-up for the TableID. Please note, this was not my design so please don't shoot me! :shocked:
There is a nightly process to go through and drop the tables (which is one place where I'm running into the difficulty of having them created by the user login).
Cat|||got some examples?|||It's all very ugly because any stored procs that access these tables have to use dynamic SQL statements like...
declare @.cmd varchar(1000)
set @.cmd = 'select * from tDynBrokerage' + convert(varchar(10), @.tabid)
exec @.cmd
where @.tabid is passed in from the application. I don't like it but I'm stuck with it for the time being. That's what I get for going on maternity leave! So... back to the original question. Any thoughts on that?
Thanks,
Cat|||Why can't these be global temps again?
owner of maintenance jobs-permission hole?
Hello,
I have a SQL 2000 user who created some maintenance jobs (they are owned by
her account). She can run them manually but says she can't schedule them. If
I change the owner of the jobs to SA, does that mean she could then modify
those jobs and get data with SA rights that she didn't have access to with
her account?
tia,
jjjj,
Why can't she schedule her jobs? If she has public rights to the msdb
database, I believe that is enough to allow her to schedule jobs that run
under her own rights.
If you allow these jobs to run at higher levels of security, then certainly
the job could technically get access to additional data.
If you do not want to grant your user rights to create SQL Agent jobs in
msdb, then you will need to set up the jobs. However, I would suggest
creating a service user account (e.g. User1MaintJobs), grant that account
the needed rights for the job, and assign that account as the job owner.
FWIW,
RLF
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:eLzCr3nlIHA.696@.TK2MSFTNGP05.phx.gbl...
> Hello,
> I have a SQL 2000 user who created some maintenance jobs (they are owned
> by
> her account). She can run them manually but says she can't schedule them.
> If
> I change the owner of the jobs to SA, does that mean she could then modify
> those jobs and get data with SA rights that she didn't have access to with
> her account?
> tia,
> jj
>
I have a SQL 2000 user who created some maintenance jobs (they are owned by
her account). She can run them manually but says she can't schedule them. If
I change the owner of the jobs to SA, does that mean she could then modify
those jobs and get data with SA rights that she didn't have access to with
her account?
tia,
jjjj,
Why can't she schedule her jobs? If she has public rights to the msdb
database, I believe that is enough to allow her to schedule jobs that run
under her own rights.
If you allow these jobs to run at higher levels of security, then certainly
the job could technically get access to additional data.
If you do not want to grant your user rights to create SQL Agent jobs in
msdb, then you will need to set up the jobs. However, I would suggest
creating a service user account (e.g. User1MaintJobs), grant that account
the needed rights for the job, and assign that account as the job owner.
FWIW,
RLF
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:eLzCr3nlIHA.696@.TK2MSFTNGP05.phx.gbl...
> Hello,
> I have a SQL 2000 user who created some maintenance jobs (they are owned
> by
> her account). She can run them manually but says she can't schedule them.
> If
> I change the owner of the jobs to SA, does that mean she could then modify
> those jobs and get data with SA rights that she didn't have access to with
> her account?
> tia,
> jj
>
owner of maintenance jobs-permission hole?
Hello,
I have a SQL 2000 user who created some maintenance jobs (they are owned by
her account). She can run them manually but says she can't schedule them. If
I change the owner of the jobs to SA, does that mean she could then modify
those jobs and get data with SA rights that she didn't have access to with
her account?
tia,
jj
jj,
Why can't she schedule her jobs? If she has public rights to the msdb
database, I believe that is enough to allow her to schedule jobs that run
under her own rights.
If you allow these jobs to run at higher levels of security, then certainly
the job could technically get access to additional data.
If you do not want to grant your user rights to create SQL Agent jobs in
msdb, then you will need to set up the jobs. However, I would suggest
creating a service user account (e.g. User1MaintJobs), grant that account
the needed rights for the job, and assign that account as the job owner.
FWIW,
RLF
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:eLzCr3nlIHA.696@.TK2MSFTNGP05.phx.gbl...
> Hello,
> I have a SQL 2000 user who created some maintenance jobs (they are owned
> by
> her account). She can run them manually but says she can't schedule them.
> If
> I change the owner of the jobs to SA, does that mean she could then modify
> those jobs and get data with SA rights that she didn't have access to with
> her account?
> tia,
> jj
>
I have a SQL 2000 user who created some maintenance jobs (they are owned by
her account). She can run them manually but says she can't schedule them. If
I change the owner of the jobs to SA, does that mean she could then modify
those jobs and get data with SA rights that she didn't have access to with
her account?
tia,
jj
jj,
Why can't she schedule her jobs? If she has public rights to the msdb
database, I believe that is enough to allow her to schedule jobs that run
under her own rights.
If you allow these jobs to run at higher levels of security, then certainly
the job could technically get access to additional data.
If you do not want to grant your user rights to create SQL Agent jobs in
msdb, then you will need to set up the jobs. However, I would suggest
creating a service user account (e.g. User1MaintJobs), grant that account
the needed rights for the job, and assign that account as the job owner.
FWIW,
RLF
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:eLzCr3nlIHA.696@.TK2MSFTNGP05.phx.gbl...
> Hello,
> I have a SQL 2000 user who created some maintenance jobs (they are owned
> by
> her account). She can run them manually but says she can't schedule them.
> If
> I change the owner of the jobs to SA, does that mean she could then modify
> those jobs and get data with SA rights that she didn't have access to with
> her account?
> tia,
> jj
>
owner of dburgent
hi:
I get a database called"linlin".But the owner of database is not dbo or sa,but user "ss".I revert the database into my SQL server.
When I use it with user sa, I need add linlin. before the name of the table and storage processes.But what influnce to do this?
When insert ,update,delete something from linlin with user sa,is it OK?My SQL is personnal edition.
Appreciate your help!
I get a database called"linlin".But the owner of database is not dbo or sa,but user "ss".I revert the database into my SQL server.
When I use it with user sa, I need add linlin. before the name of the table and storage processes.But what influnce to do this?
When insert ,update,delete something from linlin with user sa,is it OK?My SQL is personnal edition.
Appreciate your help!
I bet you that you executed your SQL statements under the context of that user 'ss'. That is why it is not showing as "dbo."
The reason why you have to prefix those tables with the database name is because your connection is not using that database. In your connection string, you can specify the [database] property to the name of the database. Then you won't have to prefix your SQL with the database name from there on.
||| You're right. The SQL statements is under the context of that user 'ss'.
The database is my brother's.And I can not use ss.My brother let me connect database to asp.net.
But what can I do?
My brother let me create a user named "ss",but it desn't work.My "ss" can not connect to the database "linlin".
help me.
The ss user must be granted access to access the database. To do this, in enterprise manager, go to the users. Go to the properties of this user "ss" and under permissions, give the ss DB Owner access to the database.
Owner of DB missing login name
SQL 2K on W2K Server...
ok. created 'user1'. made him a sys admin. created DB#1 with his credentials--he is the owner. everyone uses his credentials to create their odbc's--no problem. within EM, under db user properties for DB#1, it shows he is the owner and that his user name is 'user1' and his login name is 'dbo'.
for some reason today, the login name for this user within DB#1's db properties says <None>, as opposed to dbo. and though this same panel (Database User poperties - User1) shows that he is the owner of the db, checking 'Security-->Logins-->SQL Server Login Properties-->Database Access tab doesn't even have a checkbox under the 'Permit' column. and, when i check the Permit box, and check 'db_owner' in the 'Permit in Database role' window, i receive an error that Error 21002: [SQL-DMO]User 'User1' already exists.
well, i should hope so! but why has his ownership been taken away? why can't i re-add it? all pointers on the net for this error talk about issues with moving or restoring db's--neither of which was done in this case.
help ?need to bump this one up--sry
Originally posted by tojolo
SQL 2K on W2K Server...
ok. created 'user1'. made him a sys admin. created DB#1 with his credentials--he is the owner. everyone uses his credentials to create their odbc's--no problem. within EM, under db user properties for DB#1, it shows he is the owner and that his user name is 'user1' and his login name is 'dbo'.
for some reason today, the login name for this user within DB#1's db properties says <None>, as opposed to dbo. and though this same panel (Database User poperties - User1) shows that he is the owner of the db, checking 'Security-->Logins-->SQL Server Login Properties-->Database Access tab doesn't even have a checkbox under the 'Permit' column. and, when i check the Permit box, and check 'db_owner' in the 'Permit in Database role' window, i receive an error that Error 21002: [SQL-DMO]User 'User1' already exists.
well, i should hope so! but why has his ownership been taken away? why can't i re-add it? all pointers on the net for this error talk about issues with moving or restoring db's--neither of which was done in this case.
help ?
ok. created 'user1'. made him a sys admin. created DB#1 with his credentials--he is the owner. everyone uses his credentials to create their odbc's--no problem. within EM, under db user properties for DB#1, it shows he is the owner and that his user name is 'user1' and his login name is 'dbo'.
for some reason today, the login name for this user within DB#1's db properties says <None>, as opposed to dbo. and though this same panel (Database User poperties - User1) shows that he is the owner of the db, checking 'Security-->Logins-->SQL Server Login Properties-->Database Access tab doesn't even have a checkbox under the 'Permit' column. and, when i check the Permit box, and check 'db_owner' in the 'Permit in Database role' window, i receive an error that Error 21002: [SQL-DMO]User 'User1' already exists.
well, i should hope so! but why has his ownership been taken away? why can't i re-add it? all pointers on the net for this error talk about issues with moving or restoring db's--neither of which was done in this case.
help ?need to bump this one up--sry
Originally posted by tojolo
SQL 2K on W2K Server...
ok. created 'user1'. made him a sys admin. created DB#1 with his credentials--he is the owner. everyone uses his credentials to create their odbc's--no problem. within EM, under db user properties for DB#1, it shows he is the owner and that his user name is 'user1' and his login name is 'dbo'.
for some reason today, the login name for this user within DB#1's db properties says <None>, as opposed to dbo. and though this same panel (Database User poperties - User1) shows that he is the owner of the db, checking 'Security-->Logins-->SQL Server Login Properties-->Database Access tab doesn't even have a checkbox under the 'Permit' column. and, when i check the Permit box, and check 'db_owner' in the 'Permit in Database role' window, i receive an error that Error 21002: [SQL-DMO]User 'User1' already exists.
well, i should hope so! but why has his ownership been taken away? why can't i re-add it? all pointers on the net for this error talk about issues with moving or restoring db's--neither of which was done in this case.
help ?
Owner of database
I've written code that allows a user to create a table according to their specifications. However, when I as another user tries to open the table, I get all sorts of errors. The only reason I can think that this happens is because I dont have sufficient permission/s to access that table. Is this correct, and if so, how do I get around this problem ?
Many thanks.You should GRANT permissions for other user to access the table.
Refer to books online for more information.|||The table owner could also be causing you some greif. In other words if you create 'tbl_one' with 'user_one' and then try and access the table with 'user_two' you may need to explictly identify the tables owner. In other words 'select * from user_one.tbl_one' .
Of course just as Satya mentioned the user would need select permissions on this table (user_one.tbl_one)
Many thanks.You should GRANT permissions for other user to access the table.
Refer to books online for more information.|||The table owner could also be causing you some greif. In other words if you create 'tbl_one' with 'user_one' and then try and access the table with 'user_two' you may need to explictly identify the tables owner. In other words 'select * from user_one.tbl_one' .
Of course just as Satya mentioned the user would need select permissions on this table (user_one.tbl_one)
owner of a temp table
is there a way to query the system table in SQL Server to determine the
owner of a temp table? I doubt this is possible because it seems as
though everyone is aliased as db_owner.<dlukac@.gmail.com> wrote in message
news:1112724040.143022.24070@.z14g2000cwz.googlegro ups.com...
> is there a way to query the system table in SQL Server to determine the
> owner of a temp table? I doubt this is possible because it seems as
> though everyone is aliased as db_owner.
owner of a temp table? I doubt this is possible because it seems as
though everyone is aliased as db_owner.<dlukac@.gmail.com> wrote in message
news:1112724040.143022.24070@.z14g2000cwz.googlegro ups.com...
> is there a way to query the system table in SQL Server to determine the
> owner of a temp table? I doubt this is possible because it seems as
> though everyone is aliased as db_owner.
I'm not sure what you mean - a temp table is unique in the session that
created it, regardless of owner, login or user name (unless it's a global
temp table, of course). Perhaps you can explain what problem you're trying
to solve, or what your aim is?
Simon
Owner of a database
Hello,
What are the effects in working with a database owned by a user 'X'? In other
words, what are the differences whether i.e. the owner is 'sa', a user which is
db_owner, or a "normal" user?
[SQL Server 2K]
Thank You
Joachim
Hi
> What are the effects in working with a database owned by a user 'X'? In
> other
> words, what are the differences whether i.e. the owner is 'sa', a user
> which is
> db_owner, or a "normal" user?
One is that a "normal user" cannot delete/drop any objects created by
another user.
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:eiHwrUAIGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Hello,
> What are the effects in working with a database owned by a user 'X'? In
> other
> words, what are the differences whether i.e. the owner is 'sa', a user
> which is
> db_owner, or a "normal" user?
> [SQL Server 2K]
> Thank You
> Joachim
|||Iam not sure what it is that you want to know but when using SQL
Authentication, you can create you own user say userx and make them
owner of the database i.e assign the role db_owner, There is not
different really with using 'sa', but when you are using sa make sure
you have protected your database correctly
|||You are talking about two different things here.
Making someone the actual owner of a database is NOT the same as putting
them in the db_owner role.
If they are the true owner, their login name will be stored in the
sysdatabases table in the master database, and when they use the database of
which they are the owner, they will have the user name DBO.
A user in the database can be put in the db_owner role, but they still have
their own user name, they are not DBO. Their name is not stored anywhere
outside the database. Within the database they have all the permissions of
the true owner, but they keep their own user name.
Please read about logins, users and database roles in the Books Online.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<Lucky.Ncube@.gmail.com> wrote in message
news:1138016039.779184.79720@.g44g2000cwa.googlegro ups.com...
> Iam not sure what it is that you want to know but when using SQL
> Authentication, you can create you own user say userx and make them
> owner of the database i.e assign the role db_owner, There is not
> different really with using 'sa', but when you are using sa make sure
> you have protected your database correctly
>
What are the effects in working with a database owned by a user 'X'? In other
words, what are the differences whether i.e. the owner is 'sa', a user which is
db_owner, or a "normal" user?
[SQL Server 2K]
Thank You
Joachim
Hi
> What are the effects in working with a database owned by a user 'X'? In
> other
> words, what are the differences whether i.e. the owner is 'sa', a user
> which is
> db_owner, or a "normal" user?
One is that a "normal user" cannot delete/drop any objects created by
another user.
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:eiHwrUAIGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Hello,
> What are the effects in working with a database owned by a user 'X'? In
> other
> words, what are the differences whether i.e. the owner is 'sa', a user
> which is
> db_owner, or a "normal" user?
> [SQL Server 2K]
> Thank You
> Joachim
|||Iam not sure what it is that you want to know but when using SQL
Authentication, you can create you own user say userx and make them
owner of the database i.e assign the role db_owner, There is not
different really with using 'sa', but when you are using sa make sure
you have protected your database correctly
|||You are talking about two different things here.
Making someone the actual owner of a database is NOT the same as putting
them in the db_owner role.
If they are the true owner, their login name will be stored in the
sysdatabases table in the master database, and when they use the database of
which they are the owner, they will have the user name DBO.
A user in the database can be put in the db_owner role, but they still have
their own user name, they are not DBO. Their name is not stored anywhere
outside the database. Within the database they have all the permissions of
the true owner, but they keep their own user name.
Please read about logins, users and database roles in the Books Online.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<Lucky.Ncube@.gmail.com> wrote in message
news:1138016039.779184.79720@.g44g2000cwa.googlegro ups.com...
> Iam not sure what it is that you want to know but when using SQL
> Authentication, you can create you own user say userx and make them
> owner of the database i.e assign the role db_owner, There is not
> different really with using 'sa', but when you are using sa make sure
> you have protected your database correctly
>
Subscribe to:
Posts (Atom)