Monday, March 19, 2012
how to locate the exact user name who owns a db object
In sql 2000, how can you find out the exact user name who owns/created a db
object? They are generally recorded as 'dbo' in the sysobjects table, how
can we find out the specific user login name behind the 'dbo' entry?
many thanks,
JJThe 'dbo' user in a database is a special user that maps to the login
who owns the database (usually the login who initially created the
database). The system stored proc "exec sp_helpdb '<dbname>'" will tell
you who the owner of the database is and that login will be the one that
maps to the dbo user in the database.
Other, less Microsoft approved, ways of finding this info would be:
* "select * from master.dbo.sysdatabases" (the sid column is the
login that owns a given database, ie. that maps to the dbo user in
that database, and you can join that to master.dbo.syslogins to
get more info about that login)
* "select * from <dbname>.dbo.sysusers" (the dbo user in the
database is always uid 1; the sid column in that table will map
back to the master.dbo.syslogins table to tell you who owns the
database...unless the database user is an orphaned user (the sid
doesn't map back to any row in master.dbo.syslogins) which often
happens when you restore DBs from other servers because the other
server has different data in its master.dbo.syslogins table; this
can be corrected with sp_change_users_login)
* You could use the SUSER_SNAME() function with the sysusers table
like this:
select SUSER_SNAME(sid) from <dbname>.dbo.sysusers where uid = 1
Bear in mind, not every object in a DB has to be owned by the dbo user,
although this is quite normal. To find out which DB user owns a
specific object in the database you can use the OBJECTPROPERTY()
function like this:
select USER_NAME(OBJECTPROPERTY(OBJECT_ID('MyTa
ble'),'OwnerId'))
However, sp_helpdb is probably the easiest. Hope this helps.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
JJ Wang wrote:
>hi all,
>In sql 2000, how can you find out the exact user name who owns/created a db
>object? They are generally recorded as 'dbo' in the sysobjects table, how
>can we find out the specific user login name behind the 'dbo' entry?
>many thanks,
>JJ
>|||JJ,
I think that would need to be accomplished by using a source control system
like Visual SourceSafe as many logins may have the ability to have dbo be
the owner of an object.
HTH
Jerry
"JJ Wang" <JJWang@.discussions.microsoft.com> wrote in message
news:73FC551C-3600-4E8F-8B37-B2BB9E890879@.microsoft.com...
> hi all,
> In sql 2000, how can you find out the exact user name who owns/created a
> db
> object? They are generally recorded as 'dbo' in the sysobjects table, how
> can we find out the specific user login name behind the 'dbo' entry?
> many thanks,
> JJ|||Hi,
the only wat i think for that is too tell your developers/DBAs to use full
name while creating db / objects .
Regards
How to locate the database folder in sql 2005
We want to locate the database folder/files (where the databases are stored) like SQL Server Management Studio UI does,
when you click on attach database / Add.
The question is how to retrieve this folder/files programmatically (C# or VB, SMO?).
For example we want our application client to connect remotely to an SQL Server and attach a new database, using the folder/files obtained from the retrieved method.
If you run Profiler while you use SSMS you will see the code that it runs to populate the dialogs you open.
The code is t-sql so you will have to write your own C to do the same thing
|||We tried profiler but we didn't find something to help us.
Thank you Anyway.
|||Perhaps this will point you in the right direction:
SELECT physical_name
FROM sys.master_files
WHERE name = 'Northwind'
A little string manipulation and you should be fine.
|||Thank you but this is for a database already attached.|||So..., if the database is not attached, then you can put it just about anywhere you wish.
There are 'default' locations, and there are the locations you decide to use. If you are not concerned about currently attached databases, and their locations, then what is the issue?
All servers will have at least master, tempdb, model, and msdb databases. If you can find where they are stored, then you can store your database there too.
|||I don't know the folder and the name of the database. That is why I want to locate it.|||Remove the WHERE clause and get a list of all databases attached to the server, and their locations.
SELECT
name,
physical_name
FROM sys.master_files
Now... how to determine which one of these listed databases is the database you are seeking since you don't have a database name... You will be very, very, very lucky if there is nothing more than master, tempdb, model, msdb, -AND only the one database you seek.
Did I say, really, really, lucky...
Perhaps adding the following WHERE clause:
WHERE name = db_name()
But that may not always work, the (file) 'name' could be different from the logical database name; an improved query is:
SELECT
db_name( database_id )
name,
physical_name
FROM sys.master_files
WHERE database_id = db_id()
This last query should give you the logical database name, the filename, and the filepath.
|||
Verry verry sorry..
I don't know the folder and the name of the database. The database is NOT attached. That is why I want to locate it.
|||GeoB wrote:
Verry verry sorry..
I don't know the folder and the name of the database. The database is NOT attached. That is why I want to locate it.
That would have been GOOD information to have earlier.
Since there is no certainly as to how a database file is named (it does not have to have a [mdf] suffix), you may have quite a problem there.
|||When use SQL Server Management Studio UI you do know where the databases are stored ok?
I want to make something like that: http://www.kenix.eu/georgebakogiannis/LocateDatabaseFiles.png
|||
If you have a default instance on the box, then the default data and log locations are held in the registry at these locations:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\DefaultData
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\DefaultLog
However there's no guarantee that all database and log files will be found at these locations.
Chris
How to locate the database folder in sql 2005
We want to locate the database folder/files (where the databases are stored) like SQL Server Management Studio UI does,
when you click on attach database / Add.
The question is how to retrieve this folder/files programmatically (C# or VB, SMO?).
For example we want our application client to connect remotely to an SQL Server and attach a new database, using the folder/files obtained from the retrieved method.
If you run Profiler while you use SSMS you will see the code that it runs to populate the dialogs you open.
The code is t-sql so you will have to write your own C to do the same thing
|||We tried profiler but we didn't find something to help us.
Thank you Anyway.
|||Perhaps this will point you in the right direction:
SELECT physical_name
FROM sys.master_files
WHERE name = 'Northwind'
A little string manipulation and you should be fine.
|||Thank you but this is for a database already attached.|||So..., if the database is not attached, then you can put it just about anywhere you wish.
There are 'default' locations, and there are the locations you decide to use. If you are not concerned about currently attached databases, and their locations, then what is the issue?
All servers will have at least master, tempdb, model, and msdb databases. If you can find where they are stored, then you can store your database there too.
|||I don't know the folder and the name of the database. That is why I want to locate it.|||Remove the WHERE clause and get a list of all databases attached to the server, and their locations.
SELECT
name,
physical_name
FROM sys.master_files
Now... how to determine which one of these listed databases is the database you are seeking since you don't have a database name... You will be very, very, very lucky if there is nothing more than master, tempdb, model, msdb, -AND only the one database you seek.
Did I say, really, really, lucky...
Perhaps adding the following WHERE clause:
WHERE name = db_name()
But that may not always work, the (file) 'name' could be different from the logical database name; an improved query is:
SELECT
db_name( database_id )
name,
physical_name
FROM sys.master_files
WHERE database_id = db_id()
This last query should give you the logical database name, the filename, and the filepath.
|||
Verry verry sorry..
I don't know the folder and the name of the database. The database is NOT attached. That is why I want to locate it.
|||GeoB wrote:
Verry verry sorry..
I don't know the folder and the name of the database. The database is NOT attached. That is why I want to locate it.
That would have been GOOD information to have earlier.
Since there is no certainly as to how a database file is named (it does not have to have a [mdf] suffix), you may have quite a problem there.
|||When use SQL Server Management Studio UI you do know where the databases are stored ok?
I want to make something like that: http://www.kenix.eu/georgebakogiannis/LocateDatabaseFiles.png
|||
If you have a default instance on the box, then the default data and log locations are held in the registry at these locations:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\DefaultData
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\DefaultLog
However there's no guarantee that all database and log files will be found at these locations.
Chris
How to locate last occurrence of character in string
character in a string.
Is there similar function to find last occurrence of character in
string.
I could use reverse() or some script to find that, but it will be
inefficient.
Could anybody suggest me efficient solution for this.
Thanks and Regards,
AvinAvi, please do not multipost as I have already answered in.programming
group.
"Avi" <avimandale@.gmail.com> wrote in message
news:271710bf-a8dd-4a82-8bc2-076b920d6919@.k39g2000hsf.googlegroups.com...
> charindex() function lets you find first occurrence of a particular
> character in a string.
> Is there similar function to find last occurrence of character in
> string.
> I could use reverse() or some script to find that, but it will be
> inefficient.
> Could anybody suggest me efficient solution for this.
> Thanks and Regards,
> Avin|||On Jan 28, 5:25=A0pm, Avi <avimand...@.gmail.com> wrote:
> charindex() function lets you find first occurrence of a particular
> character in a string.
> Is there similar function to find last occurrence of character in
> string.
> I could use reverse() or some script to find that, but it will be
> inefficient.
> Could anybody suggest me efficient solution for this.
> Thanks and Regards,
> Avin
Hi Avin,
declare @.string1 varchar(50), @.string2 varchar(50)
select @.string1 =3D 'abc', @.string2=3D 'oooabcooabco'
select (datalength(@.string2)-1)-
charindex(reverse(@.string1),reverse(@.string2))
HTH
how to locate a table that contains a specific trigger?
the following tsql
select DISTINCT OBJECT_NAME([id]) FROM sysdepends
WHERE OBJECT_NAME([depid]) = 'tbl_CompanyChangeHistory'
displays a list of UDFs and a trigger that tbl_CompanyChangeHistory is
dependent upon. I need to locate the table that contains this specific
trigger.
I tried
select DISTINCT OBJECT_NAME([id]) FROM sysdepends
WHERE OBJECT_NAME([depid]) like 't_ChgHistory_Write%'
but this did not yield anything (empty).
How can I locate the table which contains this trigger?
Thanks,
RichReverse id and depid:
select DISTINCT OBJECT_NAME([depid]) FROM sysdepends
WHERE OBJECT_NAME([id]) like 't_ChgHistory_Write%' --trigger name?
"Rich" wrote:
> Hello,
> the following tsql
> select DISTINCT OBJECT_NAME([id]) FROM sysdepends
> WHERE OBJECT_NAME([depid]) = 'tbl_CompanyChangeHistory'
> displays a list of UDFs and a trigger that tbl_CompanyChangeHistory is
> dependent upon. I need to locate the table that contains this specific
> trigger.
> I tried
> select DISTINCT OBJECT_NAME([id]) FROM sysdepends
> WHERE OBJECT_NAME([depid]) like 't_ChgHistory_Write%'
> but this did not yield anything (empty).
> How can I locate the table which contains this trigger?
> Thanks,
> Rich
>|||Thanks for your reply. I had the [id] and [depid] columns switched around
which was incorrect. But your solution retrieved the dependent table and a
dependent UDF (still good stuff).
Here is something else I tried that actually retrieve the table I was
looking for
select OBJECT_NAME([parent_obj]) FROM sysobjects
WHERE [id] = 1467920351
The [id] here is the [id] of the trigger which I retrieved like this:
select * from sysobjects where xtype ='tr'
and name = 't_CompaniesWrite'
"Mark Williams" wrote:
> Reverse id and depid:
> select DISTINCT OBJECT_NAME([depid]) FROM sysdepends
> WHERE OBJECT_NAME([id]) like 't_ChgHistory_Write%' --trigger name?
>
> --
>
> "Rich" wrote:
>|||Rich
I'd not rely on sysdepends table ,instead take a look at Vyas's examle
CREATE PROCEDURE sp_FindObject
@.SearchString varchar (255)
AS
SET nocount ON
DECLARE @.Name varchar(255)
DECLARE @.Text nvarchar(4000)
CREATE TABLE #Objs
( ObjName varchar (255))
DECLARE Obj CURSOR
FOR
SELECT [NAME]
FROM
(
SELECT [NAME],[TEXT] FROM sysobjects so, syscomments sc
WHERE (so.xtype ='P' )
AND so.id = sc.id
UNION ALL
SELECT [NAME],[TEXT] FROM sysobjects so, syscomments sc
WHERE (so.xtype ='V' )
AND so.id = sc.id
UNION ALL
SELECT [NAME],[TEXT] FROM sysobjects so, syscomments sc
WHERE (so.xtype ='TR' )
AND so.id = sc.id
) AS Der WHERE [TEXT] LIKE @.SearchString
OPEN Obj
FETCH Next FROM Obj INTO @.Name
WHILE @.@.FETCH_STATUS=0
BEGIN
INSERT INTO #Objs VALUES (@.Name)
FETCH Next FROM Obj INTO @.Name
END
CLOSE Obj
DEALLOCATE Obj
SELECT objname FROM #Objs GROUP BY objname
DROP TABLE #Objs
GO
EXEC sp_FindObject '%HOST_ID()%'
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:BF61EDA5-4130-40E8-8FA5-2DF3938B0E9C@.microsoft.com...
> Thanks for your reply. I had the [id] and [depid] columns switched around
> which was incorrect. But your solution retrieved the dependent table and
> a
> dependent UDF (still good stuff).
> Here is something else I tried that actually retrieve the table I was
> looking for
> select OBJECT_NAME([parent_obj]) FROM sysobjects
> WHERE [id] = 1467920351
> The [id] here is the [id] of the trigger which I retrieved like this:
> select * from sysobjects where xtype ='tr'
> and name = 't_CompaniesWrite'
> "Mark Williams" wrote:
>