How do I change the Database owner for a MODEL database?
When I open the properties for a Model database, it shows "UNKNOWN" as the database owner, and I can't run the command 'sp_changedbowner 'sa' as it's not allowed to run on master, model, and tempdb.
Any help?
Dear All,
I have a user with DBOWNER,DBCREATOR role and access is set to deny on viewing any other databases. Now when I connect using this user and try to restore the database. after restore, I can not open it or access it using the same user.
what is causing this.
Thanks,
Hi All
I have my program create and load (restore) a database, then create a login and make it dbowner by running a script like below via OSQL:
EXEC sp_addlinkedserver 'SETUP1myserver','SQL Server'
EXEC sp_serveroption 'SETUP1myserver','DATA ACCESS', 'TRUE'
GO
DROP DATABASE [mydb]
GO
EXEC sp_droplogin 'mylogin'
GO
CREATE DATABASE [mydb]
ON (NAME = N'mydb_data', FILENAME = N'C:AppfolderSQLmydb.mdf' , SIZE = 100, FILEGROWTH = 10%)
LOG ON (NAME = N'mydb_log', FILENAME = N'C:AppfolderSQLmydb.ldf' , FILEGROWTH = 10%)
COLLATE Latin1_General_CI_AS
GO
RESTORE DATABASE mydb FROM DISK = 'C:AppfolderSetupmydb.bak' WITH REPLACE,
MOVE 'mydb_data' TO 'C:AppfolderSQLmydb.mdf',
MOVE 'mydb_log' TO 'C:AppfolderSQLmydb.ldf'
GO
EXEC sp_addlogin 'mylogin','pass1$'
GO
USE mydb
EXEC sp_changedbowner 'mylogin'
GO
This works fine to a point. The program can then connect to the database and carry out normal processing with the "mylogin" user/password but it cannot do administration activities that a dbowner can do.
How do I create a login that does have true dbowner rights?
TIA
Sed Mayne
Does anyone know how to change the database owner to sa (dbo)?
View 2 Replies View RelatedHow do I loop through a database and display all the table name in it
then use
SP_ChangeObjectOwner "TABLE_NAME","dbo"
where table_owner is OBI
In a nut shell, how do I loop through a database and isplay name ?
HiWe have a script that allows customers to change the users of thedatabase including the database owner. This script can be run at anytime. However, sometimes (and it really is only sometimes!) when thefollowing statement executes:"exec sp_changedbowner @USER_OWNER"the following error is reported:"The proposed new database owner is already a user in the database."I have checked the other postings and the Books Online but cannot finda way to query the current name of the dbowner i.e. the value of@USER_OWNER above. We have to be able to do this within the script sothat if the @USER_OWNER variable is already the dbowner we do notbother to execute the statement as otherwise this could display theerror. Can anybody help?Thanks in AdvancePaul
View 5 Replies View RelatedRunning 2005 Beta 3 Refresh. When I first deploy, it works fine. Subsequent deployments yield the following error:
View 9 Replies View Related
our application user is a db owner and my auditor has asked me to change the permission.
what permission should be give for the application user who can access all the schema. create temprorary table and drop
tables.
Without giving dbowner how can we grant permission to user to alter/create procs?
View 3 Replies View Related
Hi All
I have the following questions regrading T-SQL
1. How to assign database role "db_owner" to model database using T-SQL?
2. How to grant a window login public access to master database
Thanks.
Hi, I am trying to create an app that will work exactly like the inbox section in face book.
Do you have any database model I can use as a reference?
it works based on threads.
I am having issues with data redundancy any help will be appreciated.
Thanks
I need to develop a language specific dwh, meaning that descriptions of products are available from a SAP system in multiple languages. English is the most important language and that is the standard. But, there are also requirements of countries that wants productdescriptions in their language.
Productnr Productdesc Language
1 product EN
1 produkt DE
One option is to column the descriptions, but that is not very elegantly. I was thinking of using bridge tables to model this but you have to always select a language in a filter (I think)..
I'm thinking of a technical solution, such that when a user logs on, the language is determined and a view determines whether to pick a certain product table specific for a certain language. But then I don't have the opportunity to interchange the different language specific fields in a report (or in my case PowerPivot).
We have the following scenario,
We have our Production server having database on which Few DTS packages execute every night. Most of them have Bulk Insert stored procedures running.
SO we have to set Recovery Model of the database to simple for that period of time, otherwise it will blow up our logs.
Is there any way we can set up log shipping between our production and standby server, but pause it for some time, set recovery model of primary db to simple, execute DTS Bulk Insert Jobs, Bring it Back to Full recovery Model AND finally bring back Log SHipping.
It it possible, if yes how can we achieve this.
If not what could be another DR solution in this scenario.
Thanks Much
Tejinder
How can I move the Model database to another location (i.e. from one drive / file location to another)?
I tried sp_detach_db (to be followed up with a sp_attach_db statement) but it rejected it because Model is a "system" database.
I tried (and it failed with a "Modify file command failed" message):
ALTER DATABASE model
modify file
(NAME='modeldev',FILENAME='f:MSSQL7DATAmodel.MDF')
I could not locate any help about moving model in books online (BOL).
Any ideas?
I have a question.
I have a database on Recovery model FULL, i do know that i have to use a Complete Backups and Log backups for it, my question is
If i made a Complete backup, what happens with the transaction log? same size? i mean need a backup log to clean it ? .
A complete backup on SQL Server doesn't inclued a log backup?
I am receiving this message in my MOM Server:
The system stored procedure sp_helpdb, which is used to gather information about the databases, has returned an error that may indicate that it cannot determine the db owner for the database [model]. Here are the details: sp_helpdb @dbname='model' on SQL Server Instance: INSTANCENAME. Error number: 515, Error Information: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column '', table ''; column does not allow nulls. INSERT fails.
So, I run this script in the Master DB:
select name, suser_sname(sid) from master.dbo.sysdatabases where suser_sname(sid) is NULL
And I get the result that the db_owner is set to NULL!
I know that it is not possible to change the DB Owner of the model database but is there any workaround to solve this without reinstalling or similar solutions?
Hi, my 'model' database corrupted and my SQL Server 2005 Express can't be started.
I believe I have the SAME problem as his:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=455524&SiteID=1
but when I try the command start sqlservr.exe -c -T3608, an error message appeared:
"Your SQL Server installation is either corrupt of has been tampered
with (Error getting instance ID from name). Please uninstal then re-run
setup to correct this problem"
Well, the problem is that I have an ActiveBPEL database inside, and I really need a way without reinstalling it.
Any solution??
Thanks,
Edwin
Does anyone have a sample of what a database would look like that would support multiple clients in a single ASP.net application?
Thank you,
Hi Everyone,
I am a newbie to SQL Server Admin. Today when I ran Dbcc newalloc on Model it is showing
corruption on syobjects with a 2525 error. I am not sure how to fix this problem? Since
this is a critical payroll application server, I need to solve it at the earliest.
If anybody has faced this problem earlier or knows how to solve, please respond to the following
address: vsrikanth@ameren.com
Thanx for all responses
Srikanth
I cannot think of any reason, in our environment, why I would recover the model database. Change framework has all databases coming from DEV & QA before landing on PROD. We have never used the model database as framework of new databases either.
So, if I discontinued backup of the database, what is my recovery method if it become corrupt? Since mine is not used, can I simply copy it from another server?
Last week we reset the Model datable to autogrow. Now I get an error when I try to go into it. (I discovered this since I can no longer go into the interface of the program). I wasn't here when the changes were made to the Database Model, but the change was made because the transaction log was filled up.
Help.. I'm such a newbie.
anke
Every time a new database is created on my server,my log files and database files automatically default to the following to the C drive in the following path: C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData. Is there a way to get them to default to different drives?
For example, right now I have my data files on our F Drive, which is a RAID 5 and my log files on our G Drive which is a RAID 1. Every time an out of the box application installs a database, it does so on the C Drive. I want data files to default to my F Drive and log files to default to my G drive? Is this possible?
Hi:
I am having lot of log problems with Subscription databases. Currently all my subscription databases are on Full recovery mode. I am thinking to change them to simple because I don't I will be doing point in time recovery of them.
Do the subcription databases have to be on Full mode? Can I change them to simple to keep my log small and then I do not have to backups of my logs also? Please let me know.
Thanks
Hi folks,
I do not have a backup of the model database and have had to rebuild SQL Server 2000 once already. All my databases except model and tempdb have the collation SQL_Latin1_General_CP1_CI_AS those two have Latin1_General_CI_AS.
During SQL rebuild I've restored over master and msdb and they work fine. Only came across problem with stored procedure and I'm lost as to how to alter model without a backup of it.
Any easy way to change model collation?
The command listed in subject fails as it is a system table.
Is it as simple as going into single user mode and trying that command again?
Help please this is day 3 of my little nightmare.
I am using sql2012 se and we want to use couchbase to store some data as documents. So the database will be Hybrid(partly SQLServer and Partly couchbase). However the database is still in the design phase. What are the things that we should keep in mind when designing this database from a design perspective? Our database which was completely SQLServer based(RDBMS) was using GUIDs everywhere based off NEWID() and prime goal is to get rid of GUIDs for the most part.
View 6 Replies View RelatedHi there
We are thinking to create a small scale of the "Software On-Demand" model for Support Ticket Management System which means that the client can login to do a support tick and shared the application using the same URL.
Couple options though:
1) We design by table such has a SUPPORTS table with has a foreign key of the ClientID from CLIENTS table. This will cover that client can only can a specific data within the SUPPORTS table.
2) Alternatively, create a dynamic table which is created specifically for "Client B" for instance for SUPPORT table and it will be called such as ClientBSupport table. Something that can be common it will be shared such as like look-up table.
Couple consideration though:
1) Considered client wanting to backup this table. Obviosly it has too querying based on the client id to get the right data.
2) Security withing that table. Is the data needs to be incrypted ... I guess this is expensive excercise.
3) Possibly have a model that access to this table in querying directly etc etc.
4) If the Support table is getting larger and larger and it's not fair for small client accessing the small information which has equal to the one that has big records? Cause it's sharing table.
Any inputs I am really appreciated.
Thanks
How to move master , model and msdb database to some other location. Can we overwrite the master, model and msdb files
Hi there,
I cannot seem to find a way to export a database model from Visio to Sql Server.
Can somebody provide a walkthrough how to export it?
Thanks a lot
Have a database that's in "Simple" recovery mode whose .ldf has grown to 270GB. This database is a data warehouse so "full" is not required. I put it in simple mode a month ago and shrunk the log down and now it's filled up the disk.
What steps can I take to mitigate this in future? I've read that this is caused by long running transactions which fill the log for DR purposes. Should I put the database back into full mode and backup/truncate daily.
The auto-growth is set to 128MB which is very low.
I have to ask first, if in the command "Update model from database" the SQL Server Data Tools are involved?
View 2 Replies View RelatedBeen practicing DR strategies with a test SQL instance by following the scenarios listed here: [URL] ....
> Took a backup of the Model database
> Stopped SQL Server
> Deleted model database data & log file
> Started SQL Server and it obviously wouldn't start because TempDB needs a model database present.
> Started SQL instance with trace flags 3608 & 3609
> Connected to SQL instance using command prompt.
> Issued restore command but was met with this error:
Shared Memory Provider: The pipe has been ended.
Communication link failure
And found this in the SQL log..
2015-08-12 16:21:32.83 spid51 Starting up database 'tempdb'.
2015-08-12 16:21:36.88 spid51 Error: 3456, Severity: 21, State: 1.
2015-08-12 16:21:36.88 spid51 Could not redo log record (59:136:21), for transaction ID (0:0), on page (1:20), allocation unit 458752, database 'tempdb' (database ID 2). Page: LSN = (30:165:3), allocation unit = 458752, type = 1.
[Code] .....
Hi,Facts:I created a database to support an application that tracks events ondifferent objects. The two main tables are tbl_Object andtbl_EventLog. Each table has unique ID and on the tbl_EventLog thereis FK for a record in the tbl_Object. The events are inserted all thetime for the same or different objects from the tbl_Object. There areabout 600,000 objects in the tbl_Object and 1,500,000 (and growing)events in tbl_EventLog.Question:The user often wants to know what the last event was for a specificobject.What is the best way of retrieving the last event?Should I simply do a max(eventdatetime) on a specific object? orShould I add a LastEventID column to tbl_Object and update it everytime a new event is inserted? or any other way to implement it?I chose the second method because I didn't think it made sense searchthe event table everytime the user wants to know the last event, but Iwanted to know what the experts thought.Please let me know what you think.Thank you,Oran Levin
View 5 Replies View RelatedIn MS Visual Studio, when creating a new Report Model Project, after defining a datasource to an Oracle database (and successfully testing it), and a simple datasource view (1 table), when I click "Run" in Report Model Wizard, I receive the following error:
"ORA-02179: valid options: ISOLATION LEVEL { SERIALIZABLE | READ COMMITTED }"
It does not appear that one can create Report Models from an Oracle database (since the SQL being used to query the Oracle database cannot be edited and contains syntax errors?). Is this the case?
Thank you.