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 check user name?

I have COM object working with database via connection with administrative
rights.
I need method for checking user name: can this user has db access or no.
I want to check:
1. SQL Server users
2. Windows users which added as a SQL Server logins/db users
3. Windows users which can connect to db as a members of windows groups
added to SQL Server.
Thnx.have you looked into using SQL-DMO. The object model has collections and
methods to do all you ask.
regards,
Mark Baekdal
MSN m_baekdal@.hotmail.com
+44 (0)141 416 1490
+44 (0)208 241 1762
http://www.dbghost.com
http://www.innovartis.co.uk
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Oleg Cherkasenko" wrote:

> I have COM object working with database via connection with administrative
> rights.
> I need method for checking user name: can this user has db access or no.
> I want to check:
> 1. SQL Server users
> 2. Windows users which added as a SQL Server logins/db users
> 3. Windows users which can connect to db as a members of windows groups
> added to SQL Server.
> Thnx.
>
>sql

how check if Transaction object already rollback or not?

Hi,
i would like to know how to determine a transaction is already rollback so that we dont have to reroll back again and get an exception like below:

This SqlTransaction has completed;
it is no longer usable. at System.Data.SqlClient.SqlTransaction.Rollback()

Please help because when i tried to do this: objTrans.rollback() and i got the above exception.

Regards,If the SqlTransaction's Connection property is nothing, then the transaction is no longer valid (and you should not call Rollback() ). Here's the docs for a code sample:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqltransactionclasstopic.asp|||Thanks alot..
actually i have another issue that is somehow related to this thread, i m using dataAdater to populate my TreeView COntrol but if i refresh 2 browser at the same time it will give me 2 exception. The first exception is

1)There is already an open DataReader associated with this Connection which must be closed first.
2)The SqlCommand is currently busy Open, Fetching.

I m not so sure why can this be? i m doing recursive loop and below is my code

