Creating / Changing Owner Problem

Jul 20, 2005

I'm trying to create a new database and new login to allow a client
(through my asp.net page) to create their own database, username and
password. I've tried using the stored procedures I've found in BOL,
but I can't get it to work right. It keeps saying that I can't assign
this username as the db owner since it's already a user for the
database. I then read other posts about reassigning the db owner to
another dummy account and then trying to reassign it to the new one,
but that isn't working either. Can anyone look at my code and tell me
what I'm doing wrong? Thanks.

//create db login
qry = "sp_addlogin '" + username + "', '" + password + "', '" + dbname
+ "'";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();

//grant login access to new database to new owner
tmpCon = new SqlConnection("Data Source=ourserver;User
ID=uid;Password=pwd;Initial Catalog=" + dbname + ";Network
Library=DBMSSOCN;");
tmpcmd = new SqlCommand("sp_grantdbaccess '" + username + "'",tmpCon);
tmpCon.Open();
tmpcmd.ExecuteNonQuery();
tmpcmd.Dispose(); tmpcmd = null;
tmpCon.Close(); tmpCon.Dispose(); tmpCon = null;

//connect to new database under sa account and change owner to new
account
tmpCon = new SqlConnection("Data Source=ourserver;User
ID=uid;Password=pwd;Initial Catalog=" + dbname + ";Network
Library=DBMSSOCN;");
tmpcmd = new SqlCommand("sp_changedbowner
'HolderUserDoNotDelete'",tmpCon);
tmpCon.Open();
tmpcmd.ExecuteNonQuery();
tmpcmd.CommandText = "sp_changedbowner '" + username + "'";
tmpcmd.ExecuteNonQuery();
tmpcmd.Dispose(); tmpcmd = null;
tmpCon.Close(); tmpCon.Dispose(); tmpCon = null;

View 1 Replies


ADVERTISEMENT

Changing A Dts Package Owner

Nov 28, 2001

Hello,

Does anyone know how to change the owner of a DTS package? Currently it is set to someone's domain login and I want to change it to the system administrator account.

The reason for the change is because I think this is why the package will not run when scheduled on the server, but will run locally for the user.

Thanks,
Brent.

View 1 Replies View Related

Changing Owner On A SQLServer DB ?

Sep 3, 2004

We have received a DB from one of our customer.
We want to change the DB and tables (and all objects) owner to sa
How can we do that ?

Thanks

View 4 Replies View Related

Changing Table Owner

Jul 20, 2005

Hi- apologies for asking a stupid newbie question, but I'm really stuck atthe moment. I need to change table ownership.I've got an asp script which is looking for a table owned by the dbo role,however the table was created under a different ownership. I understand theproblem, and almost understand the solution, but I can't seem to get all theway.THE PROBLEM (using [server].[database].[owner].[table])[mgbsvr1].[dnn].[dnnadmin].[aspsearch]needs to be[mgbsvr1].[dnn].[dbo].[aspsearch]I looked up the books online and found this syntax:sp_changeobjectowner [ @objname = ] 'object' , [ @newowner = ] 'owner'But I can't see how to use it, nor more importantly, where I should use it.It won't work in the query tool in Enterprise Manager. If I need to create ascript (which I've never done before), how do I execute the script?All help deeply appreciatedManning, Sydney

View 3 Replies View Related

Changing Object Owner Syntax

Jul 20, 2005

Hi there,We have a user name convention here that specifies users in thefollowing way:jane.doejohn.smithIn order to change a database object I must specify the owner name alongwith the object name. Does anyone know the proper syntax for doing thiswith a user name that contains a period? When I put the user namefollowed by the object name in quotes such as 'jane.doe.tb_test_table',SQL Server can not find the object.Any help is appreciated.Kelly

View 1 Replies View Related

Changing Owner Of User Datatype Objects

Jan 29, 2002

Usually all the user datatypes in our databases have the owner dbo.
One has a few that are owned by a user with dbo rights. I am trying to change them to dbo owner.
Sp_changeobjectowner gives 'object does not exist'

Any ideas

Jim

View 1 Replies View Related

Changing Database Owner To Access Diagrams

Jan 23, 2007

hello,

i recently changed the machine name of my development computer and am now no longer able to create or view any diagrams for the sql database that was created by the old machine name user. i receive an error where I cannot make myself "the dbo of this database." i can see the old name in the "owner" properties field of the mdf database, but the box is grayed out and i am unable to change it to the new machine/user name. is there a way to change the owner of the database to my new machine/user name? the new name has admin rights and the computer is a standalone workstation not connected to a network.

i am using sql server 2005 express edition with visual web developer.

thanks!

View 7 Replies View Related

Changing The Owner Of A User-defined Data Type

Jul 20, 2005

Hi GuysWonder if you could help me.Basically I produce an accounts package that uses a SQL 2000 DB as theRDBMS. I always instruct users to login as 'sa' and the relevantpassword when doing an update to my program, as sometimes I need to dodatabase changes for new stuff.Found that one of my users has not only logged in with their loginname (in this case Edward), but have also made this login a 'db owner'so that when I created 2 new user-defined data types they belong toEdward rather than dbo.This must have happened a long time ago, but now that they want tomove Edward round the roles and/or delete him from a copy of thedatabase that they have, they can't because he's the owner of theseuser-defined types.This brings me to the reason for my post, how can I change the ownerfrom Edward to dbo for these data types? I found an article ontechnet of how to do this, but when it suggests changing myuser-defined type to standard format it doesn't seem to work.Any ideas?RgdsRobbie

View 1 Replies View Related

SQL 2012 :: What Account To Use For Identity When Creating Credential And To Use As Job Owner

Aug 12, 2014

We are running SQL Server 2012 on Windows 2008 Server. I created a credential with a proxy account. In creating the credential, it asked for an Indentity and Secret. I used my windows login and password. Now, I have tested the credential and proxy account by executing a Job which calls a SSIS Package. What is the 'best practice' to use when creating a credential? Should the credential be created with another windows login, created with the same abilities as my windows login, with a non-expiring password? Should that new windows login be used as the owner of my job with the Agent?

View 8 Replies View Related

Creating Persmission On User To Become Full Owner Of Database......!

Feb 15, 2008

Looks like Using Schema in SQL Server 2005 gives enough fucntionality to control persimission on pre-created objects and database. here is my scenario....

I want to have Users that can only create new databases and then have FULL control on that database that they create, like creating table, inserrting, deleting, updating, etc etc.

what will be best steps to have this scene.

(I have created a user and made it a memeber of DBCREATOR, but that user is unable to create any table inside database that is created by that user),

Any help is highly appreciated. I am using SQL 2005

Thanks

View 1 Replies View Related

Changing DB Owner From Kate To Bob, But Kate Owns Objects

Apr 13, 2006

Im having trouble changing the DB owner from Kate to Bob, because Kate owns some objects in the DB.
I first try to run sp_changedbowner Bob....but it tells me:
The proposed new database owner is already a user in the database.

When I run scripts on a table that such as;
sp_changeobjectowner 'customers', 'Bob'...I get the message of

Server: Msg 15001, Level 16, State 1, Procedure sp_changeobjectowner, Line 38
Object 'customers' does not exist or is not a valid object for this operation.

Please help with what I can do.

Thank you

View 3 Replies View Related

Changing 'Filename' Attri. When Creating Databases

Jul 13, 2006

Obadiah writes "hello,

I am a newbie to developing .net applications unfortunately my boss and my colleaques think i am a pro (interview went too well) and i would like to get at least this months paycheck before am fired....so i here's my dumb question:

I want to create a SQL database from my vb.net application using stored procedures, however i want to be able to set the Filename attribute ('Filename = C:Program FilesMicrosoft SQL ServerMSSQL$SARDONYXINSTANCEData estdb.mdf') by means of a input parameter to the stored procedure. like so...
Filename = @databasepath.

HERE is what i have:
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE rico_dbasescript
@Databasepath varchar(100) = 'c:program filesmicrosoft sql servermssql$sardonyxinstancedataSardonyxrioctestdb.mdf' , @Databaselogpath varchar(100)= 'c:program filesmicrosoft sql servermssql$sardonyxinstancedataSardonyxrioctestdblog.ldf'
AS

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'Sardonyxrioctestdb')
DROP DATABASE [Sardonyxrioctestdb]



CREATE DATABASE Sardonyxrioctestdb
ON
( NAME = 'Sardonyxrioctestdb_dat',
FILENAME = @Databasepath,
--FILENAME ='c:program filesmicrosoft sql servermssql$sardonyxinstancedataSardonyxrioctestdb.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Sardonyxrioctestdb_log',
FILENAME = @Databaselogpath,
--FILENAME ='c:program filesmicrosoft sql servermssql$sardonyxinstancedataSardonyxrioctestdblog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO


The above procedure works fine when Filename = 'c:program filesmicrosoft sql servermssql$sardonyxinstancedataSardonyxrioctestdb.mdf'
but bombs out when Filename = @Databaselogpath.

Thank you in advance."

View 2 Replies View Related

Creating Database User And Set User To Owner Of Database

Nov 30, 2007



Hi there,

I want to create database login and set that login to owner of the database? Can anyone help me?

thanks

View 3 Replies View Related

Getting Owner Name

Mar 20, 2003

Hi,
I have a little problem regarding getting owner name of object. In my application, user is creating a table with no owner name. He is logged with some login other than 'sa'.

The table is created with db's default owner name (if no server roles specified for that login) otherwise it uses the login's owner name mentioned in db properties.

Please guide me if i want the correct owner name under which the table is created, shall i use CURRENT_USER for that?
Regards,

View 3 Replies View Related

DB Owner

Aug 21, 2005

How to find out the login , that is the owner of the database .

I mean if i havea database say DB1
I want to find out , which login user is the owner of this DB

Thanks

View 1 Replies View Related

Db Owner

Jul 23, 2005

Hi,I currently have two databases (DEV & TEST) with the same users butdifferent owners. The TEST database is on a remote machine with 2database users - USR1 & DBO. The DEV database is exactly the same.However, when I create procedures etc the DEV database recognises theowner as DBO and the TEST database USR1.I have made USR1 an owner on DEV and I login using the correctcredentials. However it still is not recognising this when creatingprocedures.This is very frustrating when doing a SQL Compare as it thinks thestored procs are different!I have done a sp_changedbowner and this didn't work. There must beanother config value that need changing?Any ideas?Thanks.

View 2 Replies View Related

Dbo Owner

Jul 30, 2007

We are using SQL Express and I have a problem with dbo.
On my machine if I logon to SQL Express using windows authentication
and create a new database I automatically have db_owner role membership
on the new database.
On a colleagues machine, if he logs onto his SQL Express using windows authentication
and creates a new database he does NOT have db_owner role membership
on the new database.

How come?
I have checked pretty much everythng - windows built in admins, SQL express sysadmin roles
service pack versions but I can not find any difference in setup.
What should I do to his machine/SQL Express setup so he automatically has db_owner role membership
for every newly created database?

Thanks
Charlie

View 4 Replies View Related

The Owner Of The Table

Jul 27, 2006

Hello,
I need to user full name for the table as seen below.
 
SELECT @RowCount = COUNT(*)
FROM  T1 c  INNER JOIN [MyInstance].MyDB.dbo.T2 b ON c.T1_ID=b.T2_ID
 
T2 may be re-created by different users, how can I get this working for all users if the creator is not dbo?

View 5 Replies View Related

Owner Of Db---------urgent

Oct 17, 2005

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! 

View 3 Replies View Related

Change Owner To Dbo

Aug 6, 2002

hi,
I just imported my access database into sql server 2000 and apparently during this importation the owner was set to my name. This gives my a problem retrieving the data in asp.net though. I found out that the problem (System.Data.SqlClient.SqlException: Invalid object name 'tablename') is situated at this ownership by stripping the query to a bare minimum and trying it on a new table created in sql server with dbo as the owner.
My problem now is that I can't seem to find the way to change the owner to dbo from 'peterj'. Can you help me on this one ??

View 1 Replies View Related

DTS Job Owner - Urgent

Aug 17, 2001

Hi,

Is there any way to change the owner of DTS package after it has created?

Thanks..

View 5 Replies View Related

How Can I Get Different Between DBO Owner And Another User?

Oct 5, 2000

How can I get different between DBO owner and another user when I select SP name from SYSOBJECTS? How to compare a SP's create by DBO and another user?
Example for, I want to list all SPs of DBO owner to excute my statement (as sp_changeobjowner...), but when I select all SPs in SYSOBJECTS to cursor, if there is any SPname's created by another user (no DBO), I'm getting error.
Thanks in advance,
J.K

View 1 Replies View Related

Change Owner

Nov 4, 1999

How Can I change table's owner

View 2 Replies View Related

Database Owner - Sa Or ?

Aug 6, 2002

I want 'sa' to own all the user databases. Currently the database properties screen shows the owner as myself, 'al', but the list from the Users tab in EM for the database doesn't list me, but lists the dbo as login name 'sa'. DB_changedbowner 'sa' says:

Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 46
The proposed new database owner is already a user in the database.

BUT, who really owns the database; if it's 'sa' then why am I showing up on the properties page as the owner???

Thanks,

AL

View 2 Replies View Related

SQL Agent Job Owner

Nov 7, 2001

Hi, can a SQL Server Agent job owner be a userid instead of sa? I tried to changed it to my own
userid with sysadmin role but the job wouldn't run unless it is own by sa.

View 1 Replies View Related

Change DB Owner

Mar 2, 2005

I have several Production Databases on my SQL Server 2000 Standard Edition Server (mixed mode authentication) that I'd like to change DBO Permissions on. When I look at the Properties of the DB(s) from EM, the Database owner is showing up as a Windows NT USer who is no longer even with the company!!

Can I use the sp_changedbowner 'sa' command to change the database owner to sa without disrupting Production? Might sound like a dumb question.. but ya never know!!!!! Also, I had someone on another forum tell me that 'sa' as db owner is a bad idea but I don't know why??????? can anyone elaborate on that?????

View 5 Replies View Related

How Do I Get Table Owner?

Mar 9, 2005

I need to the table owner.
Not from in Enterprise Manager, but from my app. (it's written in Delphi).

Any suggestions?

Thank you,
Keith

View 2 Replies View Related

Table Owner

Nov 24, 2005

Hi,

I've a database table named MYTABLE
It's owner is T01.

Now when I log on as MA I want to do a select like this:

select * from MYTABLE -- Gives me an error.
Select * from T01.MYTABLE -- goes fine..

I really don't want to specify the owner for every table. All tables have the owner T01.

Is it possible to use some statement before executing my sql statement.
Something like this :-=

Use owner T01
select * from MYTABLE.

Is it possible ?

//Martin

View 3 Replies View Related

Change Owner Of All Sps

Aug 6, 2005

Hello,

I need to change the owner of all stored procedures from x to y. I know there is a sp_changeobjectowner sp, which does that, but in that way I will have to write a loop to get all sps which are owned by user x. Can somebody help me with that?

View 3 Replies View Related

Table Owner

Aug 4, 2006

hi,how do i change the table's owner as dbo using sql server 2000 query.kalai

View 2 Replies View Related

DTS Package Owner

Jul 20, 2005

Hello there,Im trying to change the owership of the DTS package but am a littleconfused.sp_changeobjectowner changes the ownership of table, view, or storedprocedure in the current database...How could i change the DTS package ownership?Thank you in advance.Leo*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 2 Replies View Related

Change Owner

Jul 20, 2005

Is there a system SP to change object owner in the DB?

View 1 Replies View Related

Owner Of The Table

Jul 20, 2005

I've got this question:Is it possible to assign the 'current owner' using a query like thefollowing one:set current schema OWNER1 (in DB2 sql, this allow me to reference the tableswithout an explicit indication of the owner (es: after this query I cansimply write 'Select * from dummy', instead of 'Select * fromOWNER1.dummy')).Thank youFederica

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved