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?

Decrypt sproc returning NULL to non DBO.

I'm still having issues with this despite my attempts to resolve. I even
have "with exec as dbo" in my sproc, and and "exec as dbo" in my execution,
but still the encrypted data returns nulls when I exec as a user other than
DBO. Below is precisely what I have done. All ideas are welcomed.

TIA, ChrisR

--If there is no master key, create one now

IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD =
'23987hxJKL95QYV4369#ghf0%94467GRdkjuw54ie5y01478d Dkjdahflkujaslekjg5k3fd117
r$$#1946kcj$n44ncjhdlj'
GO

CREATE CERTIFICATE HumanResources037
WITH SUBJECT = 'Employee Social Security Numbers';
GO

CREATE SYMMETRIC KEY SSN_Key_01
WITH ALGORITHM = DES
ENCRYPTION BY CERTIFICATE HumanResources037;
GO

USE [AdventureWorks];
GO

-- Create a column in which to store the encrypted data
ALTER TABLE HumanResources.Employee
ADD EncryptedNationalIDNumber varbinary(128);
GO

-- Open the symmetric key with which to encrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;

-- Encrypt the value in column NationalIDNumber with symmetric
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
NationalIDNumber);
GO

-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
GO

-- Now list the original ID, the encrypted ID, and the
-- decrypted ciphertext. If the decryption worked, the original
-- and the decrypted ID will match.

alter procedure getDecryptedIDNumber
with exec as owner
as
SELECT NationalIDNumber, EncryptedNationalIDNumber
AS "Encrypted ID Number",
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
AS "Decrypted ID Number"
FROM HumanResources.Employee;
GO

/*works for me, shows the decrypted data*/

exec getDecryptedIDNumber

USE [master]
GO

CREATE LOGIN [test] WITH PASSWORD=N'test',
DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE [AdventureWorks]
GO

CREATE USER [test] FOR LOGIN [test]
GO

use [AdventureWorks]
GO

GRANT EXECUTE ON [dbo].[getDecryptedIDNumber] TO [test]
GO

GRANT IMPERSONATE ON USER:: dbo TO test;
GO

/*Now, open up a "file/new/DB Engine Query" and login with the test login*/
exec as user = 'dbo'
exec getDecryptedIDNumber

/*This returns NULL values where it should show the decrypted data*/I have made some changes to the scripts, but the outcome is the same. Everything needed is below, so all ideas are welcomed. Also, please make sure to use these scripts, not the last ones.

USE [AdventureWorks];
GO

IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD =
'vato'
GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'vato'

CREATE CERTIFICATE HumanResources037
WITH SUBJECT = 'Employee Social Security Numbers';
GO

CREATE SYMMETRIC KEY SSN_Key_01
WITH ALGORITHM = DES
ENCRYPTION BY CERTIFICATE HumanResources037;
GO

-- Create a column in which to store the encrypted data
ALTER TABLE HumanResources.Employee
ADD EncryptedNationalIDNumber varbinary(128);
GO

-- Open the symmetric key with which to encrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;

-- Encrypt the value in column NationalIDNumber with symmetric
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
NationalIDNumber);
GO

-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
GO

-- Now list the original ID, the encrypted ID, and the
-- decrypted ciphertext. If the decryption worked, the original
-- and the decrypted ID will match.

create procedure getDecryptedIDNumber
with exec as owner
as
SELECT NationalIDNumber, EncryptedNationalIDNumber
AS "Encrypted ID Number",
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
AS "Decrypted ID Number"
FROM HumanResources.Employee;
GO

/*works for me, shows the decrypted data*/

exec getDecryptedIDNumber

USE [master]
GO

CREATE LOGIN [test] WITH PASSWORD=N'test',
DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE [AdventureWorks]
GO

CREATE USER [test] FOR LOGIN [test]
GO

use [AdventureWorks]
GO

GRANT EXECUTE ON [dbo].[getDecryptedIDNumber] TO [test]
GO

GRANT IMPERSONATE ON USER:: dbo TO test;
GO