private void LoadNode()
{
//clear the tree Component
TreeView1.Nodes.Clear();
string Tsql;
TreeNode Node;
Tsql="sp_framework_get_imis_function";
DataSet dsTree;
dsTree=ExecuteDbConnection.fillDataset(Tsql,imis.framework.net.ImisUser .getDetails.userID);
int i=0;
TreeNode tempNode=null;
if (dsTree.Tables.Count <= 0) return;
while (i<= dsTree.Tables[0].Rows.Count -1)
{
//create a new node for parent
//get the parent node
Node=new TreeNode();
if (dsTree.Tables[0].Rows[i]["function_desc"].ToString()!=string.Empty|| dsTree.Tables[0].Rows[i]["function_desc_chinese"].ToString() !=string.Empty)
{
//desc_chinese is not null
if (dsTree.Tables[0].Rows[i]["function_desc"].ToString() !=string.Empty && dsTree.Tables[0].Rows[i]["function_desc_chinese"].ToString()!=string.Empty ) Node.Text=dsTree.Tables[0].Rows[i]["function_desc_chinese"].ToString()==null?dsTree.Tables[0].Rows[i]["function_desc"].ToString() :dsTree.Tables[0].Rows[i]["function_desc"].ToString() + "<br>"+dsTree.Tables[0].Rows[i]["function_desc_chinese"].ToString();
if(dsTree.Tables[0].Rows[i]["function_desc"].ToString()==string.Empty && dsTree.Tables[0].Rows[i]["function_desc_chinese"].ToString()!=string.Empty )Node.Text=dsTree.Tables[0].Rows[i]["function_desc_chinese"].ToString();
if(dsTree.Tables[0].Rows[i]["function_desc"].ToString()!=string.Empty && dsTree.Tables[0].Rows[i]["function_desc_chinese"].ToString()==string.Empty ) Node.Text=dsTree.Tables[0].Rows[i]["function_desc"].ToString();
}
else
{
//throw exception because both desc and desc_chinese is not null.
return;
}
Node.SelectedImageUrl=dsTree.Tables[0].Rows[i]["img_selected"].ToString();
Node.ImageUrl =dsTree.Tables[0].Rows[i]["img"].ToString();
if (dsTree.Tables[0].Rows[i]["program"].ToString() !="")
{
Node.NavigateUrl ="index.aspx?pageID=" +dsTree.Tables[0].Rows[i]["function_id"];
Node.Target="WorkFrame";
}
tempNode=Node;
TreeView1.Nodes.Add(Node);
ChildNode(tempNode,Convert.ToInt32(dsTree.Tables[0].Rows[i]["function_id"]));
i=i+1;
}
}//end sub
private void ChildNode(TreeNode node,int functionID)
{
try
{
TreeNode nodeX;
string userID=imis.framework.net.ImisUser.getDetails.userID;
string tSql;
string TempSQL;
if (imis.framework.net.ImisUser.getDetails.userID ==null)
return;
TempSQL="sp_framework_get_imis_function_child_id";
DataSet dsChild;
dsChild=ExecuteDbConnection.fillDataset(TempSQL,functionID,userID);
int i=0;
if (dsChild.Tables[0].Rows.Count >0)
{
while (i <=dsChild.Tables[0].Rows.Count-1)
{
//get all the child of the belong the the first level nodes
nodeX=new TreeNode();
if (dsChild.Tables[0].Rows[i]["function_desc"].ToString()!=string.Empty || dsChild.Tables[0].Rows[i]["function_desc_chinese"].ToString() !=string.Empty)
{
//desc_chinese is not null
if (dsChild.Tables[0].Rows[i]["function_desc"].ToString() !=string.Empty && dsChild.Tables[0].Rows[i]["function_desc_chinese"].ToString()!=string.Empty) nodeX.Text=dsChild.Tables[0].Rows[i]["function_desc_chinese"].ToString()==null?dsChild.Tables[0].Rows[i]["function_desc"].ToString() :dsChild.Tables[0].Rows[i]["function_desc"].ToString() + "<br>"+dsChild.Tables[0].Rows[i]["function_desc_chinese"].ToString();
if(dsChild.Tables[0].Rows[i]["function_desc"].ToString()==string.Empty && dsChild.Tables[0].Rows[i]["function_desc_chinese"].ToString()!=string.Empty)nodeX.Text=dsChild.Tables[0].Rows[i]["function_desc_chinese"].ToString();
if(dsChild.Tables[0].Rows[i]["function_desc"].ToString()!=string.Empty && dsChild.Tables[0].Rows[i]["function_desc_chinese"].ToString()==string.Empty) nodeX.Text=dsChild.Tables[0].Rows[i]["function_desc"].ToString();
}
else
{
//throw exception because both desc and desc_chinese is not null.
}
//nodeX.Text= dsChild.Tables[0].Rows[i]["function_desc"].ToString();
nodeX.SelectedImageUrl=dsChild.Tables[0].Rows[i]["img_selected"].ToString();;
nodeX.ImageUrl =dsChild.Tables[0].Rows[i]["img"].ToString();
if (dsChild.Tables[0].Rows[i]["program"].ToString() != "")
{
nodeX.NavigateUrl="index.aspx?pageID=" +dsChild.Tables[0].Rows[i]["function_id"];
nodeX.Target="WorkFrame";
}
node.Nodes.Add(nodeX);
tSql="sp_framework_get_imis_function_child_id";
DataSet ds2=ExecuteDbConnection.fillDataset(tSql,Convert.ToInt32(dsChild.Tables[0].Rows[i]["function_id"]),userID);
if (Convert.ToBoolean(ds2.Tables[0].Rows.Count > 0))
{
int j=0;
while (j <=ds2.Tables[0].Rows.Count -1)
{
//recursive way of getting all the child in the first level
TreeNode nodeY=new TreeNode();
if (ds2.Tables[0].Rows[j]["function_desc"].ToString()!=string.Empty || ds2.Tables[0].Rows[j]["function_desc_chinese"].ToString() !=string.Empty)
{
//desc_chinese is not null
if (ds2.Tables[0].Rows[j]["function_desc"].ToString() !=string.Empty && ds2.Tables[0].Rows[j]["function_desc_chinese"].ToString()!=string.Empty) nodeY.Text=ds2.Tables[0].Rows[j]["function_desc_chinese"].ToString()==null?ds2.Tables[0].Rows[j]["function_desc"].ToString() :ds2.Tables[0].Rows[j]["function_desc"].ToString() + "<br>"+ds2.Tables[0].Rows[j]["function_desc_chinese"].ToString();
if(ds2.Tables[0].Rows[j]["function_desc"].ToString()==string.Empty && ds2.Tables[0].Rows[j]["function_desc_chinese"].ToString()!=string.Empty)nodeY.Text=ds2.Tables[0].Rows[j]["function_desc_chinese"].ToString();
if(ds2.Tables[0].Rows[j]["function_desc"].ToString()!=string.Empty && ds2.Tables[0].Rows[j]["function_desc_chinese"].ToString()==string.Empty) nodeY.Text=ds2.Tables[0].Rows[j]["function_desc"].ToString();
}
else
{
//throw exception because both desc and desc_chinese is not null.
}
//nodeY.Text=ds2.Tables[0].Rows[j]["function_desc_chinese"].ToString()==null?ds2.Tables[0].Rows[j]["function_desc"].ToString() :ds2.Tables[0].Rows[j]["function_desc"].ToString() + "<br>"+dsChild.Tables[0].Rows[j]["function_desc_chinese"].ToString() ;
nodeY.SelectedImageUrl=ds2.Tables[0].Rows[j]["img_selected"].ToString();
nodeY.ImageUrl =ds2.Tables[0].Rows[j]["img"].ToString();
if (ds2.Tables[0].Rows[j]["program"].ToString() != "")
{
nodeY.NavigateUrl="index.aspx?pageID=" +ds2.Tables[0].Rows[j]["function_id"];
nodeY.Target="WorkFrame";
}
nodeX.Nodes.Add(nodeY);
ChildNode(nodeY,Convert.ToInt32(ds2.Tables[0].Rows[j]["function_id"]));
++j;
}//end while
}//end if
++i;
}//end while
}//end if
}
catch(Exception err)
{
imis.framework.net.ApplicationLog.writeError(err,"TreeView");
}
}//end sub

Please advise, i m using dataAdater purely.|||I'm not sure. I'd advise you to repost the previous message in a new thread so that more people will take a look at it.sql