Get a list of the objects owned by a users

Hi,
My question may be stupid, but can;t figure out a way to do it "simply".
I'm taking over the admin of some SQL instance and I want to clenu up the
logins list. But before deleting anything, I need to know what objects are
owned by the user I want to delete to not brak anything.
Is there any way (not matter how) to retrieve the exclusive list of object
owned by a user ?
thanks,
Chris
________________________________________
______
It's still better that if it would have been worst, isn't it ?
C'est toujours mieux que si c'etait pire !Try this
SELECT o.name
FROM sysobjects o
INNER JOIN sysusers u
ON o.uid = u.uid
WHERE u.name = 'username'
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Chris V." <tophe_news@.hotmail.com> wrote in message
news:%23Q8GcImHFHA.2936@.TK2MSFTNGP15.phx.gbl...
> Hi,
> My question may be stupid, but can;t figure out a way to do it "simply".
> I'm taking over the admin of some SQL instance and I want to clenu up the
> logins list. But before deleting anything, I need to know what objects are
> owned by the user I want to delete to not brak anything.
> Is there any way (not matter how) to retrieve the exclusive list of object
> owned by a user ?
> thanks,
> Chris
> --
> ________________________________________
______
> It's still better that if it would have been worst, isn't it ?
> C'est toujours mieux que si c'etait pire !
>

GeoCluster/Windows 2003/SQL 200 and sp3a contd.

