How do determine who is the owner of an object?

I want to find objects that are owned by SQL Logins that no longer exist.
Help is appreciated,
ThanksWalter
You cannot drop Logins that have owned objects
"WalterWalt" <,> wrote in message
news:OvVYN3sHHHA.4688@.TK2MSFTNGP04.phx.gbl...
>I want to find objects that are owned by SQL Logins that no longer exist.
> Help is appreciated,
> Thanks
>|||I dropped the builtin\administrators login and have a sneaking suspicion
that that left some objects with an orphaned owner. I would like to confirm
this is not the case by running a query to get the owner of all obects. Can
you answer my question? Thanks.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OSB7k%23sHHHA.420@.TK2MSFTNGP06.phx.gbl...
> Walter
> You cannot drop Logins that have owned objects
>
>
> "WalterWalt" <,> wrote in message
> news:OvVYN3sHHHA.4688@.TK2MSFTNGP04.phx.gbl...
>|||WalterWalt wrote:
> I want to find objects that are owned by SQL Logins that no longer exist.
> Help is appreciated,
> Thanks
I think sysobjects.uid is the user ID in sysusers, in a database.
Linking database users to logins is a further step.
For instance:
create table tempdb.guest.guesttable ( i int)
select * from tempdb.dbo.sysobjects where name = 'guesttable'
select * from tempdb.dbo.sysusers order by uid
HTH|||>I dropped the builtin\administrators login and have a sneaking suspicion
>that that left some objects with an orphaned owner.
Why do you suspect this? Assuming SQL 2000, logins do not own database
objects directly. Database users own database objects and SQL will not let
you drop a database user that owns objects. Also, objects cannot be owned
by Windows groups (builtin\administrators is a Windows group).
Separately, users are mapped to database logins. SQL Server will not let
you drop a login that is mapped to a database user. However, you can end up
with orphaned users (users without logins) if you restore/attach a database.

> I would like to confirm this is not the case by running a query to get the
> owner of all obects. Can you answer my question?
Run the query below in your databases. I would expect that ObjectOwner will
not be NULL. OwnerLogin may be null for system schema or if you have
orphaned database users that also own objects.
SELECT DISTINCT
u.name AS ObjectOwner,
l.name AS OwnerLogin
FROM sysobjects o
LEFT JOIN sysusers u ON
u.uid = o.uid
LEFT JOIN master.dbo.syslogins l ON
l.sid = u.sid
Hope this helps.
Dan Guzman
SQL Server MVP
"WalterWalt" <,> wrote in message
news:egKnrGtHHHA.1816@.TK2MSFTNGP06.phx.gbl...
>I dropped the builtin\administrators login and have a sneaking suspicion
>that that left some objects with an orphaned owner. I would like to
>confirm this is not the case by running a query to get the owner of all
>obects. Can you answer my question? Thanks.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OSB7k%23sHHHA.420@.TK2MSFTNGP06.phx.gbl...
>|||OwnerLogin for sys is null. Does that mean there are orphaned database
users?
Thanks for you help.
If I logon to my local machine that is running SQL Server 2000 as
administrator, and then logon to SQL Server using Windows Authentication I
am a member of the sysadmin server role even though I don't have a SQL
Server login explicitly created for Administrator. I get my permissions
implicitly through the BUILTIN\Administrators group. While logged into SQL
Server in this context I create a database called TEST and a table called
tblTEST. Then I log out of SQL and then back in as sa. I delete the
BUILTIN\Administrators group. So who is the owner of db TEST and tblTest
and how do I query for other objects like that?
Thanks and sorry to Uri is sounded snappy.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:3624EDF3-0531-462E-B641-40124E28B6F5@.microsoft.com...
> Why do you suspect this? Assuming SQL 2000, logins do not own database
> objects directly. Database users own database objects and SQL will not
> let you drop a database user that owns objects. Also, objects cannot be
> owned by Windows groups (builtin\administrators is a Windows group).
> Separately, users are mapped to database logins. SQL Server will not let
> you drop a login that is mapped to a database user. However, you can end
> up with orphaned users (users without logins) if you restore/attach a
> database.
>
> Run the query below in your databases. I would expect that ObjectOwner
> will not be NULL. OwnerLogin may be null for system schema or if you have
> orphaned database users that also own objects.
> SELECT DISTINCT
> u.name AS ObjectOwner,
> l.name AS OwnerLogin
> FROM sysobjects o
> LEFT JOIN sysusers u ON
> u.uid = o.uid
> LEFT JOIN master.dbo.syslogins l ON
> l.sid = u.sid
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "WalterWalt" <,> wrote in message
> news:egKnrGtHHHA.1816@.TK2MSFTNGP06.phx.gbl...
>|||> OwnerLogin for sys is null. Does that mean there are orphaned database
> users?
System schema users (e.g. INFORMATION_SCHEMA) are special cases and don't
need to be associated with logins in SQL 2000. However, ths sys schema was
introduced in SQL 2005 - aren't we were talking about SQL 2000 here? There
are major differences between SQL 2000 and 2005 related to user/schema
separation.

