default dbo to object names instead of user name

Several developers have been assigned dbo of a database. Is there a way to
allow all the objects that we create to all have the dbo ownership by
default, rather than our user name?
I'm aware that we could explicitly type ...
create proc dbo.myprocedurename as
but was hoping this could be the default behavior.
Thanks in advance.
Mark
Mark,
I assume you mean they have been assigned the db_owner role? Only one login can be the dbo user. There is a
difference between dbo and db_owner.
Assuming that the logins have their own user names and are indeed db_owner, then you have to qualify the owner
when you create the object. Only way around is to use sp_addalias, but this will disappear in future and
there's no GUI support for this. Be careful to read in BOL whether it is supported or not.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mark" <mfield@.idonotlikespam.cce.umn.edu> wrote in message news:%23GgYl4dMEHA.1392@.TK2MSFTNGP09.phx.gbl...
> Several developers have been assigned dbo of a database. Is there a way to
> allow all the objects that we create to all have the dbo ownership by
> default, rather than our user name?
> I'm aware that we could explicitly type ...
> create proc dbo.myprocedurename as
> but was hoping this could be the default behavior.
> Thanks in advance.
> Mark
>
|||Mark wrote:

> Several developers have been assigned dbo of a database. Is there a way to
> allow all the objects that we create to all have the dbo ownership by
> default, rather than our user name?
could you put all those developers into a group on the db server and then have
that group be the dbowner of the db?

default dbo to object names instead of user name

Several developers have been assigned dbo of a database. Is there a way to
allow all the objects that we create to all have the dbo ownership by
default, rather than our user name?
I'm aware that we could explicitly type ...
create proc dbo.myprocedurename as
but was hoping this could be the default behavior.
Thanks in advance.
MarkMark,
I assume you mean they have been assigned the db_owner role? Only one login
can be the dbo user. There is a
difference between dbo and db_owner.
Assuming that the logins have their own user names and are indeed db_owner,
then you have to qualify the owner
when you create the object. Only way around is to use sp_addalias, but this
will disappear in future and
there's no GUI support for this. Be careful to read in BOL whether it is sup
ported or not.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mark" <mfield@.idonotlikespam.cce.umn.edu> wrote in message news:%23GgYl4dMEHA.1392@.TK2MSFTN
GP09.phx.gbl...
> Several developers have been assigned dbo of a database. Is there a way t
o
> allow all the objects that we create to all have the dbo ownership by
> default, rather than our user name?
> I'm aware that we could explicitly type ...
> create proc dbo.myprocedurename as
> but was hoping this could be the default behavior.
> Thanks in advance.
> Mark
>|||Mark wrote:

> Several developers have been assigned dbo of a database. Is there a way t
o
> allow all the objects that we create to all have the dbo ownership by
> default, rather than our user name?
could you put all those developers into a group on the db server and then ha
ve
that group be the dbowner of the db?sql

default dbo to object names instead of user name

Several developers have been assigned dbo of a database. Is there a way to
allow all the objects that we create to all have the dbo ownership by
default, rather than our user name?
I'm aware that we could explicitly type ...
create proc dbo.myprocedurename as
but was hoping this could be the default behavior.
Thanks in advance.
MarkMark,
I assume you mean they have been assigned the db_owner role? Only one login can be the dbo user. There is a
difference between dbo and db_owner.
Assuming that the logins have their own user names and are indeed db_owner, then you have to qualify the owner
when you create the object. Only way around is to use sp_addalias, but this will disappear in future and
there's no GUI support for this. Be careful to read in BOL whether it is supported or not.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mark" <mfield@.idonotlikespam.cce.umn.edu> wrote in message news:%23GgYl4dMEHA.1392@.TK2MSFTNGP09.phx.gbl...
> Several developers have been assigned dbo of a database. Is there a way to
> allow all the objects that we create to all have the dbo ownership by
> default, rather than our user name?
> I'm aware that we could explicitly type ...
> create proc dbo.myprocedurename as
> but was hoping this could be the default behavior.
> Thanks in advance.
> Mark
>|||Mark wrote:
> Several developers have been assigned dbo of a database. Is there a way to
> allow all the objects that we create to all have the dbo ownership by
> default, rather than our user name?
could you put all those developers into a group on the db server and then have
that group be the dbowner of the db?