I thought of opening a new thread, so it does not get overlooked...
So my problem is that I can't install sp3a on a virtual SQL 2000 server, installed on a Windwos 2003 cluster.
I have preinstalled MSDTC as a clustered app (according to Q301600) so I guess the install of SQL just skips it, because it detects it as being already there. So all MSDTC resources are there. SQL 2000 installs fine on a 2-node cluster win 2003. But when
I try to apply sp3 for SQL 2000 it fails with "Setup failed to perform required operations on cluster nodes" and in the sqlsp0.log I see something like:
Error running script: repltran.sql (1)
-or-
12:29:16 Setup is installing the Microsoft Full-Text Search Engine ...
12:29:16 "C:\sql2ksp3\x86\FullText\MSSearch\Search\SearchSt p.exe" /s /a:SQLServer$INST1
the above being the last line in the log
Is it a propper sequence of installing SQL 2000 sp3 on a Windows 2003 cluster?
I just want to mention I have read and tryied all of the following KB docs: Q815431, 309398, 318672, 321063, 815430, 301600, 811168.
Thanks for your help, Jason!
That doesnt sound like a GeoCluster problem.
The only thing that GC does it split the shared storage underneath the
cluster
- in a regular cluster, you define a shared-disk resource (call it drive
X and then when you install, SQL will select from the available clustered
drives (which here will be X)
- in a cluster with GC, you define a replicated-disk resource (call it
drive X and then when you install, SQL will select from the available
clustered drives (which here will be X)
Past that, everything is how you install SQL. Literally, no other
differences.
jason
"suzana ciur" <anonymous@.discussions.microsoft.com> wrote in message
news:68283381-D8A5-41DD-AA1F-7740DD918BFB@.microsoft.com...
> I thought of opening a new thread, so it does not get overlooked...
> So my problem is that I can't install sp3a on a virtual SQL 2000 server,
installed on a Windwos 2003 cluster.
> I have preinstalled MSDTC as a clustered app (according to Q301600) so I
guess the install of SQL just skips it, because it detects it as being
already there. So all MSDTC resources are there. SQL 2000 installs fine on a
2-node cluster win 2003. But when I try to apply sp3 for SQL 2000 it fails
with "Setup failed to perform required operations on cluster nodes" and in
the sqlsp0.log I see something like:
> Error running script: repltran.sql (1)
> -or-
> 12:29:16 Setup is installing the Microsoft Full-Text Search Engine ...
> 12:29:16 "C:\sql2ksp3\x86\FullText\MSSearch\Search\SearchSt p.exe" /s
/a:SQLServer$INST1
> the above being the last line in the log
> Is it a propper sequence of installing SQL 2000 sp3 on a Windows 2003
cluster?
> I just want to mention I have read and tryied all of the following KB
docs: Q815431, 309398, 318672, 321063, 815430, 301600, 811168.
> Thanks for your help, Jason!
>

Generate SQL scripts creating a separate file per object

Hello all,

What is an easy way of scripting all the database tables and sprocs automatically to a separate file each one? I can't find this option in SQL Server Management Studio.

Or is any other way of uploading all objects to a Visual Source Safe project that does not require to copy and paste each script into a VSS sql script?

Thanks!

David

Using SQL Server 2005, right click the database, choose Tasks-> Generate Scripts. You should see a new window open with an info screen. Click Next. Now you should be at the 'Select Database' screen. Select the database you want to generate scripts for, then check the "Script all objects in the selected database" checkbox. Click Next.

Now make sure your script options are correct. If you use the default properties for SQL Server setup, then default should be fine for this as well. Click Next after making any necessary changes.

Now you are at the "Output Option" screen. Select the "Script to file" radio button, and under "Files to generate", select the "File per object" option. Make sure you set your save location and encoding, if necessary.

At this point, you can either click Next to view a summary of what you just selected, or you can click Finish to start scripting. (There is also a "Finish" button on the summary screen.)

|||

That "File per Object" option does not appear in my SQL Server Manager 2005.

I think I have installed the latest service pack.

|||

neutrino:

That "File per Object" option does not appear in my SQL Server Manager 2005.

I think I have installed the latest service pack.

I'm not sure if it has to do with the service pack. I have two computers that should both be updated to the latest SP. However, I was able to reproduce this problem with a machine running XP Pro, and the SQL Server Management Studio version was 9.00.1399.x.

The version this works on is 9.00.3042.x, and it is running on Server 2003.

Both computers only have the Development version installed, so maybe it has to do with being on a server OS?

Generate SQL Script.

Hello, Folks
Problem in SQL EM , I can't generate sql script for selected tables with
depended objects.
Any ideas ?
Jonh Smith wrote:
> Hello, Folks
> Problem in SQL EM , I can't generate sql script for selected tables
> with depended objects.
> Any ideas ?
Could you let us know what's happening when you try to generate the DDL?
David G.
|||David,
It is only show me selected table(s) no depended.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message news:%23j85MDBTFHA.2556@.TK2MSFTNGP12.phx.gbl...
> Jonh Smith wrote:
> Could you let us know what's happening when you try to generate the DDL?
> --
> David G.
>
|||I'm not sure how these objects you are extracting are related or how SQL
EM implements dependant object extraction. It may use sp_depends to
figure it out. If you run sp_depends on the objects you are extracting,
do you see the dependant objects listed?
David Gugick
Imceda Software
www.imceda.com
"Jonh Smith" <snapplefor@.hotmail.com> wrote in message
news:%23j00$GBTFHA.2124@.TK2MSFTNGP14.phx.gbl...
David,
It is only show me selected table(s) no depended.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23j85MDBTFHA.2556@.TK2MSFTNGP12.phx.gbl...
> Jonh Smith wrote:
> Could you let us know what's happening when you try to generate the
> DDL?
> --
> David G.
>
|||In EM "Display all dependant" working fine,sp_depends working too
But what happen with Script Generator ? it don't generate for me dependant
objects.
Did you try your EM on this case ?
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23IlOeNBTFHA.3620@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> I'm not sure how these objects you are extracting are related or how SQL
> EM implements dependant object extraction. It may use sp_depends to figure
> it out. If you run sp_depends on the objects you are extracting, do you
> see the dependant objects listed?
> --
> David Gugick
> Imceda Software
> www.imceda.com
> "Jonh Smith" <snapplefor@.hotmail.com> wrote in message
> news:%23j00$GBTFHA.2124@.TK2MSFTNGP14.phx.gbl...
> David,
> It is only show me selected table(s) no depended.
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:%23j85MDBTFHA.2556@.TK2MSFTNGP12.phx.gbl...
|||Jonh Smith wrote:
> In EM "Display all dependant" working fine,sp_depends working too
> But what happen with Script Generator ? it don't generate for me
> dependant objects.
> Did you try your EM on this case ?
>
I've used it in the past successfully. If I export the "titleauthor"
table from pubs and select the option to export dependant objects, it
works for me.
David Gugick
Imceda Software
www.imceda.com
|||David, you are right it is working for "pubs", but for different db is not.
What is a problem? What do you think?
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:O69APYCTFHA.3936@.TK2MSFTNGP15.phx.gbl...
> Jonh Smith wrote:
> I've used it in the past successfully. If I export the "titleauthor" table
> from pubs and select the option to export dependant objects, it works for
> me.
> --
> David Gugick
> Imceda Software
> www.imceda.com
|||Jonh Smith wrote:
> David, you are right it is working for "pubs", but for different db
> is not.
> What is a problem? What do you think?
>
I have no idea, unfortunately. Sorry.
David Gugick
Imceda Software
www.imceda.com
|||Thank you David for your response.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:eq2tq4CTFHA.2304@.tk2msftngp13.phx.gbl...
> Jonh Smith wrote:
> I have no idea, unfortunately. Sorry.
> --
> David Gugick
> Imceda Software
> www.imceda.com

generate sp using wizards - have problem

When I change some tables dbowner (design table > table and index properties > owner) I can’t generate sp using wizards (tools > wizards > database > create stored procedures wizards). What is the problem? (User dbo haven’t any problem)

Which application are you using for that ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Tharindu Dhaneenja wrote:

When I change some tables dbowner (design table > table and index properties > owner) I can’t generate sp using wizards (tools > wizards > database > create stored procedures wizards). What is the problem? (User dbo haven’t any problem)

does the user have the right to script? i mean permission wise?

|||

I am using sql server 2000. Manually I can write sp using my table

Eg : select EmpNo, EmpName from tharindu.emp (0 errors)

Sp name: tharindu.insertEmp_sp (after exec 0 errors (normal insert update sp))

My problem is I can’t create sp (insert record sp) using wizards.

|||

hi,

\which wizard are you referring to?

if it is the dot.net wizard, you need a primary key on the table

or you should be using the required data provider in the connection string

thanks

joey

|||

Run sql server enterprise manager > tools > wizards > database > create stored procedures wizards.

|||

could it be permission problem.

check if you have rights to create proc in the Db

|||

Yes I have rights to create proc in the Db, for example after login SQL query analyzer using my user (“Tharindu”) I can create, delete all sp and tables. But only problem is I can’t create sp using wizards.

This error prompt,

http://tharindu.50webs.com/err/error.htm

|||

Finally I see my error jpg doesn’t open.

Still can not find good solution I think sql server wizards support only user “dbo”

|||

More information about this matter read this, Generate sp using wizards display flowing Error…(Error 21776)

generate sp using wizards - have problem

When I change some tables dbowner (design table > table and index properties > owner) I can’t generate sp using wizards (tools > wizards > database > create stored procedures wizards). What is the problem? (User dbo haven’t any problem)

Which application are you using for that ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Tharindu Dhaneenja wrote:

When I change some tables dbowner (design table > table and index properties > owner) I can’t generate sp using wizards (tools > wizards > database > create stored procedures wizards). What is the problem? (User dbo haven’t any problem)

does the user have the right to script? i mean permission wise?

|||

I am using sql server 2000. Manually I can write sp using my table

Eg : select EmpNo, EmpName from tharindu.emp (0 errors)

Sp name: tharindu.insertEmp_sp (after exec 0 errors (normal insert update sp))

My problem is I can’t create sp (insert record sp) using wizards.

|||

hi,

\which wizard are you referring to?

if it is the dot.net wizard, you need a primary key on the table

or you should be using the required data provider in the connection string

thanks

joey

|||

Run sql server enterprise manager > tools > wizards > database > create stored procedures wizards.

|||

could it be permission problem.

check if you have rights to create proc in the Db

|||

Yes I have rights to create proc in the Db, for example after login SQL query analyzer using my user (“Tharindu”) I can create, delete all sp and tables. But only problem is I can’t create sp using wizards.

This error prompt,

http://tharindu.50webs.com/err/error.htm

|||

Finally I see my error jpg doesn’t open.

Still can not find good solution I think sql server wizards support only user “dbo”

|||

More information about this matter read this, Generate sp using wizards display flowing Error…(Error 21776)