> I delete the BUILTIN\Administrators group. So who is the owner of db TEST
> and tblTest
The tblTest table will continue to be owned by user dbo. The TEST database
will still to be owned by your Windows account. Note that the database
owner database owner will be your Windows account because you connected via
Windows group membership (BUILTIN\Administrators) when you created the
database. Databases can't be owned by Windows groups.

> and how do I query for other objects like that?
Database ownership is recorded in sysdatabases and also as the mapping
between the dbo database user and syslogins. The login mapped to the dbo
user and the database owner should be the same but can get out-of-sync after
a restore or attach. You can use sp_changedbowner to change/fix database
ownership. The query below can identify a mismatch.
SELECT
u.name AS DatabaseUser,
l.name AS DboLogin,
l2.name AS DatabaseOwner
FROM sysusers u
LEFT JOIN master.dbo.syslogins l ON
l.sid = u.sid
JOIN master.dbo.sysdatabases d on
d.name = DB_NAME()
LEFT JOIN master.dbo.syslogins l2 ON
l2.sid = d.sid
WHERE u.name = 'dbo'
Hope this helps.
Dan Guzman
SQL Server MVP
"WalterWalt" <,> wrote in message
news:Ow6jV%236HHHA.4992@.TK2MSFTNGP04.phx.gbl...
> OwnerLogin for sys is null. Does that mean there are orphaned database
> users?
> Thanks for you help.
> If I logon to my local machine that is running SQL Server 2000 as
> administrator, and then logon to SQL Server using Windows Authentication I
> am a member of the sysadmin server role even though I don't have a SQL
> Server login explicitly created for Administrator. I get my permissions
> implicitly through the BUILTIN\Administrators group. While logged into
> SQL Server in this context I create a database called TEST and a table
> called tblTEST. Then I log out of SQL and then back in as sa. I delete
> the BUILTIN\Administrators group. So who is the owner of db TEST and
> tblTest and how do I query for other objects like that?
> Thanks and sorry to Uri is sounded snappy.
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:3624EDF3-0531-462E-B641-40124E28B6F5@.microsoft.com...
>

How can you mass-validate stored procedures, views, etc?

I am looking for a way to validate all objects in the database that is is
possible for in some automated fashion. For instance, if a column is droppe
d
from a table stored procedures that rely on it will fail on the next
execution, but I want to be able to run a process that will reveal that prio
r
to execution. I would want to do that for views and user defined functions
as well. Other than executing every procedure and function and selecting
from every view does anyone know of a way to do this?
I thought I'd just script the objects out for ALTER and run that script
against the database but it appears you can only create an ALTER script one
object at a time through EM and with thousands of objects I really don't wan
t
to go through that.
I created a script that ran sp_recompile against every stored procedure,
then another block that executed every stored procedure without supplying an
y
parameters and that seemed to catch stored procedures that relied on missing
columns. The problem with that is it makes the assumption that the stored
procedure would either fail when no parameters were supplied, or would be
harmless when the stored procedure executed either because it required no
parameters or had defaults for all parameters. Of course this also only
worked for stored procedures and not the functions and views.
I made the mistake of changing a view without realizing that it was used in
a stored procedure and when that stored procedure executed it failed. You
can't depend on the dependencies that SQL maintains since they are not alway
s
correct, so you can't even make note of the dependent objects and selectivel
y
check only those objects without risking missing a dependency.
Any suggestions would be greatly appreciated.Byron (Byron@.discussions.microsoft.com) writes:
> I am looking for a way to validate all objects in the database that is
> is possible for in some automated fashion. For instance, if a column is
> dropped from a table stored procedures that rely on it will fail on the
> next execution, but I want to be able to run a process that will reveal
> that prior to execution. I would want to do that for views and user
> defined functions as well. Other than executing every procedure and
> function and selecting from every view does anyone know of a way to do
> this?
> I thought I'd just script the objects out for ALTER and run that script
> against the database but it appears you can only create an ALTER script
> one object at a time through EM and with thousands of objects I really
> don't want to go through that.
>...
This is indeed a difficult situation, and I don't really think there
is a single good way around it.
The method in the above paragraph is fairly easy to apply: use Generate
Scripts from Enterprise Manager to script the entire database. Then
open the generated script in a an editor and change CREATE PROCEDURE
to ALTER PROCEDURE.
However, you may not catch all errors this way. Say that you have:
INSERT #temp (...)
SELECT ..., missing_column
FROM ...
Because of deferred name resolution, SQL Server will not alert you of the
missing column. It sees that #temp is missing, and will defer compilation
of that statement until #temp has been created, and thus the error will
not occur until run-time.
The approach I use myself is based on the fact that we have all code
under version control. We also have a build tool that goes head over
heels to nullify the effect of deferred name resolution, by creating
all temp tables in an SP before loading the procedure. Thus a missing
column is noticed directly. Furthermore, it's easy for me to build an
empty database from SourceSafe, which will give me a complete sysdepends
that I can rely on.