default DB names for SSRS?

I was wondering if one SQL Server could host more then one set of Reporting Services databases? This would then lead to the question of, can the default names be change to what ever? Say the Accounting and R/D groups wants to use the same SSRS so, we name their databases ACCT_ReportServer and RD_ReportServer, etc..? Can I do this? I believe I can but need to be sure.

thanks much,
joe

can i ask , why you would want to do this? Report Server allows you to set up groups of people who have access to certain reports...

sound to me like it would be a waste of resources to have multiple ssrs databases... besides I honestly don't think it would work.

Report server security feature is very good, that is the only reason I can think of why you might want to seperate them.

|||Thanks for you quick reply. I really do need to do more reading/research. As long as I can keep groups separated, that will work.

Again - Thanks.
|||Hey JoeyDba,
How's it going today? Did you find any more answers?

De-duping data

I have a list of companies that all have the same Company Id but have formatting issues with the names. For example I have the following:

CompanyId Name
12345 Microsoft
12345 Microsoft Inc.
12345 Microsoft, Inc.

I figured I would use a simple data flow task to insert the company id and name into a company table where the primary key is the company id (I would then ignore the errors of duplicate keys). I was expecting to see the first instance of the company inserted into the company table and the dupes ignored. That's not the case - I get an error on duplicate primary keys. Can I not do this?

jrp210 wrote:

I have a list of companies that all have the same Company Id but have formatting issues with the names. For example I have the following:

CompanyId Name
12345 Microsoft
12345 Microsoft Inc.
12345 Microsoft, Inc.

I figured I would use a simple data flow task to insert the company id and name into a company table where the primary key is the company id (I would then ignore the errors of duplicate keys). I was expecting to see the first instance of the company inserted into the company table and the dupes ignored. That's not the case - I get an error on duplicate primary keys. Can I not do this?

This is more of a transact-sql question. The observed behavior is correct. In the sense of SSIS, you can use a sort transformation to eliminate dups. Or, you can structure your source query to do more of a select companyid, max(name) from table group by companyid.|||

You are right. But, how would a sort transorm work to eliminate dupes?

|||

jrp210 wrote:

You are right. But, how would a sort transorm work to eliminate dupes?

There is an option in the sort transformation (a check box) to eliminate dups. You'd have to sort only on the companyID though.

sql

declaring cursors

I need to declare and initialise a cursor using variable field names and tables. However, an error occurs when I use the fetch next into

Server: Msg 16924, Level 16, State 1, Line 98
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.

If I use the second (commented out) statement which hard codes the fields the whole script works fine.

Can anyone point out where I'm going wrong. The cursor appears to be built but I cannot use it.

Thanks
Paul

Declare Keys_cursor CURSOR FOR Select + @.LastNameField + ', ' +
@.GenderField + ', ' + @.PostCodeField + ', ' + @.FirstNameField + ', ' +
@.TitleField + ', ' + @.Add1Field + ', ' + @.Add2Field + ', ' + @.Add3Field + ' from ' + @.TableName + ' ' + @.WhereSQL

--Declare Keys_cursor CURSOR FOR Select surname,gender,post_code,forename,title,add1,add2, add3 from test_credit_data

OPEN Keys_cursor

FETCH NEXT FROM Keys_cursor
INTO @.LastName,@.Gender, @.PostCode, @.FirstName, @.Title, @.Add1, @.Add2, @.Add3I don't see any error by this help u, you can try to probe ur parametirized select whit statement EXECUTE like this:

EXECUTE Select + @.LastNameField + ', ' + @.GenderField + ', ' + @.PostCodeField + ', ' + @.FirstNameField + ', ' + @.TitleField + ', ' + @.Add1Field + ', ' + @.Add2Field + ', ' + @.Add3Field + ' from ' + @.TableName + ' ' + @.WhereSQL

so you'll can know if your statement is correct.

bye,
Maritzita

Originally posted by plineham
I need to declare and initialise a cursor using variable field names and tables. However, an error occurs when I use the fetch next into

Server: Msg 16924, Level 16, State 1, Line 98
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.

If I use the second (commented out) statement which hard codes the fields the whole script works fine.

Can anyone point out where I'm going wrong. The cursor appears to be built but I cannot use it.

Thanks
Paul

Declare Keys_cursor CURSOR FOR Select + @.LastNameField + ', ' +
@.GenderField + ', ' + @.PostCodeField + ', ' + @.FirstNameField + ', ' +
@.TitleField + ', ' + @.Add1Field + ', ' + @.Add2Field + ', ' + @.Add3Field + ' from ' + @.TableName + ' ' + @.WhereSQL

--Declare Keys_cursor CURSOR FOR Select surname,gender,post_code,forename,title,add1,add2, add3 from test_credit_data

OPEN Keys_cursor

FETCH NEXT FROM Keys_cursor
INTO @.LastName,@.Gender, @.PostCode, @.FirstName, @.Title, @.Add1, @.Add2, @.Add3|||To do this I need to change the sting to put quotes around the 'Select ' of the statement If I do this the SQL works fine

Thanks for your help|||Great...cursors are bad enough, noe we have dynamicx sql cursors...

what are you trying to do?

I hope it's an admin function...

(as opposed to an applcation function)

I mean you still have to code the fetch, and you still have to KNOW what you're working with, and you'll still have to reference the variables in the sproc...

so...

why bother?|||We have about 30 different databases which all have keys built on them in the same way. When component data is changed the keys need rebuilding.

If we use ADO and VB or C to move through a recordset to update these keys time can become a serious problem. Building the keys within SQL would be even more complicated. I decided to then write an ActiveX dll which was referenced from an SQL function. This was paramatized and would return the key I wanted to update into the database.

However, and I apologize for the life story but I have been unable to think of a quicker way to complete this process, building six keys and updating was obviuosly extremelly inefficient. Therefore I wanted to build all six from the ActiveX Dll, update the record for all keys in the same procedure. This again worked fine for one table but a generic script for all the tables was the obvious next step, hence the need for dynamic table and field names.

I have attached the script as is if anyone wants to have a look. Laugh at how badly I've done but still give me a better solution

Otherwise Brett Kaiser I'll see you behind the bike sheds after school if you've still got a problem with me|||You dude..no problem...just trying to help you..

What do you mean by building keys?

And this..

Building the keys within SQL would be even more complicated.

Doesn't make sense to me...

mostly because I don't understand what building keys means...

In any event...I hope it's working for you...

AND it's FRIDAY...yeeeeHaaaaaaa

Now if I can find my lost shaker of salt, I'd be in business..|||It was friday but it is now monday. Aaaaaaa not good at all.

The key I mean is essentially a string containing key components of an address

for instance on every record we have key which contains the first seven alpha characters of the surname and the postcode. This enables us to match records within databases based on this key.

To build these keys we have simple VB code - which is as you'd expect a series of mid, case and character matching to build the key. Not all the keys are this simple.

I cannot even imagine how I could do this in SQL. It is unfortunately not a simple case of using substring.

The solution at the moment is to open a recordset and move through the data updating the keys but this is obviously slow when updating 100000 records in a database containing 3 million.

Thanks for your help anyway|||What are the rules to build a "key" and how do you update it?

Sounds like a job for a user defined function.

If you can do it in VB, you can do it in T-SQL (Well except for Arrays, but youcan fake that out too)

Can you post some VB code?|||Find attached the classes we use to build some of the keys we use for address manipulation.

If you know a way to build all these keys into separate fields through SQL I will be very grateful