Logins, Object Ownership And Sp_detach_db/sp_attach_db
Apr 4, 2000
Here's one. :)
I have Database A in Server 1 which contains objects (tables and SPs) that are owned by users other than dbo. Then I detach this database from Server 1 using sp_detach_db and attach it to Server2 (sp_attach_db). Even if the same logins exist or are created in Server 2, I still won't be able to access the objects in Database A using these logins. I know that this happens because of different SIDs for the same login names across the 2 servers. So given that I want to achieve this without having to use the Import Wizard or any other method, how do I go around this lil bit of a problem? Any thoughts? :)
Thanks!
View 1 Replies
ADVERTISEMENT
Feb 16, 2007
A co-worker recently quit the company, and all of his storedprocedures and DTS packages are apparently under his name. Is thereany way to change his account to another (ie administrator or SA)??I am afraid to delete his account in fear all the stored procedureswill be lost. (we have backups, but ... ugh)
View 1 Replies
View Related
Feb 11, 2008
I have a user who has been granted db_owner rights to a database and want him to be able create new views using the dbo schema. When the user creates a view right now, the schema created is under their username. How can I change this so that when a user creates a new view, they always create it under the dbo schema.
View 3 Replies
View Related
Jan 9, 2007
Hi all,
I have a problem while executing a stored procedure. I have created a database called "cpd" and created some stored procedures. for all my stored procedure the owner is "CPDUSER". when ever i am executing any stored procedures i have to write the user name first else it is not working.
let's say i have a stored procedure called "cp_checklogin". it takes 2 parameters. to execute this i have to write
exec cpduser.cp_checklogin 'admin@jk.com', 'admin'
but i don't want to write the user name there. and if without username "CPDUSER" i am trying to execute the stored procedure it is throwing me the error that "the stored procedure cp_checklogin is not exist in the database". can anybody suggest me. it's very urgent.
Thanks in advance
Krishna
View 4 Replies
View Related
Jun 26, 2006
we have a group of developers which have created and asked us (DBAs) tocreate many objects in the databases including tables / storedprocedures / functions / etc.since our company is growing, however we have an increasing amount ofobjects that have either been abandoned or have several versions.in an effort to clean of the huge amount of clutter and anytime thatsomething simple like a stored proc needs to change, it is almostimpossible to predict exactly where we will see negative effects ofthis change.i am looking for a system (preferably without developing our own tool)that would keep track of history of database objects (in terms of whocreated it and what purpose it has) as well as link that to all thedevelopers/users we might need to notify of any changes to that object.Also, this should be linked to the application which rely on theobject.bottom line... every object in the database needs to have at least 1corresponding contact as well as the applications which us it.with this information, we can much more easily maintain objects in ourDBs.thx
View 3 Replies
View Related
Oct 25, 1999
Logged in under a login id that is a db_owner on a database, I can not bind a rule owned by dbo to a table owned by dbo.
Following does not work:
EXEC sp_bindrule 'dbo.rule1', 'table1.column1'
error:You do not own a table named 'table1' that has a column named 'column1'.
Following does not work:
EXEC sp_bindrule 'dbo.rule1', 'dbo.table1.column1'
error: Rule,table and user datatype must be in current database
To bind the rule, I had to change table ownership to my login id and then bind the rule. I then changed table ownership back to dbo. This method seems odd. I do not have this problem when binding defaults to dbo owned tables.
Any one else run into this?
View 2 Replies
View Related
Dec 14, 2007
I have read the previous threads on the bugs with this task mainly: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1438968&SiteID=1 . These are great posts that helpmed me avoid wasting time. I haven't seen one yet that addresses copying an entire database including the sql server logins.
I would like to import the ENTIRE database from one (2005) server to another(2005) using the SSIS Transfer SQL Object task (not just sprocs,tables,views and functions). I have figured out how to pull the tables,views,sprocs and functions ... by using an execute sql task to drop these objects. But I cannot get this to work for users since the user dbo cannot be dropped and guest can only be disabled. I am creating a new database (this is the database where the sql objects will be copied to) via management studio to test this. There has to ba a way to get this working ... Microsoft must have published some sort of KB article on this task or a Script Task using SMO object calls. If need be I can drop the entire database on the target machine and have SSIS recreate it.
The only reason I'm willing to take a risk with SSIS rather than backup and restore is because of time constraints (I assume the SSIS task is faster) and backup storage administration.
declare @name varchar(200)
declare @object varchar(200)
DECLARE object_cursor CURSOR READ_ONLY FORWARD_ONLY FOR
select table_name,table_type from INFORMATION_SCHEMA.TABLES
union
Select name,'SPROC' table_type from sys.procedures Where [type] = 'P' and is_ms_shipped = 0 and [name] not like 'sp[_]%diagram%'
union
select name,'FN' from sys.objects where type_desc like '%FUNCTION'
OPEN object_cursor
FETCH NEXT FROM object_cursor INTO @name,@object
IF @@FETCH_STATUS <> 0
PRINT ' <<None>>'
WHILE @@FETCH_STATUS = 0
BEGIN
if @object = 'BASE TABLE'
begin
exec ('drop table ' + @name)
end
else if @object = 'VIEW'
begin
exec ('drop view ' + @name)
end
else if @object = 'SPROC'
begin
exec ('drop procedure ' + @name)
end
else if @object = 'FN'
begin
exec ('drop function ' + @name)
end
FETCH NEXT FROM object_cursor INTO @name,@object
END
CLOSE object_cursor
DEALLOCATE object_cursor
View 4 Replies
View Related
May 19, 2000
I am moving an entire server on to a brand new box, I was going to use sp_detach_db and sp_attach_db to move all the databases including msdb, master, model, and distribution. The new server is going to be brought up with the same name and ip address as the old box and of course the old box will go off line. Are there any problems using sp_detach and sp_attach_db with these system databases?
View 2 Replies
View Related
Jan 21, 2007
ok, first, I know... I forgot to run a backup of the master database, and I forgot to run a script to caputure logins. Not that that is out of the way... I need to recreate the logins under the Securities tab below the databases. All the company databases have the user names and passwords assigned to them, but they are not able to login, because they are not able to authenticate to the SQL server first.
Is there a script that someone has that will copy the company database security info for the users and recreate them in the SQL security tab?
I know that I can rebuild them manually, but I need to delete them first in the application software, then delete them from the databases, and then recreate them in the application software... and as simple as that sounds... it is a slow moving process.
Any assistance would be greatly appreciated.
Thanks,
John
View 3 Replies
View Related
Aug 31, 2005
We have a nightly DTS package that recreates a 'day-old' Student info database from downloads of our mainframe student database. It creates two temporary databases (Stage1 and Stage2), folds the Stage1 tables into the Stage2 database (I won't bore you with why), detaches the Stage2 database and the current 'day old' Student database, changes the names of the database files (Student to Old_student, Stage2 to Student) and re-attaches the new student database. The DTS tasks that detach the databases KILL any processes that are attached to the databases first.
The problem that we're having is that the detaches of the Stage2 and Student databases work fine for weeks or even months and then start failing. (Even when I haven't changed anything!) I've tried a few things to try and find why, but haven't had any luck.
What could be causing the failures and how can I trap the cause?
Thanks in advance!
View 2 Replies
View Related
Jul 20, 2005
Hi,After a power outage (and UPS failure), my boss's SQL Server 2000 won'tstart (or crashes quickly or something). We don't have any backups, as it'sa test server. Is there a way to import the database files into a new SQLinstallation? By "installation," I mean either a re-installed SS, a secondinstallation on the same PC, or maybe even on another PC. In another words,given only the files sitting on the harddrive, is there a way to "import"the DBs in those files into SS?Sp_attach_db would probably be the answer, except it seems to requirerunning sp_detach_db...which I cannot do, as SS is not working.Thanks in advance,Jay
View 2 Replies
View Related
May 16, 2000
Can anyone spread any light on the following.
I've created a database of 2 tables which exists over 2 drives. I had a 6 Gig log file on one drive and a 1 Gig log file on the other so I used sp_detach_db to detach it. I then deleted the log files and attempted to sp_attach_db with the following:
"sp_attach_db @dbname =N'cwc_load', @filename1 = N'f:cwc_loadcwc_load_data.mdf', @filename2 = N'e:cwc_loadcwc_load_data2.mdf'"
which are the locations of the .mdf files.
I get the error messages 5105 and 945 (which cannot be tracked down using either the Microsoft site or on-line help) but the messages are "Device Activation error. The physical file name 'F:cwc_loadcwc_load_log.ldf' may be incorrect"
"Database 'cwc_load' cannot be opened because some of the files cannot be activated"
which to me tends to suggest that the database is looking for the log files (now deleted).
I've tried forcing a new log file I created using the same locations for the mdfs. I've tried using create database cwc_load on primary (filename = 'etc) which gives the same error message, please tell me someone out there has a resolution for this.
View 2 Replies
View Related
Aug 6, 2002
As part of moving to SQL2K from SQL7 I'm doing sp_detach_db and sp_attach_db on the source server. For those databases that have multiple .ldf files do I need to add a separate filename= line to the sp_attach_db command for each .ldf, or only reference the original .ldf in a single filename?
Thanks,
AL
P.S. Given that I've done a few databases already, what's the effect of entering only the single filename line if multiple filename lines were required?
View 1 Replies
View Related
Jul 17, 2001
Hello,
I would move a Database to another server. I try to use DTS but I have problems with this process because DB have big tables, I think. I try to use DETACH and ATTACH procedures but logins doesn't export. And more, in new server there are already logins from another DBs.
What's the best way to solve this problem?
Please, help
Thanks
View 3 Replies
View Related
Apr 3, 2007
I am a systems analyst and work with an app that runs against 2 SQL Server DBs. Though I have some familiarity with SQL Server and SQL, I am not a DBA.
The app executable is tied to a Windows service.
When we install the app, we run a process that builds 2 dbs to include:
Tables, indexes, stored procedures, views and user accounts.
SQL Server is set up for mixed mode authentication.
Normally, the dbs run off the local db user accounts which are tied to local logins with the same names.
We have a client that wants to remove our standard logins so that they can run on only a Windows login.
I know I should be able to tie the db users to a Windows login.
And I can do the same for the service.
But I am at a loss as to how to get this done.
How do you associate db users with a Windows login?
When I have tried sp_change_users_login I get an error that the Windows login does not exist. (Though I have added the Windows account to the DB.)
Hope this all makes sense.
View 2 Replies
View Related
Apr 23, 2001
how do i use "sp_attach_db" properly so that i can make a new database with only the .mdf file?
View 2 Replies
View Related
Nov 9, 2000
Does SQL Server Agent need to be running in order to execute the
sp_attach_db stored procedure?
I am getting device activation errors while running this stored procedure.
Is there any other way to attach those files back to the SQL Server.
PLEASE HELP
View 1 Replies
View Related
Nov 15, 2006
I am trying to use sp_attach_db SP.
I have a dadafile and a log file at D:Asset
which I am trying to extract using
EXECUTE sp_attach_db 'Asset',
'D:AssetAsset_data.mdf',
'D:Assetasset_log.ldf'
can any one of u tell where i went wrong
I am getting this errror "Device activation error. The physical file name 'D:AssetaaAsset_data.mdf' may be incorrect."
I am trying to use the database provided from
http://www.trigonblue.com/sp_download.htm#Diagram
to practise stored procedures.
Thanks in advance
View 4 Replies
View Related
Sep 20, 2000
I had a SQL Server falure. I rebiuld Master and tried to attach my database
with sp_attach_db? but get an error
Location: pageref.cpp:3931
Expression: rowLog.RowCount () == 1 || pPage->IsEmpty ()
SPID: 10
Process ID: 119
Connection Broken
View 1 Replies
View Related
Dec 6, 2001
Hi,
I'm doing some work for a client who has not been backing up
his databases. After a server crash he has been left with
the .MDF and .LDF files. I have managed to successfully
use sp_attach_db on all but two databases. When attempting
to use sp_attach_db I get the following error message :
1> sp_attach_db @dbname = N'CSGDEV', @filename1 = N'e:SQL_DataDirDataCSGDEV_Data.MDF', @filename2 = N'e:SQL_DataDirDataCSGDEV_Log.LDF'
2> go
Warning: sort order 51 in database differs from server sort order of 52.
Warning: Unicode comparison flags 196608 in database differs from server
Unicode comparison flags of 196609.
Msg 1816, Level 16, State 1, Server CSGSERVER, Line 1
Could not attach the database because the character set, sort order, or Unicode
collation for the database differs from this server.
Warning: sort order 51 in database differs from server sort order of 52.
Warning: Unicode comparison flags 196608 in database differs from server
Unicode comparison flags of 196609.
Msg 1816, Level 16, State 1, Server CSGSERVER, Line 1
Could not attach the database because the character set, sort order, or Unicode
collation for the database differs from this server.
I'd appreciate any ideas. I'm not a SQL Server expert (heck, I'm not even
a Windows expert...) as you can probably tell.
Thanks and cheers,
Deeran
View 1 Replies
View Related
Feb 2, 2004
I entered the following in the Query Analyzer and got the result shown:
EXEC sp_attach_db @dbname = N'DS2004',
@filename1 = N'd:ds2004_data.mdf',
@filename2 = N'd:ds2004_log.Ldf'
Server: Msg 5172, Level 16, State 15, Line 1
The header for file 'd:ds2004_data.MDF' is not a valid database file header. The PageAudit property is incorrect
I can find nothing about PageAudit property.
What is it and how do I need to modify the command?
Many thanks
View 2 Replies
View Related
Jul 20, 2005
HiWith the sql server on line help , The syntax for the sp_attach_db hasthe file name .It is aslo given that max of 16 files can be geven. Iattached a db with a single d.mdf .could u pls tell what r the 16 file types or how a db can be attachedwith 16 files.With thanks
View 1 Replies
View Related
Nov 12, 2003
Hello,
We use local server windows groups to allow or disallow use of our SQL databases. We are now being asked to move off of our current server. My thoughts are to dettach and attach the database. What is going to happen to all my permissions based on the user groups of our old server? I intend to create the same group names on the new server but they will be newservergroupname as opposed to oldservergroupname. Is it just a case of scripting the database user, roles and object level permissions an manually changing oldserver to newserver?
View 3 Replies
View Related
Jul 20, 2005
I am attaching a database with 3 data files.When I execute "exe sp_attache_db..." I obtain this error:database 'POINT' cannot be opened because some of the files could not beactivated.I have deleted its LDF file.Usually I detach my db, then I delete transaction log, and reattach 3 datafiles...Now it doesn'work!!!!!!!!!!Someone can help me?Thanks.
View 1 Replies
View Related
Oct 29, 2007
hi,
My problem is, I am going to ship MSDE with our product. Now the problem is that if some one copies the .MDF and .LDF files and uses "sp_attach_db" to attach the db files to his own server then my database will be exposed to him. I have tried this and it gets exosed. I cant deploy NT Authentication for the database, bcoz if the end user installes Enterprise manager then my database will be exposed to him. Is there any way that i can sequre the LDF and MDF files of my database so that no one can use that with sp_attach_db? I m using MSDE(Sql server 2000).
View 10 Replies
View Related
Sep 26, 2007
I successfully install SQL Express silently.
Then I try the following
-S .INSTANCE -Q "sp_attach_db 'ATTACH_AS_NAME', 'C:Program FilesAPP_NAMESQLDataAPP_DB.MDF'"
This works...sometimes. But Many times I wll get a permissions error on the folder "C:Program FilesAPP_NAME". If I go and give "Everyone" "Full Permission" to the folder...it works fine.
My questions are:
1) What system users need access (and at what level) to this folder to do an attach? Because obviously giving everyone full access is bad.
2) Is there a better method for deploying a populated database with my application that this?
View 9 Replies
View Related
Mar 1, 2001
Hi,all.
I tried to issue:
sp_attach_db pubs,'F:anthonypubs.mdf','F:anthonypubs.ldf'
where the pubs.mdf and pubs.ldf are on a remote mapped drive F:,
I got error:
Server: Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name 'f:anthonypubs.ldf' may
be incorrect.
Is attaching a database ONLY supported for local servers ??
What about creating database on remote drive? (I have same error msg back)
Thx
Anthony
View 1 Replies
View Related
Apr 25, 2007
Here is the error, I receive:
Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name 'L:Program FilesMicrosoft SQL ServerMSSQLLOGTestDB_Log.LDF' may be incorrect.
Device activation error. The physical file name 'L:Program FilesMicrosoft SQL ServerMSSQLLOGTestDB_Log.LDF' may be incorrect.
Msg 5170, Level 16, State 1, Line 1
Cannot create file 'L:Program FilesMicrosoft SQL ServerMSSQLLOGTestDB_log.LDF' because it already exists.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'TestDB'. CREATE DATABASE is aborted.
Here is my attach script:
USE [master]
GO
sp_attach_db @dbname= N'TestDB',
@filename1 = N'S:Program FilesMicrosoft SQL ServerMSSQLDataTestDB.MDF',
@filename2 = N'L:Program FilesMicrosoft SQL ServerMSSQLLOGTestDB_Log.LDF'
This server has a Raid 5 Disk Array partitioned into three Logic Drives C:, S:, L:. Not sure, why the server was configured this way. If I attach the database with the data and log files on the same logical drive, the DB attaches successfully. When I try to put the data and log files on separate logical drives, it fails. Issue seems to be with the drive partitioning. Any Ideas?
View 4 Replies
View Related
Sep 20, 2007
Sometime ago, I had read a posting here about where in the system we can specify default owner of all objects in user database as being a DBO.
The topic came about, because somebody found out that if an object referred to in a query, does not have a fully qualified name, then it takes some time for SQL server to figure out what the owner of the object is; this causes performance problems.
e.g. select * from employees and select * from dbo.employees.
If anybody knows what that setting is...I will really appreciate it
Regards
Paresh Motiwala
Boston, USA
View 4 Replies
View Related
Apr 1, 2008
My system was crashing so I reinstalled everything. including SQL 2005 Express with management studio. I renamed the computer from the previous install. but for some reason the sql 2005 express management studio remembers the old machine name even when I create a new DB inside the management studio with the new computer name.when I try to change the ownership in properties-files it cant find the 'new computer nameuser'i double checked the computer name its correct. The funny thing is i installed express with the 'new computerame' anyone have a clue that may help with this what file needs to be edited?Thanks inadvance for any help.
View 2 Replies
View Related
Jul 5, 2001
Is there a way in SS 7.0 to change ownership of an object from a user owned object to dbo owned object other than dropping the object and re-creating it? E.G. juser.table to dbo.table
Thank you,
GaetonC
View 2 Replies
View Related
Jul 26, 2000
I have tables created by a user called "toms". He has dbo permission etc.
I want to make all these tables to be owned by sa ( dbo). How do I do that?
the owner of the database is sa
thanks,
Rachel
View 1 Replies
View Related
Nov 17, 1999
Is there anyway to change the ownership of a table.. I have developers who create tables and the tables are then shown to be owned by them.. These developers are in the db_owner database role.. The application will not run successfully unless the table is owned by the dbo..
The only way I see that I can handle this is to have them generate a script and have myself (full sa) run it..
Any other suggestions?
Thanks
View 1 Replies
View Related
