Database Restore Problem

I've created a Restore Job with owner sa, run as dbo, to refresh a Test database from Production.

After ONE successful refresh from the Production database, this
job fails with:
Msg 3101, Sev 16: Exclusive access could not be obtained because the
database is in use. [SQLSTATE 42000]
Msg 3013, Sev 16: RESTORE DATABASE is terminating abnormally. [SQLSTATE
42000]

There are no users connected to the database or processes running against it.

The syntax is as follows:
ALTER DATABASE DBA_pfaids_test SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE DBA_pfaids_test
FROM DISK = 'd:\PFAIDS\restore\PRODbackup.bak'
WITH REPLACE,
MOVE 'SS2K_prod_empty_Data' TO 'D:\MSSQL\DATA\DBA_pfaids_test_Data.MDF',
MOVE 'SS2K_prod_empty_Log' TO 'D:\MSSQL\DATA\DBA_pfaids_test_Log.LDF'

ALTER DATABASE DBA_pfaids_test SET MULTI_USER

Does this have something to do with the initial refresh from Production?
How can I get it to work?

Thanks.
MaryYou probably have either a (some) thoughtless user(s) who leave their connections open at the end of the day, or scheduled jobs that access the database.

You cannot put a database in single user mode while others are attached. You could preface your script with a call to sp_who2 and capture the returned data in a table. Then use a cursor to inspect the table, and when you find a spid attached to the database you want to restore, kill the spid.

Once you have finished with the cursor, close and deallocate. repeat until no losers (oops ... 'xcuse me ... users) are left in the database. When that occurs, execute the alter to put it into single_user, and proceed with the restore.

Things would run so much better if we just didn't have to let users into our databases ;)|||Thanks for you reply.

Actually, users do not access this database. I created it for testing purposes only, and they don't even know it exists. The restore works fine in Enterprise Manager, but fails when doing it with T-SQL. (I am trying to automate the restore).|||Things would run so much better if we just didn't have to let users into our databases ;)

~"This job would be great if it were'nt for the customers"
-Clerks|||The microsoft tools are getting a bit annoying with how "helpful" they are trying to be. If you have not already, shut down enterprise manager, and close the object browser in Query Analyzer. These two things are the top cause of this error. If that does not help, check to see if you have any extra windows open in Query Analyzer.|||Also check if the QA window you're trying to do the restore in isn't connected to the same database (switch to master to be sure).

Lex

Database ownership

Hi
Can an NT GROUP own a database in SQL2000 ? I'm trying to have users in the group create objects with dbo owner ship by default.
However sp_changedbowner does not seem to recogise the group as a login even though sp_helplogins lists it .
Thanksmay be a bug with sp_changedbowner, when done through EM it works.|||Originally posted by Paul Young
may be a bug with sp_changedbowner, when done through EM it works.

Thanks Paul.

I can't seem to see where to do this in EM. Do you mean by adding the group to the dbo role?|||yup!
under Security, right click on the group, select Properties, then Database Access Tab, select the db, and click on the db_owner.|||Originally posted by Paul Young
yup!
under Security, right click on the group, select Properties, then Database Access Tab, select the db, and click on the db_owner.

Thanks again

However I'm getting tables owned by individual nt users which cannot be seen by other members of that group (i'm testing with users
connecting via query analyzer using nt authentication.

What I'm looking for is to be able to get the database owned by
the nt group so that any table created by the users are owned by dbo
and not the individual users, or at least have it so that all users of the group can see and manipulate the tables.

Any ideas?|||user tables are always use instead of dbo tables. Also, don't quote me on this, I think if you are aliased to dbo and alter a uesr table the table remains under the users'd id, it's only when you drop and create that the user identity changes.

Can you move all the user objects to a holding db and try your tests again?|||Originally posted by Paul Young
user tables are always use instead of dbo tables. Also, don't quote me on this, I think if you are aliased to dbo and alter a uesr table the table remains under the users'd id, it's only when you drop and create that the user identity changes.

Can you move all the user objects to a holding db and try your tests again?

Thanks for your patience. When a sql login owns a database then
all tables created are dbo.table rather than sqllogin.table

This is what I'm trying to achieve with the NT group .