> I created a script that ran sp_recompile against every stored procedure,
> then another block that executed every stored procedure without
> supplying any parameters and that seemed to catch stored procedures that
> relied on missing columns. The problem with that is it makes the
> assumption that the stored procedure would either fail when no
> parameters were supplied, or would be harmless when the stored procedure
> executed either because it required no parameters or had defaults for
> all parameters. Of course this also only worked for stored procedures
> and not the functions and views.
Our build-and-load tool is available for free download on
http://www.abaris.se/abaperls/. To get started with it, may be overmuch.
However, the toolset in includes a free-standing tool SPTRITEST which
performs essentially the above, but also provides dummy value for all
parameters, so it permits you test far more procedures. It goes without
saying that you should run this on a *copy* of the database, or else
you get a load of junk in it.
And it may still not find all cases of missing columns, since if there
are IF-ELSE branches, you are likely to pass over only one of them.
I also need to add the disclaimer that I wrote it in 6.5 days, and I
have not used it myself for ages.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

How can only certain objects (dimensions, cubes) be deployed?

How can only certain objects (dimensions, cubes) be deployed?

From Visual Studio I would like to deploy individual items like a dimension or cube without all of the changes in the other objects being deployed.

I don’t see any way to do that. Every time one thing gets deployed, all changes get deployed.

Visual Studio sends a Message Box Stating that the whole SSAS Database will be overwritten on the server and asks if you want to deploy or not.

We have multiple developers working and it has become a problem. We can’t deploy just those changes that are ready without overwriting the whole database.

I feel your pain...I have the same issues here (even though I'm the only developer on our BI apps). One possibility could be to have your developers work directly on the dev analysis server and add objects there. Then, you can use SSMS to individually process objects (dimensions, partitions, measure groups, etc). I'm also going to try deploying all my changes to a "staging" Analytics Database, which could be swapped out with the production DB when necessary.

It'd be great if there was a set of best-practices around the complete SSAS lifecycle.|||

While the underlying XMLA commands do support deploying single objects like a cube or dimension, it can get a bit difficult to manage due to the dependancy relationships between the various objects.

Probably a better approach with multiple developers would be to use a version control system. This way you could check-in your changes and do a "get latest" to make sure you are deploying the latest version of all the objects.

Although having said this, by remarkable coincidence I have been working on an add-in for Visual Studio that lets you deploy only the calculation script for a cube. It would probably be technically possible to extend this to deploy just a cube or dimension, but I am still concerned about the effect that this may have on dependant objects if you had multiple developers working on the same database. I still think it would be worth looking into using a version control system.

|||

We use Visual SourceSafe. The situation is as follows:

I have the Time dimension checked out and am making changes. Bob has the CashRecxeipts cube checked out and is making changes.

If I finish 1st and am ready to deploy the new Time dimension, it will invalidate all cubes that use the Time dimension. Bob will have to get the latest version of the Time dimension and fix the relatoinships for it in the CashReceipts cube before he deploys his changes.

If Bob deploys without getting the latest version of the Time dimension he will overwrite my changes.

Also, I have to modify all cubes that use the Time dimension and check their relationships to the Time dimension before I can deploy. I can't modify the CashReceipts cube because Bob has it checked out. I am at a standstill.

It is very difficult to coordinate with developers when we are in different cities.

|||

I don't see any easy answer to your question.

In your scenario, it might be possible to write something that would deploy just the altered time dimension and just the CashReceipts cube, but if you did this the CashReciepts cube would still be broken if it was not first adjusted to cater for the changes in the time dimension.

One approach that is sometimes employed (not with SSAS as far as I know, but the theory would still apply) is to have only one person (or process) that deploys to the server. They do a "get latest" from Source Safe, check that everything builds and then deploys, if anyone wants to get something new onto the server they have to check their changes in and let this person know. Ideally each developer should have their own local copy of the DB, preferrably with a small set of data so that they can process it quickly and test their changes before checking them in.

There are even tools that exist for platforms like .Net and Java, that will check the latest code out of the version control system when a new checkin is detected and build and deploy. I don't think any of these tools have hooks to SSAS, but most of them are fairly extensible. CruiseControl.Net http://ccnet.thoughtworks.com/ is one example, if you search for "Continuous Integration" you should be able to find others.

Mark Garner http://mgarner.wordpress.com/ has done some posts on his blog about applying these sorts of principles to Data Marts http://wordpress.com/tag/agile-data-warehouse/

|||

Darren,

Thanks for your suggestions and information on tools and procedures. They are good ideas and should prove helpful.