/*Now, open up a "file/new/DB Engine Query" and login with the test login*/
exec as user = 'dbo'
exec getDecryptedIDNumber

/*This returns NULL values where it should show the decrypted data*/

Declare cursor for execute stored_procedure

Hello,

I am using SQL 2005 and i would like to create a cursor from executing a stored procedure (dbo.SP_getDate @.Variable).
Something like this:

DECLARE Cursor1 CURSOR FOR EXECUTE dbo.SP_getDate @.Variable

i get an error saying "incorrect syntax near the keyword 'EXECUTE'."
cannot get rid of the error. what am i doing wrong?
(i am trying to avoid using #tempTbl to store the results of the execute first and then doing a select on the #tempTbl)

Not sure if i am doing this right all together.
any help would be greatly appreciate.See if this helps. It is not good practice to concatenate user entries into a SQL string, so be careful not to risk SQL Injection.

create proc SP_getDate (

@.v int

) as

SET NOCOUNT ON

SELECT TOP (@.v) HireDate

FROM AdventureWorks.HumanResources.Employee

ORDER BY EmployeeID

go

DECLARE @.sql NVARCHAR(1000)

DECLARE @.Variable int

SET @.Variable = 13

SET @.sql = '

DECLARE Cursor1 CURSOR FOR

SELECT HireDate

FROM OPENQUERY([SK8400\YUK], ''exec AdventureWorks.dbo.SP_getDate @.v'')'

SET @.sql = REPLACE(@.sql,'@.v',@.Variable)

exec (@.sql)

go

declare @.d datetime

open Cursor1

toploop:

fetch from Cursor1 into @.d

while @.@.fetch_status = 0 begin

print @.d

goto toploop

end

close Cursor1

deallocate Cursor1

go

drop proc SP_getDate

-- Steve Kass

-- Drew University

-- http://www.stevekass.com

Yassi@.discussions.microsoft.com wrote:

> Hello,

>

> I am using SQL 2005 and i would like to create a cursor from executing a

> stored procedure (dbo.SP_getDate @.Variable).

> Something like this:

>

> DECLARE Cursor1 CURSOR FOR EXECUTE dbo.SP_getDate @.Variable

>

> i get an error saying "incorrect syntax near the keyword 'EXECUTE'."

> cannot get rid of the error. what am i doing wrong?

> (i am trying to avoid using #tempTbl to store the results of the execute

> first and then doing a select on the #tempTbl)

>

> Not sure if i am doing this right all together.

> any help would be greatly appreciate.

>

>

Decimal Help

I am having trouble with the following view in SQL:
SELECT CAST(AVG(L) AS Decimal(9, 1)) AS LAvg
FROM dbo.HOSurvey_holding
WHERE (L <> 0)
I want one decimal holder but when i have a whole number it doesnt put
a .0 after it? is this possible if so how would i do it? The data i
have in the database averages out to 4 and i would like for it to be
4.0
Any ideas on how i can fix the view so it adds the .0 after whole
numbers.That's a presentation layer issue basically. If you return numbers to your
application, your application should be able to present them in the
appropriate format to the user. VB, Excel, ASP etc all have the appropriate
controls for formatting.
But if you really need to do it in SQL Server, for example because you
create an export to a text file, you can use the STR function:
SELECT STR(AVG(L),10,1)AS LAvg
FROM dbo.HOSurvey_holding
WHERE (L <> 0)
Jacco Schalkwijk
SQL Server MVP
<pkruti@.hotmail.com> wrote in message
news:1113488579.279634.184360@.o13g2000cwo.googlegroups.com...
>I am having trouble with the following view in SQL:
> SELECT CAST(AVG(L) AS Decimal(9, 1)) AS LAvg
> FROM dbo.HOSurvey_holding
> WHERE (L <> 0)
> I want one decimal holder but when i have a whole number it doesnt put
> a .0 after it? is this possible if so how would i do it? The data i
> have in the database averages out to 4 and i would like for it to be
> 4.0
> Any ideas on how i can fix the view so it adds the .0 after whole
> numbers.
>