When I create a table under an nt user which is in that nt group (whose sql login has dbo access to the database) the table is created as ntuser.table , and that nt user appears as a user in the database (where it didn't appear before ).

I tried to run the sp_addalias to get around this too but this also gave me an error as if it didn't recognise the nt group sql login.

I think I'm on the right track its just annoying that I can't find any real information about this scenario. My other option is to ensure that all tables are explicitly created with dbo. but not all 3rd party applications are so forgiving .

Thanks again for your help Paul

database ownership

I have a database which has stored procedures which have an owner by the name
of essbase. How do I change the ownership of these stored procedures to dbo
Thanks
Check out sp_changeobjectowner.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Raks" <Raks@.discussions.microsoft.com> wrote in message
news:553BF42A-9184-4624-A62F-A730605CDDD0@.microsoft.com...
> I have a database which has stored procedures which have an owner by the name
> of essbase. How do I change the ownership of these stored procedures to dbo
> Thanks
|||Read up on sp_changeobjectowner within Books Online.
Keith
"Raks" <Raks@.discussions.microsoft.com> wrote in message
news:553BF42A-9184-4624-A62F-A730605CDDD0@.microsoft.com...
> I have a database which has stored procedures which have an owner by the
name
> of essbase. How do I change the ownership of these stored procedures to
dbo
> Thanks
|||Be careful, although it is a best practice that dbo own everything, you may
have an application that refers to this sp by its, 2-part name... If so,
then that application will also have to be changed...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Raks" <Raks@.discussions.microsoft.com> wrote in message
news:553BF42A-9184-4624-A62F-A730605CDDD0@.microsoft.com...
> I have a database which has stored procedures which have an owner by the
name
> of essbase. How do I change the ownership of these stored procedures to
dbo
> Thanks

database ownership

I have a database which has stored procedures which have an owner by the nam
e
of essbase. How do I change the ownership of these stored procedures to dbo
ThanksCheck out sp_changeobjectowner.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Raks" <Raks@.discussions.microsoft.com> wrote in message
news:553BF42A-9184-4624-A62F-A730605CDDD0@.microsoft.com...
> I have a database which has stored procedures which have an owner by the n
ame
> of essbase. How do I change the ownership of these stored procedures to d
bo
> Thanks|||Read up on sp_changeobjectowner within Books Online.
Keith
"Raks" <Raks@.discussions.microsoft.com> wrote in message
news:553BF42A-9184-4624-A62F-A730605CDDD0@.microsoft.com...
> I have a database which has stored procedures which have an owner by the
name
> of essbase. How do I change the ownership of these stored procedures to
dbo
> Thanks|||Be careful, although it is a best practice that dbo own everything, you may
have an application that refers to this sp by its, 2-part name... If so,
then that application will also have to be changed...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Raks" <Raks@.discussions.microsoft.com> wrote in message
news:553BF42A-9184-4624-A62F-A730605CDDD0@.microsoft.com...
> I have a database which has stored procedures which have an owner by the
name
> of essbase. How do I change the ownership of these stored procedures to
dbo
> Thanks

database ownership

I have a database which has stored procedures which have an owner by the name
of essbase. How do I change the ownership of these stored procedures to dbo
ThanksCheck out sp_changeobjectowner.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Raks" <Raks@.discussions.microsoft.com> wrote in message
news:553BF42A-9184-4624-A62F-A730605CDDD0@.microsoft.com...
> I have a database which has stored procedures which have an owner by the name
> of essbase. How do I change the ownership of these stored procedures to dbo
> Thanks|||Read up on sp_changeobjectowner within Books Online.
--
Keith
"Raks" <Raks@.discussions.microsoft.com> wrote in message
news:553BF42A-9184-4624-A62F-A730605CDDD0@.microsoft.com...
> I have a database which has stored procedures which have an owner by the
name
> of essbase. How do I change the ownership of these stored procedures to
dbo
> Thanks|||Be careful, although it is a best practice that dbo own everything, you may
have an application that refers to this sp by its, 2-part name... If so,
then that application will also have to be changed...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Raks" <Raks@.discussions.microsoft.com> wrote in message
news:553BF42A-9184-4624-A62F-A730605CDDD0@.microsoft.com...
> I have a database which has stored procedures which have an owner by the
name
> of essbase. How do I change the ownership of these stored procedures to
dbo
> Thanks

database owner with restrictions

I have group of users that I would like to have the ability to create tables
as dbo owners but would deny them insert,update and delete roles in some
tables.
It that scenario possible? If it is how?
Thanks in advance,
Tony
icwgroupdb_ddladmin role will allow the user to do DDL operations (i.e.
create/delete/alter <obj>). See this for other predefined roles:
http://msdn.microsoft.com/library/en-us/adminsql/ad_security_6ndx.asp
-oj
"tony-icwgroup" <tonyicwgroup@.discussions.microsoft.com> wrote in message
news:CA57FF63-5ABE-4B7C-8D3F-21150CE2BC56@.microsoft.com...
>I have group of users that I would like to have the ability to create
>tables
> as dbo owners but would deny them insert,update and delete roles in some
> tables.
> It that scenario possible? If it is how?
> Thanks in advance,
> Tony
> icwgroup

database owner with restrictions

I have group of users that I would like to have the ability to create tables
as dbo owners but would deny them insert,update and delete roles in some
tables.
It that scenario possible? If it is how?
Thanks in advance,
Tony
icwgroup
db_ddladmin role will allow the user to do DDL operations (i.e.
create/delete/alter <obj>). See this for other predefined roles:
http://msdn.microsoft.com/library/en...urity_6ndx.asp
-oj
"tony-icwgroup" <tonyicwgroup@.discussions.microsoft.com> wrote in message
news:CA57FF63-5ABE-4B7C-8D3F-21150CE2BC56@.microsoft.com...
>I have group of users that I would like to have the ability to create
>tables
> as dbo owners but would deny them insert,update and delete roles in some
> tables.
> It that scenario possible? If it is how?
> Thanks in advance,
> Tony
> icwgroup

database owner with restrictions

I have group of users that I would like to have the ability to create tables
as dbo owners but would deny them insert,update and delete roles in some
tables.
It that scenario possible? If it is how?
Thanks in advance,
Tony
icwgroupdb_ddladmin role will allow the user to do DDL operations (i.e.
create/delete/alter <obj> ). See this for other predefined roles:
http://msdn.microsoft.com/library/e...curity_6ndx.asp
-oj
"tony-icwgroup" <tonyicwgroup@.discussions.microsoft.com> wrote in message
news:CA57FF63-5ABE-4B7C-8D3F-21150CE2BC56@.microsoft.com...
>I have group of users that I would like to have the ability to create
>tables
> as dbo owners but would deny them insert,update and delete roles in some
> tables.
> It that scenario possible? If it is how?
> Thanks in advance,
> Tony
> icwgroup

Database Owner Problems 2005

I am using SQL Server 2005 and I was having a problem with the owner of a particular database. When I go into Database Diagrams for the database I get:

"Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects."

So I tried some different things (assigning different users as the owner) I was only able to assign one user as the owner (someone that is no longer working here), then I deleted that user (oops.). Now I still don't have an owner assigned AND I can't get into the database properties because I don't have permissions.

Any help would be appreciated.You can set sa as the owner of a db:

USE Mydb
GO

EXEC dbo.sp_changedbowner @.loginame = N'sa', @.map = false
GO
It does sound, though, like you don't have eleveated permissions. You might need to log in as sa or get a sysadmin in to run this.|||That worked. Thanks. The thing that was killing me is that I have the permissions to do it. I am the administrator, user, etc. It is mne and another person that have complete access to the database. We are migrating to SQL Sever 2005 from Access.

If you don't mind answering another question regarding SQL Server security. For this particular database, I try to transfer ownership to my login (which is mapped to my network login, Windows Authentication) and I get the following error:

"The proposed new database owner CORE_PROCESS\dkunkle is mapped as user CORE_PROCESS\dkunkle in this database. Parameter name: CORE_PROCESS\dkunkle"

Any thoughts on this error? My login is the owner of another database. This database was created/developed on another system, then moved to the server.|||If you don't mind answering another question regarding SQL Server security. omg - what a liberty :rolleyes:

I think this means that you are trying to map the owner to a login which is already mapped to a user in the database.

Not certain though. What are you executing?|||Poots has got it ... here's the pertinent section from 2005 BOL


sp_changedbowner [ @.loginame = ] 'login' [ , [ @.map= ] remap_alias_flag ]
http://www.dbforums.com/ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/local/collapse.gif</IMG>Arguments

[ @.loginame = ] 'login' Is the login ID of the new owner of the current database. login is sysname, with no default. login must be an already existing SQL Server login or Microsoft Windows user. login cannot become the owner of the current database if it already has access to the database through an existing alias or user security account within the database. To avoid this, drop the alias or user within the current database first.
[ @.map = ] remap_alias_flag Is the value true or false, which indicates whether existing aliases assigned to the old database owner (dbo) are mapped to the new owner of the current database or dropped. remap_alias_flag is varchar(5), with a default of NULL. This indicates that any existing aliases to the old dbo are mapped to the new owner of the current database. false indicates that existing aliases to the old database owner are dropped.

database owner is needed

hi,

on 1 of my servers (actually, the dev. server I have setup up here at home), I must included the database owner everytime I select something or make a db call.

for example

select T.foo from K inner join T on T.id = K.id

must actualy be written like:

select dbo.T.foo from dbo.K inner join dbo.T on dbo.T.id = dbo.K.id

or else it wont work

what did I do wrong for this database owner thing to be a "must" when writing queries on this server.

spec:
sql 2000 sp4You are not connecting as a dbo user.|||

That's wierd. What error are you getting? It should check the dbo owned objects after checking for an object owned by you.

|||

Amir, please update thread.

Thanks,

Derek

|||sorry folks,

I was tied up on another project for the week. I came back and tried to

trouble shoot instead of wasting you guy's time but at the end, i

failed.
basically, here is the situation:

the actual server that will end up hosting the project is setup fine so if i leave out the dbo.* or user.* and just type select * from table it works.

right now, i'm resorting to including the dbo in my queries... and evertime i upload to the server, i do a search in that folder and replace " dbo." with "" in all files.

let me explain how i made this database:

originally, it was on a dev server somewhere in US...
i made the db on my home server. Then created a user and gave it admin privilages over that db.
Then i did "All Tasks > Import" and imported the tables from that server, to this new home db. I had to go back and manually select the primary key for each table, as they got lost during the transfer.

now, when i look at the "Server Explorer" cluster of tables in this new database, i see that they all have (dbo) beside their names, meaning the owner of each table by default is dbo.

i think that should pretty much cover everything....
any clue as to why user "must" be specified?|||

What do you get on the server that requires dbo. when you execute:

select suser_sname(), user_name()

Also, for some object where you have to enter dbo. for the object, execute:

select *
from sysobjects
where name = '<name>'

select *
from information_schema.tables
where table_name = '<name>'

Perhaps this will shed some light? Also what is in @.@.version?

This might just be a stumper that requires a higher power :)

