Ownership of Stored Procedures/Functions By Role Other Than dbo

I would like to enforce the following security policy. This policy is used in our other db systems (Rdb and Oracle), and I'm thinking about how to implement this in SQL Server. (Yes, I'm a DBA.)

(1) Developers are not allowed to create/alter/delete tables owned
by dbo. To prevent this, no developers will be granted role db_owner.
Developers should only be creating/modifying stored procedures/functions.

(2) All tables will be owned by dbo. DBA's (who have role db_owner
and server privileges) will be creating/modifying table definitions. DBA's will also be granting individual table priv's to the developers (most likely through the role "dco" below).

(3) All stored procedures/functions will be owned by a new role "dco"
(database code owner). All developers will be granted role "dco". No tables should be created in "dco", so role "dco" will be DENY-ed the privilege CREATE TABLE. (I'm also thinking about restricting view creation to dbo by DENY-ing CREATE VIEW.) DBA's will implicitly get access to dco procs/funcs from server privileges.

Does anybody see any possible problems with this approach? Have you tried anything like this? I've read about "broken ownership chains", but as long as the DBA grants the object privs on the tables to the developers I don't see a problem.

Thanks in advance for any input.

JeffWho will be the owner of what the DCO's will create?|||Role "dco" will own the stored procedures/functions, ie:

Create Procedure mydb.dco.myproc ...
Create Function mydb.dco.myfunc ...

Jeff

Ownership Chain Issue

Here is the situation:
I create a database - I am dbo. In this database I have 1 table and 1
updateable view. dbo shows as the owner of both the table and the view. I
create a new database user and do not add them to any database or server
roles. I grant the new user select, update, insert and delete permissions o
n
the view.
The user can view all data through the view, however, they cannot add or
update. When they attempt to add or update an error is generated indicating
that they do not have insert permissions on the table (for an add) or that
they do not have select and update persissions on the table (for an update).
My understanding is that since I have an unbroken ownership chain that SQL
Server should not even be checking the permissions on the table.
What am I missing?Some tools/APIs require that you create the view with the VIEW_METADATA
option. Otherwise, the base tables are accessed directly rather than via
the view. You can use a Profiler trace to see if this is the case.
Hope this helps.
Dan Guzman
SQL Server MVP
"Scott Shearer" <ScottShearer@.discussions.microsoft.com> wrote in message
news:B12E2447-6AAD-4722-B53C-EC84517FD80E@.microsoft.com...
> Here is the situation:
> I create a database - I am dbo. In this database I have 1 table and 1
> updateable view. dbo shows as the owner of both the table and the view.
> I
> create a new database user and do not add them to any database or server
> roles. I grant the new user select, update, insert and delete permissions
> on
> the view.
> The user can view all data through the view, however, they cannot add or
> update. When they attempt to add or update an error is generated
> indicating
> that they do not have insert permissions on the table (for an add) or that
> they do not have select and update persissions on the table (for an
> update).
> My understanding is that since I have an unbroken ownership chain that SQL
> Server should not even be checking the permissions on the table.
> What am I missing?|||I have noticed this behavior using Enterprise Manager and Microsoft Access
ADP. I also see this behavior with a stored proc in an MS Access ADP.
While I can execute a stored proc that contains an insert or update statemen
t
in the Access ADP, it won't work when using the stored proc as a bound objec
t
unless the end user has appropriate permissions on the underlying table.
Is this behavior as designed?
I'll try creating the view with the VIEW_METADATA option and report back.
Thanks
"Dan Guzman" wrote:

> Some tools/APIs require that you create the view with the VIEW_METADATA
> option. Otherwise, the base tables are accessed directly rather than via
> the view. You can use a Profiler trace to see if this is the case.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Scott Shearer" <ScottShearer@.discussions.microsoft.com> wrote in message
> news:B12E2447-6AAD-4722-B53C-EC84517FD80E@.microsoft.com...
>
>|||VIEW_METADATA is needed in views with Access ADPs so that Access doesn't
access the underlying tables directly. This is by design.
AFAIK, there shouldn't be a problem with stored procedures as long as there
is no dynamic SQL in the proc. Dynamic SQL always breaks the ownership
chain.
Hope this helps.
Dan Guzman
SQL Server MVP
"Scott Shearer" <ScottShearer@.discussions.microsoft.com> wrote in message
news:AD3BD957-D452-4CE5-96F0-B311A797872C@.microsoft.com...[vbcol=seagreen]
>I have noticed this behavior using Enterprise Manager and Microsoft Access
> ADP. I also see this behavior with a stored proc in an MS Access ADP.
> While I can execute a stored proc that contains an insert or update
> statement
> in the Access ADP, it won't work when using the stored proc as a bound
> object
> unless the end user has appropriate permissions on the underlying table.
> Is this behavior as designed?
> I'll try creating the view with the VIEW_METADATA option and report back.
> Thanks
> "Dan Guzman" wrote:
>|||Including the VIEW_META argument in the view definition worked. However,
SQL Server does appear to be checking permissions on the stored proc. I hav
e
a simple select statement in the stored proc that should be updateable when
accessed through an ADP. I get error messages indicating that the
permissions are being checked on the underlying table. However, when I
create a view using a select * against the table with the VIEW_METADATA
argument and reference that view in the SP rather than the underlying table,
it works OK.
By way of background, I am doing this in preparation for a project where row
level security will be required. I will filter data in a view and access th
e
views through stored procs. The users will have no direct access to any dat
a
other than through a view (which filters data appropriately) or an SP that
refernces one of the views.
"Dan Guzman" wrote:

> VIEW_METADATA is needed in views with Access ADPs so that Access doesn't
> access the underlying tables directly. This is by design.
> AFAIK, there shouldn't be a problem with stored procedures as long as ther
e
> is no dynamic SQL in the proc. Dynamic SQL always breaks the ownership
> chain.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Scott Shearer" <ScottShearer@.discussions.microsoft.com> wrote in message
> news:AD3BD957-D452-4CE5-96F0-B311A797872C@.microsoft.com...
>
>|||> I have a simple select statement in the stored proc that should
> be updateable when accessed through an ADP.
Your proc has only a SELECT statement and no INSERT/UPDATE/DELETE
statements. What is actually happening here is that Access is modifying the
base tables directly due to data binding. This necessitates that users have
permissions on the underlying table because the data modifications are not
done via a proc.
It seems views with VIEW_METADATA will allow you to use updatable bound
controls but this won't provide the security you want with stored procedure
resultsets. If you must use procedures for your SELECT, one option is to
create and execute INSERT/UPDATE/DELETE procs instead of relying on data
binding. Another approach is you use application roles from within you app
so that you can use data binding yet still prevent ad-hoc access outside
your application. See
<http://support.microsoft.com/defaul...kb;en-us;318816> for more
information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Scott Shearer" <ScottShearer@.discussions.microsoft.com> wrote in message
news:CCA6F68B-E42D-45EB-85AB-598827765FCC@.microsoft.com...[vbcol=seagreen]
> Including the VIEW_META argument in the view definition worked. However,
> SQL Server does appear to be checking permissions on the stored proc. I
> have
> a simple select statement in the stored proc that should be updateable
> when
> accessed through an ADP. I get error messages indicating that the
> permissions are being checked on the underlying table. However, when I
> create a view using a select * against the table with the VIEW_METADATA
> argument and reference that view in the SP rather than the underlying
> table,
> it works OK.
> By way of background, I am doing this in preparation for a project where
> row
> level security will be required. I will filter data in a view and access
> the
> views through stored procs. The users will have no direct access to any
> data
> other than through a view (which filters data appropriately) or an SP that
> refernces one of the views.
>
> "Dan Guzman" wrote:
>|||When the stored proc that I access through the ADP does a select from a view
created with the view_metadata argument, permissions appear only to be
checked on the stored proc and the view - not on the underlying table. I am
able to dpdates/inserts/deletes.
VIEW_METADATA was the answer - thanks for the help.
"Dan Guzman" wrote:

> Your proc has only a SELECT statement and no INSERT/UPDATE/DELETE
> statements. What is actually happening here is that Access is modifying t
he
> base tables directly due to data binding. This necessitates that users ha
ve
> permissions on the underlying table because the data modifications are not
> done via a proc.
> It seems views with VIEW_METADATA will allow you to use updatable bound
> controls but this won't provide the security you want with stored procedur
e
> resultsets. If you must use procedures for your SELECT, one option is to
> create and execute INSERT/UPDATE/DELETE procs instead of relying on data
> binding. Another approach is you use application roles from within you ap
p
> so that you can use data binding yet still prevent ad-hoc access outside
> your application. See
> <http://support.microsoft.com/defaul...kb;en-us;318816> for more
> information.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Scott Shearer" <ScottShearer@.discussions.microsoft.com> wrote in message
> news:CCA6F68B-E42D-45EB-85AB-598827765FCC@.microsoft.com...
>
>

Owner of tables

I have a stored procedure, that among other things drops and recreates two
new tables:
drop table dbo.t_de_tracking
drop table dbo.t_MR_TRACKING
SELECT * INTO dbo.T_DE_TRACKING
FROM OPENQUERY(ACS_SLR_DE, 'SELECT * FROM ACS_SLR_DATAENTRY..
T_DE_TRACKING (nolock)')
SELECT * INTO dbo.T_MR_TRACKING
FROM OPENQUERY(ACS_SLR_MR, 'SELECT * FROM ACS_SLR_MAILROOM..
T_MR_TRACKING (nolock)')
When these tables are created the owner is NOT dbo. How can I get the owner
of these tables to become dbo when created by this stored procedure?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200510/1Robert,
Do you really want to drop/create the tables or just TRUNCATE the table and
reload it? Probably not the best approach esp if there are a lot of records
and/or NC indexes. What are the fixed server and fixed database role
memberships of the user creating this proc? Also, you might want to build
some IF logic into testing if the tables exist prior to dropping them.
HTH
Jerry
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:55d48972e2388@.uwe...
>I have a stored procedure, that among other things drops and recreates two
> new tables:
> drop table dbo.t_de_tracking
> drop table dbo.t_MR_TRACKING
> SELECT * INTO dbo.T_DE_TRACKING
> FROM OPENQUERY(ACS_SLR_DE, 'SELECT * FROM ACS_SLR_DATAENTRY..
> T_DE_TRACKING (nolock)')
> SELECT * INTO dbo.T_MR_TRACKING
> FROM OPENQUERY(ACS_SLR_MR, 'SELECT * FROM ACS_SLR_MAILROOM..
> T_MR_TRACKING (nolock)')
> When these tables are created the owner is NOT dbo. How can I get the
> owner
> of these tables to become dbo when created by this stored procedure?
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200510/1|||Try naming the stored procedure dbo.[sproc name]
--
Thomas
"Robert R via droptable.com" wrote:

> I have a stored procedure, that among other things drops and recreates two
> new tables:
> drop table dbo.t_de_tracking
> drop table dbo.t_MR_TRACKING
> SELECT * INTO dbo.T_DE_TRACKING
> FROM OPENQUERY(ACS_SLR_DE, 'SELECT * FROM ACS_SLR_DATAENTRY..
> T_DE_TRACKING (nolock)')
> SELECT * INTO dbo.T_MR_TRACKING
> FROM OPENQUERY(ACS_SLR_MR, 'SELECT * FROM ACS_SLR_MAILROOM..
> T_MR_TRACKING (nolock)')
> When these tables are created the owner is NOT dbo. How can I get the owne
r
> of these tables to become dbo when created by this stored procedure?
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200510/1
>

Owner of tables

I have a stored procedure, that among other things drops and recreates two
new tables:
drop table dbo.t_de_tracking
drop table dbo.t_MR_TRACKING
SELECT * INTO dbo.T_DE_TRACKING
FROM OPENQUERY(ACS_SLR_DE, 'SELECT * FROM ACS_SLR_DATAENTRY..
T_DE_TRACKING (nolock)')
SELECT * INTO dbo.T_MR_TRACKING
FROM OPENQUERY(ACS_SLR_MR, 'SELECT * FROM ACS_SLR_MAILROOM..
T_MR_TRACKING (nolock)')
When these tables are created the owner is NOT dbo. How can I get the owner
of these tables to become dbo when created by this stored procedure?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200510/1Robert,
Do you really want to drop/create the tables or just TRUNCATE the table and
reload it? Probably not the best approach esp if there are a lot of records
and/or NC indexes. What are the fixed server and fixed database role
memberships of the user creating this proc? Also, you might want to build
some IF logic into testing if the tables exist prior to dropping them.
HTH
Jerry
"Robert R via SQLMonster.com" <u3288@.uwe> wrote in message
news:55d48972e2388@.uwe...
>I have a stored procedure, that among other things drops and recreates two
> new tables:
> drop table dbo.t_de_tracking
> drop table dbo.t_MR_TRACKING
> SELECT * INTO dbo.T_DE_TRACKING
> FROM OPENQUERY(ACS_SLR_DE, 'SELECT * FROM ACS_SLR_DATAENTRY..
> T_DE_TRACKING (nolock)')
> SELECT * INTO dbo.T_MR_TRACKING
> FROM OPENQUERY(ACS_SLR_MR, 'SELECT * FROM ACS_SLR_MAILROOM..
> T_MR_TRACKING (nolock)')
> When these tables are created the owner is NOT dbo. How can I get the
> owner
> of these tables to become dbo when created by this stored procedure?
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200510/1|||Try naming the stored procedure dbo.[sproc name]
--
Thomas
"Robert R via SQLMonster.com" wrote:
> I have a stored procedure, that among other things drops and recreates two
> new tables:
> drop table dbo.t_de_tracking
> drop table dbo.t_MR_TRACKING
> SELECT * INTO dbo.T_DE_TRACKING
> FROM OPENQUERY(ACS_SLR_DE, 'SELECT * FROM ACS_SLR_DATAENTRY..
> T_DE_TRACKING (nolock)')
> SELECT * INTO dbo.T_MR_TRACKING
> FROM OPENQUERY(ACS_SLR_MR, 'SELECT * FROM ACS_SLR_MAILROOM..
> T_MR_TRACKING (nolock)')
> When these tables are created the owner is NOT dbo. How can I get the owner
> of these tables to become dbo when created by this stored procedure?
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200510/1
>

Owner of tables

I have a stored procedure, that among other things drops and recreates two
new tables:
drop table dbo.t_de_tracking
drop table dbo.t_MR_TRACKING
SELECT * INTO dbo.T_DE_TRACKING
FROM OPENQUERY(ACS_SLR_DE, 'SELECT * FROM ACS_SLR_DATAENTRY..
T_DE_TRACKING (nolock)')
SELECT * INTO dbo.T_MR_TRACKING
FROM OPENQUERY(ACS_SLR_MR, 'SELECT * FROM ACS_SLR_MAILROOM..
T_MR_TRACKING (nolock)')
When these tables are created the owner is NOT dbo. How can I get the owner
of these tables to become dbo when created by this stored procedure?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200510/1
Robert,
Do you really want to drop/create the tables or just TRUNCATE the table and
reload it? Probably not the best approach esp if there are a lot of records
and/or NC indexes. What are the fixed server and fixed database role
memberships of the user creating this proc? Also, you might want to build
some IF logic into testing if the tables exist prior to dropping them.
HTH
Jerry
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:55d48972e2388@.uwe...
>I have a stored procedure, that among other things drops and recreates two
> new tables:
> drop table dbo.t_de_tracking
> drop table dbo.t_MR_TRACKING
> SELECT * INTO dbo.T_DE_TRACKING
> FROM OPENQUERY(ACS_SLR_DE, 'SELECT * FROM ACS_SLR_DATAENTRY..
> T_DE_TRACKING (nolock)')
> SELECT * INTO dbo.T_MR_TRACKING
> FROM OPENQUERY(ACS_SLR_MR, 'SELECT * FROM ACS_SLR_MAILROOM..
> T_MR_TRACKING (nolock)')
> When these tables are created the owner is NOT dbo. How can I get the
> owner
> of these tables to become dbo when created by this stored procedure?
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200510/1
|||Try naming the stored procedure dbo.[sproc name]
Thomas
"Robert R via droptable.com" wrote:

> I have a stored procedure, that among other things drops and recreates two
> new tables:
> drop table dbo.t_de_tracking
> drop table dbo.t_MR_TRACKING
> SELECT * INTO dbo.T_DE_TRACKING
> FROM OPENQUERY(ACS_SLR_DE, 'SELECT * FROM ACS_SLR_DATAENTRY..
> T_DE_TRACKING (nolock)')
> SELECT * INTO dbo.T_MR_TRACKING
> FROM OPENQUERY(ACS_SLR_MR, 'SELECT * FROM ACS_SLR_MAILROOM..
> T_MR_TRACKING (nolock)')
> When these tables are created the owner is NOT dbo. How can I get the owner
> of these tables to become dbo when created by this stored procedure?
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200510/1
>

owner of dburgent

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!

I bet you that you executed your SQL statements under the context of that user 'ss'. That is why it is not showing as "dbo."

The reason why you have to prefix those tables with the database name is because your connection is not using that database. In your connection string, you can specify the [database] property to the name of the database. Then you won't have to prefix your SQL with the database name from there on.

|||

You're right. The SQL statements is under the context of that user 'ss'.
The database is my brother's.And I can not use ss.My brother let me connect database to asp.net.
But what can I do?
My brother let me create a user named "ss",but it desn't work.My "ss" can not connect to the database "linlin".
help me.

|||

The ss user must be granted access to access the database. To do this, in enterprise manager, go to the users. Go to the properties of this user "ss" and under permissions, give the ss DB Owner access to the database.

owner data objects

I've a problem.

In my MSSql db I've some tables named username.mytable and only one
store procedure named dbo.sp;

CREATE TABLE [pippo].[mytable] (
[year] [int] NOT NULL ,
[month] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [pluto].[mytable] (
[year] [int] NOT NULL ,
[month] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE PROCEDURE [dbo].[sp]
AS
select * from mytable
GO

but when I try to execute dbo.sp (from pippo or pluto connection) I've
this error (users pippo and pluto are owner):

Server: Msg 208, Level 16, State 1, Procedure sp, Line 4
Invalid object name 'mytable'.

How can I access to pippo's (or pluto's) data from dbo.sp?

thanks!!zMatteo (origma@.edpsistem.it) writes:
> I've a problem.
> In my MSSql db I've some tables named username.mytable and only one
> store procedure named dbo.sp;
>
> CREATE TABLE [pippo].[mytable] (
> [year] [int] NOT NULL ,
> [month] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [pluto].[mytable] (
> [year] [int] NOT NULL ,
> [month] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE PROCEDURE [dbo].[sp]
> AS
> select * from mytable
> GO
>
> but when I try to execute dbo.sp (from pippo or pluto connection) I've
> this error (users pippo and pluto are owner):
> Server: Msg 208, Level 16, State 1, Procedure sp, Line 4
> Invalid object name 'mytable'.
> How can I access to pippo's (or pluto's) data from dbo.sp?

SELECT * FROM pippo.mytable

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns9629B0769BAFBYazorman@.127.0.0.1>...
> zMatteo (origma@.edpsistem.it) writes:
> > I've a problem.
> > In my MSSql db I've some tables named username.mytable and only one
> > store procedure named dbo.sp;
> > CREATE TABLE [pippo].[mytable] (
> > [year] [int] NOT NULL ,
> > [month] [int] NOT NULL
> > ) ON [PRIMARY]
> > GO
> > CREATE TABLE [pluto].[mytable] (
> > [year] [int] NOT NULL ,
> > [month] [int] NOT NULL
> > ) ON [PRIMARY]
> > GO
> > CREATE PROCEDURE [dbo].[sp]
> > AS
> > select * from mytable
> > GO
> > but when I try to execute dbo.sp (from pippo or pluto connection) I've
> > this error (users pippo and pluto are owner):
> > Server: Msg 208, Level 16, State 1, Procedure sp, Line 4
> > Invalid object name 'mytable'.
> > How can I access to pippo's (or pluto's) data from dbo.sp?
> SELECT * FROM pippo.mytable

but for user pluto i'd make a new store procedure...
I'd resolve the problem (attention: it's ok only for not sysadmin users)!!:

CREATE TABLE [dbo].[mytable] (
[user] [smallint] NOT NULL,
[year] [int] NOT NULL ,
[month] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE VIEW [dbo].[myview]
AS
select * from mytable
where user=user_id()
GO

CREATE PROCEDURE [dbo].[sp]
AS
select * from myview
GO

Owership of database tables

Dear all,
I have a database in SQL server 2000. In Ent. Manager, it shows that the
"Owner" of the tables in this database is "dbo". Is it possible to change the
owner to one of the database user in this database?
Thanks in advance.
Ivan
Hi
If the user is called Albert:
EXEC sp_changedbowner 'Albert'
Just be aware, once a user is the owner of an object, you can not remove the
user until a new owner is set.
Regards
Mike
"Ivan" wrote:

> Dear all,
> I have a database in SQL server 2000. In Ent. Manager, it shows that the
> "Owner" of the tables in this database is "dbo". Is it possible to change the
> owner to one of the database user in this database?
> Thanks in advance.
> Ivan
|||Continued...
To change the owner of an object like a table or a SP:
EXEC sp_changeobjectowner 'authors', 'Albert'
If you change the owner of a View or Stored Procedure, it does not change
the actual script that is stored, so when you script the SP or view out, the
old owner is still shown on the Create statement.
Regards
Mike
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> If the user is called Albert:
> EXEC sp_changedbowner 'Albert'
> Just be aware, once a user is the owner of an object, you can not remove the
> user until a new owner is set.
> Regards
> Mike
> "Ivan" wrote:
|||Thanks for your help!
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Continued...
> To change the owner of an object like a table or a SP:
> EXEC sp_changeobjectowner 'authors', 'Albert'
> If you change the owner of a View or Stored Procedure, it does not change
> the actual script that is stored, so when you script the SP or view out, the
> old owner is still shown on the Create statement.
> Regards
> Mike
> "Mike Epprecht (SQL MVP)" wrote:

Owership of database tables

Dear all,
I have a database in SQL server 2000. In Ent. Manager, it shows that the
"Owner" of the tables in this database is "dbo". Is it possible to change th
e
owner to one of the database user in this database?
Thanks in advance.
IvanHi
If the user is called Albert:
EXEC sp_changedbowner 'Albert'
Just be aware, once a user is the owner of an object, you can not remove the
user until a new owner is set.
Regards
Mike
"Ivan" wrote:

> Dear all,
> I have a database in SQL server 2000. In Ent. Manager, it shows that the
> "Owner" of the tables in this database is "dbo". Is it possible to change
the
> owner to one of the database user in this database?
> Thanks in advance.
> Ivan|||Continued...
To change the owner of an object like a table or a SP:
EXEC sp_changeobjectowner 'authors', 'Albert'
If you change the owner of a View or Stored Procedure, it does not change
the actual script that is stored, so when you script the SP or view out, the
old owner is still shown on the Create statement.
Regards
Mike
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> If the user is called Albert:
> EXEC sp_changedbowner 'Albert'
> Just be aware, once a user is the owner of an object, you can not remove t
he
> user until a new owner is set.
> Regards
> Mike
> "Ivan" wrote:
>|||Thanks for your help!
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Continued...
> To change the owner of an object like a table or a SP:
> EXEC sp_changeobjectowner 'authors', 'Albert'
> If you change the owner of a View or Stored Procedure, it does not change
> the actual script that is stored, so when you script the SP or view out, t
he
> old owner is still shown on the Create statement.
> Regards
> Mike
> "Mike Epprecht (SQL MVP)" wrote:
>

Owership of database tables

Dear all,
I have a database in SQL server 2000. In Ent. Manager, it shows that the
"Owner" of the tables in this database is "dbo". Is it possible to change the
owner to one of the database user in this database?
Thanks in advance.
IvanHi
If the user is called Albert:
EXEC sp_changedbowner 'Albert'
Just be aware, once a user is the owner of an object, you can not remove the
user until a new owner is set.
Regards
Mike
"Ivan" wrote:
> Dear all,
> I have a database in SQL server 2000. In Ent. Manager, it shows that the
> "Owner" of the tables in this database is "dbo". Is it possible to change the
> owner to one of the database user in this database?
> Thanks in advance.
> Ivan|||Continued...
To change the owner of an object like a table or a SP:
EXEC sp_changeobjectowner 'authors', 'Albert'
If you change the owner of a View or Stored Procedure, it does not change
the actual script that is stored, so when you script the SP or view out, the
old owner is still shown on the Create statement.
Regards
Mike
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> If the user is called Albert:
> EXEC sp_changedbowner 'Albert'
> Just be aware, once a user is the owner of an object, you can not remove the
> user until a new owner is set.
> Regards
> Mike
> "Ivan" wrote:
> > Dear all,
> >
> > I have a database in SQL server 2000. In Ent. Manager, it shows that the
> > "Owner" of the tables in this database is "dbo". Is it possible to change the
> > owner to one of the database user in this database?
> > Thanks in advance.
> >
> > Ivan|||Thanks for your help!
"Mike Epprecht (SQL MVP)" wrote:
> Continued...
> To change the owner of an object like a table or a SP:
> EXEC sp_changeobjectowner 'authors', 'Albert'
> If you change the owner of a View or Stored Procedure, it does not change
> the actual script that is stored, so when you script the SP or view out, the
> old owner is still shown on the Create statement.
> Regards
> Mike
> "Mike Epprecht (SQL MVP)" wrote:
> > Hi
> >
> > If the user is called Albert:
> >
> > EXEC sp_changedbowner 'Albert'
> >
> > Just be aware, once a user is the owner of an object, you can not remove the
> > user until a new owner is set.
> >
> > Regards
> > Mike
> >
> > "Ivan" wrote:
> >
> > > Dear all,
> > >
> > > I have a database in SQL server 2000. In Ent. Manager, it shows that the
> > > "Owner" of the tables in this database is "dbo". Is it possible to change the
> > > owner to one of the database user in this database?
> > > Thanks in advance.
> > >
> > > Ivan

Owership of database objects, Yu'll login to use?

I am very new to SQL Server 2000 but was a Sybase DBA for 2 years. In
Sybase we would alias a developer to DBO (of that database only) so
that all objects (tables/stored procedures) would show up as owned by
DBO.
In SQL Server it looks to me that the only way to get obects owned by
DBO is to load them as SA or add the developer to the fixed role of
sysadmin. Neither of those appears to be the way to go.
So I'm wondering how other shows handle this issue. Do you set up an
specific account for loading objects and maintaining a SQL server
database?
Thanks,
Randy K
wawork@.hotmail.comHi Randy
I worked with Sybase for 8 years, and have been working with Microsoft SQL
Server since its inception.
In SQL Server, you can alias logins to the dbo user of a database exactly
the same way you do with Sybase, with the sp_addalias procedure.
Is it possible you're trying to achieve this functionality using the
db_owner role? Putting a user in that role will give her all the rights and
privileges of the database owner, but her name will still be her own name,
and not dbo. So any objects she creates, by default will not be owned by
dbo. However, someone in the db_owner role can create objects and specify
that the owner should be dbo:
CREATE TABLE dbo.newtable
(column ...)
So there are really two different things going on. You can actually give
someone the name dbo using sp_addalias, or you can put them in the db_owner
role, and they can specify that new obects are to be owned by dbo.
It's actually recommended that you always specify the owner name of objects,
both when creating them and when referencing them, and if you get into this
habit, you don't lose anything by just using the db_owner role.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Randy K" <wawork@.hotmail.com> wrote in message
news:3f27f6bc.59865578@.msnews.microsoft.com...
> I am very new to SQL Server 2000 but was a Sybase DBA for 2 years. In
> Sybase we would alias a developer to DBO (of that database only) so
> that all objects (tables/stored procedures) would show up as owned by
> DBO.
> In SQL Server it looks to me that the only way to get obects owned by
> DBO is to load them as SA or add the developer to the fixed role of
> sysadmin. Neither of those appears to be the way to go.
> So I'm wondering how other shows handle this issue. Do you set up an
> specific account for loading objects and maintaining a SQL server
> database?
> Thanks,
> Randy K
> wawork@.hotmail.com|||Unfortunately, a user with the db_owner role can
> not change the ownership of a object when creating it using the ESRI
> software.
Yes, this can be a problem. Even Microsoft's own Enterprise Manager didn't
allow specifying a different owner in SQL Server 7, and although SQL 2000
allows it, it is not obvious how to do it. That's one of the reasons why I
never use GUIs to create tables. :-)
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Randy K" <wawork@.hotmail.com> wrote in message
news:3f284734.18529640@.msnews.microsoft.com...
> Thank you Kalen that's what I was looking for. I had always used
> Sybase Central GUI to alias users as DBO and mistaking thought
> assigning a user to the db_owner role in SQL Server was the same
> thing.
> I'll need to do some testing since we are using SQL Server with ArcSDE
> by ESRI on top. Of the two methods only the first, spp_addalias,
> works with ArcSDE. Unfortunately, a user with the db_owner role can
> not change the ownership of a object when creating it using the ESRI
> software.
>
> On Wed, 30 Jul 2003 10:21:16 -0700, "Kalen Delaney"
> <replies@.public_newsgroups.com> wrote:
> >Hi Randy
> >
> >I worked with Sybase for 8 years, and have been working with Microsoft
SQL
> >Server since its inception.
> >
> >In SQL Server, you can alias logins to the dbo user of a database exactly
> >the same way you do with Sybase, with the sp_addalias procedure.
> >
> >Is it possible you're trying to achieve this functionality using the
> >db_owner role? Putting a user in that role will give her all the rights
and
> >privileges of the database owner, but her name will still be her own
name,
> >and not dbo. So any objects she creates, by default will not be owned by
> >dbo. However, someone in the db_owner role can create objects and specify
> >that the owner should be dbo:
> >
> >CREATE TABLE dbo.newtable
> >(column ...)
> >
> >So there are really two different things going on. You can actually give
> >someone the name dbo using sp_addalias, or you can put them in the
db_owner
> >role, and they can specify that new obects are to be owned by dbo.
> >
> >It's actually recommended that you always specify the owner name of
objects,
> >both when creating them and when referencing them, and if you get into
this
> >habit, you don't lose anything by just using the db_owner role.
> >
> >--
> >HTH
> >--
> >Kalen Delaney
> >SQL Server MVP
> >www.SolidQualityLearning.com
> >
> >
> >"Randy K" <wawork@.hotmail.com> wrote in message
> >news:3f27f6bc.59865578@.msnews.microsoft.com...
> >> I am very new to SQL Server 2000 but was a Sybase DBA for 2 years. In
> >> Sybase we would alias a developer to DBO (of that database only) so
> >> that all objects (tables/stored procedures) would show up as owned by
> >> DBO.
> >>
> >> In SQL Server it looks to me that the only way to get obects owned by
> >> DBO is to load them as SA or add the developer to the fixed role of
> >> sysadmin. Neither of those appears to be the way to go.
> >>
> >> So I'm wondering how other shows handle this issue. Do you set up an
> >> specific account for loading objects and maintaining a SQL server
> >> database?
> >>
> >> Thanks,
> >> Randy K
> >> wawork@.hotmail.com
> >
> >
>

overload function call

Hi,

I want to write one function like that

dbo.function( number , 1 , 2 ) ,

but I would like to overload, and send char or number

dbo.function( number, 'abx' , ' xpto' ).

I would like to keep the same name, Can I do this? or Do I need to write to differents functions

thanks,

Hi Alessandro,

Yes, this can be done. You would use the sql_variant datatype as such:

create function fntest (@.one sql_variant, @.two sql_variant)
returns int
as
begin
declare @.ret int

set @.ret = 0

if (select SQL_VARIANT_PROPERTY(@.one,'BaseType')) = 'int'
begin
set @.ret = 99
end

return (@.ret)
end;

The SQL_VARIANT_PROPERTY function will return the base data type, so you can therefore test for not only int, but varchar etc.

Cheers,

Rob

|||

Hi Rob,

Thank you.... Can I do this for return parameter?

Thanks

|||

Hi Alessandro,

Sure can, in the above example, you'd simply change the returning datatype:

create function fntest2 (@.one sql_variant, @.two sql_variant)
returns sql_variant
as
begin

Cheers,

Rob

|||

Hi Rob,

Yes it is true, but I can send or return float values sql_variant doesn't work for this :(

In fact I want to do this.

funtion ( number (always number) , char/number(param1) , char/number(param2) )

if number = 1

return param1

else param 2

similar to oracle decode, but I dont know how to do, do you have any ideia? Maybe clr function but I think overload function via c# doesn't work as well

thanks,

|||

Hi Alessandro,

I think I understand what you want, and the below will let you do this:

create function fntest2 (@.one sql_variant, @.two sql_variant)
returns sql_variant
as
begin
declare @.ret sql_variant

set @.ret = 0

if (select SQL_VARIANT_PROPERTY(@.one,'BaseType')) = 'int'
begin
set @.ret = @.one
end
else
begin
set @.ret = @.two
end

return (@.ret)
end;

Is that what you were after, or did you mean something else?

Cheers,
Rob

|||

Hi Rob,

Yes, but when you call with float from Select clauses like

select fntest( 1 , value1(float) , value2 (float) )

doesn't work I need to convert, have you tried?

Thanks

|||

Hi Alessandro,

What about the below:

create function fntest2 (@.one int, @.two sql_variant, @.three sql_variant)
returns sql_variant
as
begin
declare @.ret sql_variant

set @.ret = 0

if (select SQL_VARIANT_PROPERTY(@.two,'BaseType')) = 'int'
begin
set @.ret = 'it is an int'
end
else if (select SQL_VARIANT_PROPERTY(@.two,'BaseType')) = 'float'
begin
set @.ret = 'it is a float'
end
--more if else statements here...

return (@.ret)
end;

declare @.f float
set @.f = 123.9982

select tempdb.dbo.fntest2( 1 , @.f , 'another param here' )

This returns the character line "it is a float" as the param @.f is explicity defined as float. If you execute the below line:

select tempdb.dbo.fntest2( 1 , 23.55 , 'another param here' )

0 will be returned as 23.55 is interpreted as type numeric

Cheers,

Rob

|||

Hi Rob,

Woks well, thanks, however when I try doing this

select tempdb.dbo.fntest2( 1 , 23.55 , 'another param here' ) - tempdb.dbo.fntest2( 1 , 23.55 , 'another param here' )

I receive this msg

Operand data type sql_variant is invalid for subtract operator.

thanks,

|||

Hi Alessandro,

Yes, that's correct - you cannot perform mathematical operations on the sql_variant datatype. This makes sense as sql has no idea what type it is: it may be binary, or one may be char, and the other numeric or both may be char, in which case you would receive concatenation of the two char values: probably not what you intended...

You'll need to perform an explicit cast as such:

declare @.a sql_variant,
@.b sql_variant

set @.a = 2;
set @.b = 1;

begin try
select (cast(@.a as int) + cast(@.b as int)) as 'sum'
end try
begin catch
print ERROR_MESSAGE()
end catch

You'll need to include the explicit cast in a try block in order to catch any conversion errors (for example, if @.a is a char value, not int).

Cheers,

Rob

overload function call

Hi,

I want to write one function like that

dbo.function( number , 1 , 2 ) ,

but I would like to overload, and send char or number

dbo.function( number, 'abx' , ' xpto' ).

I would like to keep the same name, Can I do this? or Do I need to write to differents functions

thanks,

Hi Alessandro,

Yes, this can be done. You would use the sql_variant datatype as such:

create function fntest (@.one sql_variant, @.two sql_variant)
returns int
as
begin
declare @.ret int

set @.ret = 0

if (select SQL_VARIANT_PROPERTY(@.one,'BaseType')) = 'int'
begin
set @.ret = 99
end

return (@.ret)
end;

The SQL_VARIANT_PROPERTY function will return the base data type, so you can therefore test for not only int, but varchar etc.

Cheers,

Rob

|||

Hi Rob,

Thank you.... Can I do this for return parameter?

Thanks

|||

Hi Alessandro,

Sure can, in the above example, you'd simply change the returning datatype:

create function fntest2 (@.one sql_variant, @.two sql_variant)
returns sql_variant
as
begin

Cheers,

Rob

|||

Hi Rob,

Yes it is true, but I can send or return float values sql_variant doesn't work for this :(

In fact I want to do this.

funtion ( number (always number) , char/number(param1) , char/number(param2) )

if number = 1

return param1

else param 2

similar to oracle decode, but I dont know how to do, do you have any ideia? Maybe clr function but I think overload function via c# doesn't work as well

thanks,

|||

Hi Alessandro,

I think I understand what you want, and the below will let you do this:

create function fntest2 (@.one sql_variant, @.two sql_variant)
returns sql_variant
as
begin
declare @.ret sql_variant

set @.ret = 0

if (select SQL_VARIANT_PROPERTY(@.one,'BaseType')) = 'int'
begin
set @.ret = @.one
end
else
begin
set @.ret = @.two
end

return (@.ret)
end;

Is that what you were after, or did you mean something else?

Cheers,
Rob

|||

Hi Rob,

Yes, but when you call with float from Select clauses like

select fntest( 1 , value1(float) , value2 (float) )

doesn't work I need to convert, have you tried?

Thanks

|||

Hi Alessandro,

What about the below:

create function fntest2 (@.one int, @.two sql_variant, @.three sql_variant)
returns sql_variant
as
begin
declare @.ret sql_variant

set @.ret = 0

if (select SQL_VARIANT_PROPERTY(@.two,'BaseType')) = 'int'
begin
set @.ret = 'it is an int'
end
else if (select SQL_VARIANT_PROPERTY(@.two,'BaseType')) = 'float'
begin
set @.ret = 'it is a float'
end
--more if else statements here...

return (@.ret)
end;

declare @.f float
set @.f = 123.9982

select tempdb.dbo.fntest2( 1 , @.f , 'another param here' )

This returns the character line "it is a float" as the param @.f is explicity defined as float. If you execute the below line:

select tempdb.dbo.fntest2( 1 , 23.55 , 'another param here' )

0 will be returned as 23.55 is interpreted as type numeric

Cheers,

Rob

|||

Hi Rob,

Woks well, thanks, however when I try doing this

select tempdb.dbo.fntest2( 1 , 23.55 , 'another param here' ) - tempdb.dbo.fntest2( 1 , 23.55 , 'another param here' )

I receive this msg

Operand data type sql_variant is invalid for subtract operator.

thanks,

|||

Hi Alessandro,

Yes, that's correct - you cannot perform mathematical operations on the sql_variant datatype. This makes sense as sql has no idea what type it is: it may be binary, or one may be char, and the other numeric or both may be char, in which case you would receive concatenation of the two char values: probably not what you intended...

You'll need to perform an explicit cast as such:

declare @.a sql_variant,
@.b sql_variant

set @.a = 2;
set @.b = 1;

begin try
select (cast(@.a as int) + cast(@.b as int)) as 'sum'
end try
begin catch
print ERROR_MESSAGE()
end catch

You'll need to include the explicit cast in a try block in order to catch any conversion errors (for example, if @.a is a char value, not int).

Cheers,

Rob

Overlapping Sets

I have the following table structure

CREATE TABLE [dbo].[QDisc](
[Id] [int] NOT NULL,
[MinVal] [int] NOT NULL,
[MaxVal] [int] NOT NULL,
[PerVal] [int] NOT NULL,
CONSTRAINT [PK_QDisc] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

I need to be able to select unique overlapping sets of data based on the minval and maxval.

Simple Example
minval maxval
5 15
16 25
10 20

query would produce
minval maxval
5 10
11 15
16 20
21 25

More Complex example
minval maxval
5 15
16 25
10 20
7 7
1 100

query would produce
minval maxval
1 5
6 6
7 7
8 10
11 15
16 20
21 25
26 100

Extra points if anyone can do this without SP and cursor but I'd be satisfied if someone supplied it that way.Extra points for doing your homework assignment for you?|||Extra points if anyone can do this without SP and cursor but I'd be satisfied if someone supplied it that way.Gosh - generous and gracious. How super.|||I'll give myself extra credit

ALTER PROCEDURE [dbo].[usp_Select_Disc]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

select minval, maxval into #QtyRange from qdisc

declare @.minval int
declare @.maxval int
declare @.xminval int
declare @.xmaxval int

declare @.pmaxval int
declare @.pmaxva2 int

declare @.loopflg bit
set @.loopflg = 1
while @.loopflg = 1
begin
set @.loopflg = 0
DECLARE curSel CURSOR FOR select minval, maxval from #QtyRange order by minval
open curSel
fetch next from curSel into @.minval, @.maxval
WHILE (@.@.FETCH_STATUS <> -1)
begin

set @.xminval = null
set @.xmaxval = null

select top(1) @.xminval = minval, @.xmaxval = maxval from #QtyRange where minval > @.minval or maxval > @.maxval order by minval

if @.maxval > @.xminval
begin
set @.pmaxval = @.maxval
if @.minval = @.xminval
begin
set @.pmaxva2 = @.xmaxval
update #QtyRange set maxval = @.xminval where minval = @.minval and maxval = @.maxval
update #QtyRange set minval = @.xminval + 1, maxval = @.pmaxval where minval = @.xminval and maxval = @.xmaxval
insert into #QtyRange values (@.pmaxval + 1, @.pmaxva2)
set @.loopflg = 1
break
end
update #QtyRange set maxval = @.xminval - 1 where minval = @.minval and maxval = @.maxval

if @.xmaxval > @.pmaxval
begin
set @.pmaxva2 = @.xmaxval
update #QtyRange set maxval = @.pmaxval where minval = @.xminval and maxval = @.xmaxval
insert into #QtyRange values (@.pmaxval + 1, @.pmaxva2)
set @.loopflg = 1
break
end
if @.xmaxval < @.pmaxval
insert into #QtyRange values (@.xmaxval + 1, @.pmaxval)
set @.loopflg = 1
end

if @.maxval = @.xminval
begin
set @.pmaxval = @.maxval

if @.minval = @.maxval
begin
update #QtyRange set minval = @.pmaxval + 1 where minval = @.xminval and maxval = @.xmaxval
set @.loopflg = 1
break
end
else
begin
update #QtyRange set maxval = @.pmaxval - 1 where minval = @.minval and maxval = @.maxval
if @.xminval <> @.xmaxval
begin
insert into #QtyRange values (@.pmaxval, @.pmaxval)
update #QtyRange set minval = @.pmaxval + 1 where minval = @.xminval and maxval = @.xmaxval
set @.loopflg = 1
break
end
end
end
fetch next from curSel into @.minval, @.maxval
end
Close curSel
DEALLOCATE curSel
end

select distinct(a.minval), a.maxval, sum(qdisc.perval) from #QtyRange a
inner join qdisc on a.minval >= qdisc.minval and a.maxval <= qdisc.maxval
group by a.minval,a.maxval
order by minval
END|||I'll give myself extra credit

You shouldn't, you used a cursor.|||You shouldn't, you used a cursor.
I'm not sure it can be done without a cursor or some other form of looping. I see no way to do it with a simple SELECT statement.|||neither do I, but that was his self-professed requirement for extra points.|||No no no. That was the requirement for YOU to get extra points. He, of course, is free to give himself as much credit as he wants.|||how many points do i need to get a free ship?|||this worked for both the data sets provided by u. i have tried with a couple of other sets and appears to be working. interested to know if it worked at your end or not...

create table #tt1 (Id int identity (1,1), Val int )
insert into #tt1 (Val) select Val from (
select Minval as Val from QDisc
union all
select Maxval as Val from QDisc
) A order by 1

select B.Id,A.Val AVal, B.Val BVal, C.Val CVal into #tt2
from #tt1 A, #tt1 B, #tt1 C where A.Id+1 =* B.Id and C.Id-1 =* B.Id

update #tt2 set BVal=BVal-1 where AVal+1=BVal
update #tt2 set CVal=Null where BVal+1=CVal
update #tt2 set CVal=CVal-1 from #tt2 A where A.CVal= (select BVal from #tt2 where #tt2.id = A.id+1 and BVal=CVal)

select
BVal+ case when BVal=(select min(BVal) from #tt2) then 0 when BVal= CVal then 0 else 1 end as MinVal,
CVal as MaxVal
from #tt2
where CVal is not null