|||

> What do you get on the server that requires dbo. when you execute:

>select suser_sname(), user_name()

| __|_
| foouser | foouser

>select *
>from sysobjects
>where name = '<name>'

irrelevant

> select *
> from information_schema.tables
> where table_name = '<name>'

TABLE_SCHEMA is 'dbo' for all objects

conclusion:
objects where created as dbo when they were imported from the server on the hosting company to the local staging environment;

how can i prevent this when using SQL 2000 enterprise manager for import

(note: from my exp. with sql 2005, this problem wasn't encountered when going through the same steps -- or rather similar steps -- using native sql 2005 import/export functionality within the SQL 2005 studio)


side note:
i rather find a script based answer rather learning to use the GUI, because of my DB2, mysql background -- obviously i'm not gifted when using GUI tools

Database owner change

Hello,

I'm using tableadapters in VWD 2005 Express to link our ASP.NET application to a SQL Server 2000 database. Initially, I used Database Explorer to drop tables into the DataSet object, which picks up the owner of the tables from the database.

Recently, we had to change the owner of all objects in the database, including tables and stored procedures. When we run our application it chokes on stored procedures because the owner of the SP's has changed.

My question is: how can I update tables and tableadapters in the DataSet to reflect the change in owner, without having to recreate everything in DataSet?

We resolved this.

database owner chaining

A question of database owner chaining:
I have a SQL 200 server.
In master the database owner is sa.
In a db called SPOA the database owner is sa. (sp_changedbowner 'sa')
The dbo is linked to login sa.
I create a stored procedure in SPOA:
create proc test_OA as
DECLARE @.object int
DECLARE @.hr int
DECLARE @.src varchar(255), @.desc varchar(255)
EXEC @.hr = sp_OACreate 'SQLDMO.SQLServer', @.object OUT
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
RETURN
END
I create a user in SPOA
sp_addlogin sune
sp_adduser sune
grant execute on test_OA to sune
I connect to SPOA as sune and do EXECUTE test_OA
As expected I get: EXECUTE permission denied on object 'sp_OACreate',
database 'master'
I then give user sune role db_owner.
I connect again to SPOA as sune and do EXECUTE test_OA
And I get: EXECUTE permission denied on object 'sp_OACreate', database
'master'
I expected it to work.
What have I missed?
/kCross database ownership chaining turned is off by default in SQL 2000 SP3+.
You can selectively turn it on in your user database using sp_dboption:
EXEC sp_dboption 'SPOA', 'db chaining', true
Note that you should enable cross-database chaining in an sa-owned database
when only symin role members can create dbo-owned objects in that
database.
Hope this helps.
Dan Guzman
SQL Server MVP
"kurt sune" <apa@.apa.com> wrote in message
news:elLPghnGFHA.2276@.TK2MSFTNGP15.phx.gbl...
>A question of database owner chaining:
> I have a SQL 200 server.
> In master the database owner is sa.
> In a db called SPOA the database owner is sa. (sp_changedbowner 'sa')
> The dbo is linked to login sa.
> I create a stored procedure in SPOA:
> create proc test_OA as
> DECLARE @.object int
> DECLARE @.hr int
> DECLARE @.src varchar(255), @.desc varchar(255)
> EXEC @.hr = sp_OACreate 'SQLDMO.SQLServer', @.object OUT
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
> SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
> RETURN
> END
> I create a user in SPOA
> sp_addlogin sune
> sp_adduser sune
> grant execute on test_OA to sune
> I connect to SPOA as sune and do EXECUTE test_OA
> As expected I get: EXECUTE permission denied on object 'sp_OACreate',
> database 'master'
> I then give user sune role db_owner.
> I connect again to SPOA as sune and do EXECUTE test_OA
> And I get: EXECUTE permission denied on object 'sp_OACreate', database
> 'master'
> I expected it to work.
> What have I missed?
> /k
>|||yes it helped.
THANKS!!!
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uG1JAunGFHA.2732@.TK2MSFTNGP15.phx.gbl...
> Cross database ownership chaining turned is off by default in SQL 2000
SP3+.
> You can selectively turn it on in your user database using sp_dboption:
> EXEC sp_dboption 'SPOA', 'db chaining', true
> Note that you should enable cross-database chaining in an sa-owned
database
> when only symin role members can create dbo-owned objects in that
> database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "kurt sune" <apa@.apa.com> wrote in message
> news:elLPghnGFHA.2276@.TK2MSFTNGP15.phx.gbl...
>|||Does anyone know what table I need to look into for SQL 2000 which stores db
o's name and in VSQL, IP address & port number.
Many Thanks

Database Owner

Dear friends
I have a question,please help me.
I have a database and one of my database user is the owner of my database.
If i detach my database , i can attach it on other instance of SQL SERVER
with user of 'sa'.I want to stop this action in term of security.
Thanks
Mehdi
Hi,
SA is the system administrator and you cant restrict the permissions to
attach the database.
Thanks
Hari
SQL Server MVP
"Mehdi" <Mehdi@.discussions.microsoft.com> wrote in message
news:E312383F-AB28-4B51-BE85-59B605BE118F@.microsoft.com...
> Dear friends
> I have a question,please help me.
> I have a database and one of my database user is the owner of my database.
> If i detach my database , i can attach it on other instance of SQL SERVER
> with user of 'sa'.I want to stop this action in term of security.
> Thanks
> Mehdi
>
|||However, AFTER you've re-attached the database you can change the
database owner using sp_changedbowner.
ALI
Hari Prasad wrote:[vbcol=seagreen]
> Hi,
> SA is the system administrator and you cant restrict the permissions to
> attach the database.
> Thanks
> Hari
> SQL Server MVP
> "Mehdi" <Mehdi@.discussions.microsoft.com> wrote in message
> news:E312383F-AB28-4B51-BE85-59B605BE118F@.microsoft.com...
|||You can achieve this security target only outside of SQL - restricting the
access to the detached database files. If someone can get access to those
files - he has access to the data inside, no way to prevent it.
Encryption of database files would be appropriate here - but it is not
available in SQL - only network communication and procedure bodies can be
encrypted.
Cheers
Wojtek
"Mehdi" <Mehdi@.discussions.microsoft.com> wrote in message
news:E312383F-AB28-4B51-BE85-59B605BE118F@.microsoft.com...
> Dear friends
> I have a question,please help me.
> I have a database and one of my database user is the owner of my database.
> If i detach my database , i can attach it on other instance of SQL SERVER
> with user of 'sa'.I want to stop this action in term of security.
> Thanks
> Mehdi
>

Database Owner

Dear friends
I have a question,please help me.
I have a database and one of my database user is the owner of my database.
If i detach my database , i can attach it on other instance of SQL SERVER
with user of 'sa'.I want to stop this action in term of security.
Thanks
MehdiHi,
SA is the system administrator and you cant restrict the permissions to
attach the database.
Thanks
Hari
SQL Server MVP
"Mehdi" <Mehdi@.discussions.microsoft.com> wrote in message
news:E312383F-AB28-4B51-BE85-59B605BE118F@.microsoft.com...
> Dear friends
> I have a question,please help me.
> I have a database and one of my database user is the owner of my database.
> If i detach my database , i can attach it on other instance of SQL SERVER
> with user of 'sa'.I want to stop this action in term of security.
> Thanks
> Mehdi
>|||However, AFTER you've re-attached the database you can change the
database owner using sp_changedbowner.
ALI
Hari Prasad wrote:[vbcol=seagreen]
> Hi,
> SA is the system administrator and you cant restrict the permissions to
> attach the database.
> Thanks
> Hari
> SQL Server MVP
> "Mehdi" <Mehdi@.discussions.microsoft.com> wrote in message
> news:E312383F-AB28-4B51-BE85-59B605BE118F@.microsoft.com...|||You can achieve this security target only outside of SQL - restricting the
access to the detached database files. If someone can get access to those
files - he has access to the data inside, no way to prevent it.
Encryption of database files would be appropriate here - but it is not
available in SQL - only network communication and procedure bodies can be
encrypted.
Cheers
Wojtek
"Mehdi" <Mehdi@.discussions.microsoft.com> wrote in message
news:E312383F-AB28-4B51-BE85-59B605BE118F@.microsoft.com...
> Dear friends
> I have a question,please help me.
> I have a database and one of my database user is the owner of my database.
> If i detach my database , i can attach it on other instance of SQL SERVER
> with user of 'sa'.I want to stop this action in term of security.
> Thanks
> Mehdi
>

Database Owner

Dear friends
I have a question,please help me.
I have a database and one of my database user is the owner of my database.
If i detach my database , i can attach it on other instance of SQL SERVER
with user of 'sa'.I want to stop this action in term of security.
Thanks
MehdiHi,
SA is the system administrator and you cant restrict the permissions to
attach the database.
Thanks
Hari
SQL Server MVP
"Mehdi" <Mehdi@.discussions.microsoft.com> wrote in message
news:E312383F-AB28-4B51-BE85-59B605BE118F@.microsoft.com...
> Dear friends
> I have a question,please help me.
> I have a database and one of my database user is the owner of my database.
> If i detach my database , i can attach it on other instance of SQL SERVER
> with user of 'sa'.I want to stop this action in term of security.
> Thanks
> Mehdi
>|||However, AFTER you've re-attached the database you can change the
database owner using sp_changedbowner.
ALI
Hari Prasad wrote:
> Hi,
> SA is the system administrator and you cant restrict the permissions to
> attach the database.
> Thanks
> Hari
> SQL Server MVP
> "Mehdi" <Mehdi@.discussions.microsoft.com> wrote in message
> news:E312383F-AB28-4B51-BE85-59B605BE118F@.microsoft.com...
> > Dear friends
> >
> > I have a question,please help me.
> > I have a database and one of my database user is the owner of my database.
> > If i detach my database , i can attach it on other instance of SQL SERVER
> > with user of 'sa'.I want to stop this action in term of security.
> >
> > Thanks
> > Mehdi
> >|||You can achieve this security target only outside of SQL - restricting the
access to the detached database files. If someone can get access to those
files - he has access to the data inside, no way to prevent it.
Encryption of database files would be appropriate here - but it is not
available in SQL - only network communication and procedure bodies can be
encrypted.
Cheers
Wojtek
"Mehdi" <Mehdi@.discussions.microsoft.com> wrote in message
news:E312383F-AB28-4B51-BE85-59B605BE118F@.microsoft.com...
> Dear friends
> I have a question,please help me.
> I have a database and one of my database user is the owner of my database.
> If i detach my database , i can attach it on other instance of SQL SERVER
> with user of 'sa'.I want to stop this action in term of security.
> Thanks
> Mehdi
>