Is there a SQL Server equivalent for Oracle synonyms?

I have a SQL Server database which has one user (UserA) which owns some tables. I've added an additional user (UserB) to the database such that it has access to the tables owned by UserA. What is happening is that when I log on as UserB I have to fully qualify table names and fields in my SQL statements when I deal with tables owned by UserA. Is there a way make the tables accessible without specifying the owner? In Oracle you could create a public synonym for the table eg. <table_name>. Wherever that synonym is referenced the DBMS would know thats its refering to UserA.<table_name>. Is such functionality available in SQL Server? Thanks.I found out that if the tables belong to 'dbo' they can be accessed without the fully qualified names.|||Specifying schema owner is a good practice. It releaves the optimizer to do it EVERY time you choose to be lazy about it.

is there a quick way to get a list of roles a user is a member of?

What I'm looking for is a list of roles a particular user is a member
of.

the closest I've found so far is sp_helprolemember without any
arguements. but this gives me all the roles and all the users. I want
this same list filtered on a specific user.
something like sp_? 'user'Hi

Maybe:

CREATE TABLE #UserRoles (
DbRole sysname,
MemberName sysname,
MemberSID varbinary(85) )

INSERT INTO #UserRoles (
DbRole ,
MemberName ,
MemberSID )
EXEC sp_helprolemember

SELECT * FROM #UserRoles WHERE MemberName = 'dbo'

John

"IndianaJonesWB" <jryan@.lcra.org> wrote in message
news:1103735940.367536.213730@.c13g2000cwb.googlegr oups.com...
> What I'm looking for is a list of roles a particular user is a member
> of.
> the closest I've found so far is sp_helprolemember without any
> arguements. but this gives me all the roles and all the users. I want
> this same list filtered on a specific user.
> something like sp_? 'user'|||Perfectamundo! Thanks

Is there a possibility to be hacked

We have connection with sql server 2000 with front end visual foxpro 9 in
which we gave rights to user and install front end exe in client computer is
there any possibility that outsider can hack our server. how it possiblity
pls tellTHere is *ALWAYS* a possibilitiy that you can be hacked.
with vfp - you should do some encrypting/branding of your code; a guy named
Leonid has made a pretty good tool that he has indicated might be freeware w
hen
he releases.
Anyway -- the code can be dcompiled & someone can see your connection string
.
someone can packet sniff & watch teh data going back & forth.
There are exploits for sql2000 to gain access
If your firewall is up to date, and by "outsider" you mean not on your local
network; the chances are probably fairly low.
On Wed, 25 Jan 2006 17:51:49 +0530, "Dinesh" <dinesh_pl2001@.yahoo.co.in> wro
te:

>We have connection with sql server 2000 with front end visual foxpro 9 in
>which we gave rights to user and install front end exe in client computer i
s
>there any possibility that outsider can hack our server. how it possiblity
>pls tell
>
-- AntiSpam/harvest --
Remove X's to send email to me.|||can u tell the path of that pretty good tool that he has indicated might be
freeware when he releases.
"Josh Assing" <XjoshX@.jAssing.com> wrote in message
news:ee7ft1pa94b01kbmd3o207c5if8nqqc0fi@.
4ax.com...
> THere is *ALWAYS* a possibilitiy that you can be hacked.
> with vfp - you should do some encrypting/branding of your code; a guy
named
> Leonid has made a pretty good tool that he has indicated might be freeware
when
> he releases.
> Anyway -- the code can be dcompiled & someone can see your connection
string.
> someone can packet sniff & watch teh data going back & forth.
> There are exploits for sql2000 to gain access
> If your firewall is up to date, and by "outsider" you mean not on your
local
> network; the chances are probably fairly low.
> On Wed, 25 Jan 2006 17:51:49 +0530, "Dinesh" <dinesh_pl2001@.yahoo.co.in>
wrote:
>
is[vbcol=seagreen]
possiblity[vbcol=seagreen]
>
> -- AntiSpam/harvest --
> Remove X's to send email to me.

Is there a log of activity as compared to the Tran log?

I am trying to identify if I have unwanted guests gaining access to my
system or it's a run away query by a user on my network. I am thinking of
recording SPIDs as one way.
Via Spotlight I can see that I'm having beyond normal usage. Granted I have
a new PHB who likes to run queries off his laptop to show off to other
managers. Real scary when he has a little understanding of the data.
The box running Spotlight is WAY under powered, but can it keep that logging
data instead of the server itself?You can create your own log with profiler - it's a good tool
to track down performance issues in a database. If you use
it and performance is a concern, don't trace to the database
or from the database. Trace from a client and if tracing to
a file, have it go on that client.
Or use a server side trace. But you'd probably want to play
around with profiler from a client first.
-Sue
On Fri, 23 Sep 2005 08:30:52 -0500, "Stephen Russell"
<srussell@.transactiongraphics.com> wrote:

>I am trying to identify if I have unwanted guests gaining access to my
>system or it's a run away query by a user on my network. I am thinking of
>recording SPIDs as one way.
>Via Spotlight I can see that I'm having beyond normal usage. Granted I hav
e
>a new PHB who likes to run queries off his laptop to show off to other
>managers. Real scary when he has a little understanding of the data.
>The box running Spotlight is WAY under powered, but can it keep that loggin
g
>data instead of the server itself?
>|||2 things here really
1. Tracking "unwanted guests" gaining access to your SQL Server is easily
tracked with no perf hit by setting "Audit level" to "All". This is strictly
for login attempts and can be se via EM. Right click on your server, select
properties and click on the security tab. This puts an entry in the logs
everytime a login attempt is successfully or failed. At the very minimum,
you should log failures.
2. Runaway queries are a little harder to do with SQL Server 2000. First,
you need to define what you mean by runaway queries. CPU consumption?
Memory? Length of query? SQL Profiler and a bunch of 3rd party tools from
folks like Quest, BMC, etc... let's you capture the appropriate data to
"look at" so you can figure out who's got a runaway query. Depending on what
you use, you may have to do extra work. For example, if you capture trace
info with Profiler or SQL Trace, you'll need a way to track the data and
raise alerts based on thresholds that you set. Life gets a LOT better in
2005 with the default "reports" (it's a cool dashboard really) but in 2000,
there's a little bit of work needed to get you there. :-)
Btw, one thing you might want to consider is the Query Governor cost limit
option. It's not the most "precise" tool you can have since it works based
on estimates but it's a pretty good start with minimal effort. Look it up in
BOL. It's well documented.
joe.
"Stephen Russell" <srussell@.transactiongraphics.com> wrote in message
news:u5%23LlMEwFHA.664@.tk2msftngp13.phx.gbl...
>I am trying to identify if I have unwanted guests gaining access to my
>system or it's a run away query by a user on my network. I am thinking of
>recording SPIDs as one way.
> Via Spotlight I can see that I'm having beyond normal usage. Granted I
> have a new PHB who likes to run queries off his laptop to show off to
> other managers. Real scary when he has a little understanding of the
> data.
> The box running Spotlight is WAY under powered, but can it keep that
> logging data instead of the server itself?
>
>

Is there a free Graphical User Interface for MSDE?

I asked a similar question moments ago. I wanted to clarify because I didn't use the correct terms..
I have a purchased product that contains a MSDE database. I was hopin
to access the database with a MSDE front-end, however I don't have
separate licensed copy of SQL Server. Is there a way I can obtain a fre
copy of the user interface for a MSDE DB?
Thank you
AFGTry this:
http://www.whitebearconsulting.com/Utilities.htm
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"AFG" <anonymous@.discussions.microsoft.com> wrote in message
news:198C89E8-A24F-4935-A347-6D2AA5C2786E@.microsoft.com...
> I asked a similar question moments ago. I wanted to clarify because I
didn't use the correct terms...
> I have a purchased product that contains a MSDE database. I was hoping
> to access the database with a MSDE front-end, however I don't have a
> separate licensed copy of SQL Server. Is there a way I can obtain a free
> copy of the user interface for a MSDE DB?
> Thank you,
> AFG|||No, you can't get SQL Server client tools for free. Several alternatives,
however: http://www.aspfaq.com/2442
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"AFG" <anonymous@.discussions.microsoft.com> wrote in message
news:198C89E8-A24F-4935-A347-6D2AA5C2786E@.microsoft.com...
>I asked a similar question moments ago. I wanted to clarify because I
>didn't use the correct terms...
> I have a purchased product that contains a MSDE database. I was hoping
> to access the database with a MSDE front-end, however I don't have a
> separate licensed copy of SQL Server. Is there a way I can obtain a free
> copy of the user interface for a MSDE DB?
> Thank you,
> AFG

Is there a free Graphical User Interface for MSDE?

I asked a similar question moments ago. I wanted to clarify because I didn't use the correct terms...
I have a purchased product that contains a MSDE database. I was hoping
to access the database with a MSDE front-end, however I don't have a
separate licensed copy of SQL Server. Is there a way I can obtain a free
copy of the user interface for a MSDE DB?
Thank you,
AFG
Try this:
http://www.whitebearconsulting.com/Utilities.htm
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"AFG" <anonymous@.discussions.microsoft.com> wrote in message
news:198C89E8-A24F-4935-A347-6D2AA5C2786E@.microsoft.com...
> I asked a similar question moments ago. I wanted to clarify because I
didn't use the correct terms...
> I have a purchased product that contains a MSDE database. I was hoping
> to access the database with a MSDE front-end, however I don't have a
> separate licensed copy of SQL Server. Is there a way I can obtain a free
> copy of the user interface for a MSDE DB?
> Thank you,
> AFG
|||No, you can't get SQL Server client tools for free. Several alternatives,
however: http://www.aspfaq.com/2442
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"AFG" <anonymous@.discussions.microsoft.com> wrote in message
news:198C89E8-A24F-4935-A347-6D2AA5C2786E@.microsoft.com...
>I asked a similar question moments ago. I wanted to clarify because I
>didn't use the correct terms...
> I have a purchased product that contains a MSDE database. I was hoping
> to access the database with a MSDE front-end, however I don't have a
> separate licensed copy of SQL Server. Is there a way I can obtain a free
> copy of the user interface for a MSDE DB?
> Thank you,
> AFG

Is there a free Graphical User Interface for MSDE?

I asked a similar question moments ago. I wanted to clarify because I didn't
use the correct terms...
I have a purchased product that contains a MSDE database. I was hoping
to access the database with a MSDE front-end, however I don't have a
separate licensed copy of SQL Server. Is there a way I can obtain a free
copy of the user interface for a MSDE DB?
Thank you,
AFGTry this:
http://www.whitebearconsulting.com/Utilities.htm
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"AFG" <anonymous@.discussions.microsoft.com> wrote in message
news:198C89E8-A24F-4935-A347-6D2AA5C2786E@.microsoft.com...
> I asked a similar question moments ago. I wanted to clarify because I
didn't use the correct terms...
> I have a purchased product that contains a MSDE database. I was hoping
> to access the database with a MSDE front-end, however I don't have a
> separate licensed copy of SQL Server. Is there a way I can obtain a free
> copy of the user interface for a MSDE DB?
> Thank you,
> AFG|||No, you can't get SQL Server client tools for free. Several alternatives,
however: http://www.aspfaq.com/2442
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"AFG" <anonymous@.discussions.microsoft.com> wrote in message
news:198C89E8-A24F-4935-A347-6D2AA5C2786E@.microsoft.com...
>I asked a similar question moments ago. I wanted to clarify because I
>didn't use the correct terms...
> I have a purchased product that contains a MSDE database. I was hoping
> to access the database with a MSDE front-end, however I don't have a
> separate licensed copy of SQL Server. Is there a way I can obtain a free
> copy of the user interface for a MSDE DB?
> Thank you,
> AFG

Is there a Forum for those of us who study from SS 2005 Implementation and Maintenance (Exam 70-

Is there a Forum for those of us who study from SS 2005 Implementation and Maintenance (Exam 70-431), a la Wrox P2P user forum?

Thanks.

I don't think there is any particular forums for all the microsoft certified exams but this is the right place for you to gain knowlege wrt sql server and pass the exam ! if yo wish to learn sql 2005 practically you can make use of Microsofts Virtual labs refer the link,
http://msdn2.microsoft.com/en-us/virtuallabs/aa740409.aspx

Thanxx
Deepak
sql

is there a collection of all database objects

i want to write a tool to make scripting of databases easy. the user will select the server and dababase, then they will select what they want to script: eg tables, sprocs, permissions, scheemas etc etc. currently i have a load of check boxes , one for each type of object in the db ( one for tables, one for sprocs etc). this is very long winded,and if there was a collection of the db objects i could call into a list box it would be a lot tidier and easier.

There are a number of collections within the Database object in SMO which you can use, such as Tables, Triggers, StoredProcedures, Users and Views. Each of these is a collection of the named objects within the database. (At the Server level of course, there's a Databases collection which will get you to each Database object on the server.)

Hope that helps.

sql

is there a "user defined row type" ability in MSSQL server?

Hi all. We have a mix of informix and mssql server and I want to know if something we do in informix has an analogous feature in MSSQL. We can define a "row type" in informix, like so:

create row type name_1(fname char(20),lname char(20));

The when we create any table that includes a first and last name, we do so using this row type like so:

create table sometable(name name_1, some column,...etc)

This allows us to set a standard for certain common fields and avoids having different developers build the same type of field in more than one way, different lengths, etc.

Is there a similar function in MSSQL server?No. SQL Server 2000 supports user defined functions, but not user defined data types.

The best way that I've found to do this is to create a table with just an arbitrary PK (usually an identity column) and the columns needed to implement the new datatype. Then have your developers add an FK column wherever they would use the user defined datatype. It isn't quite as simple to code this way, but relationally it is much more sound so the two seem to balance each other.

-PatP|||I do not know about creating row types in SQL server 7, but you can surely create your own data type i.e., user defined datatypes, using sp_addtype command.
for example
EXEC sp_addtype telephone, 'varchar(24)', 'NOT NULL'

The datatype telephone has been created. You can use it in any table

CREATE table customers
(
customer_name varchar(30),
telephone_number telephone
)

Roshmi Choudhury|||There's also a sql_variant data type, and a table variable. Not sure if there's something ugly to come up with using either one or worse: both.|||The user defined datatypes created by sp_addtype are scalars (single columns). So are sql_variant columns (and they are really messy too). What WiccaChic seems to want is the ability to define multiple columns that are treated as one (like a Pascal record or a C struct).

A table variable does exactly what they want, but you can't put the darned thing (as a whole anyway) into another table!

The best answer that I've found for doing this so far is to create a stand alone table with the appropriate columns and a surrogate primary key, then make a foreign key reference whereever I want to use it in another table.

-PatP

Is restore or Detach/Attach better?

Hello, everyone:

I want to move a database instance (about 50 user databases) to another server, both are running SQL Server 2000. Which method is better, retore from backup files or detach/attach? Some papers said restore amybe cause incorrect login and password transfer. Is it true?

Thanks

ZYTAll databases store the SID (Security Identifier) of the logins that have permissions on that database. When you restore or attach a database to a new instance, these users may become orphaned, if there is no login with the corresponding SID. When you create the SQL Authenticated logins on the new server, make sure you specify the SID that they should have. Windows logins have their SIDs specified by the Domain Controllers, and need not be specified in the create login statement.|||MCrowley:

Thanks for the reply. Can DTS transfer login with SID? What I am concerning is to transfer login from old server to new one.

Thanks

ZY

All databases store the SID (Security Identifier) of the logins that have permissions on that database. When you restore or attach a database to a new instance, these users may become orphaned, if there is no login with the corresponding SID. When you create the SQL Authenticated logins on the new server, make sure you specify the SID that they should have. Windows logins have their SIDs specified by the Domain Controllers, and need not be specified in the create login statement.|||Google "sp_help_revlogin" for some help to transfer sql accounts|||when the db is restored run (from the restored db)

If you do not bring logins over and create them manually

-- to view broken users
sp_change_users_login 'report' -- will give you list of "broken" users.

you then run sp_change_users_login auto_fix, 'username' for each user:

I just use this little sql script after restoring a db (make db current):

set nocount on
declare @.v_dbuser varchar(255)
declare @.sql nvarchar(255)
declare c1 cursor for
select a.name from sysusers a, master..syslogins b
where a.name = b.name
open c1
fetch c1 into @.v_dbuser
while (@.@.FETCH_STATUS <> -1)
BEGIN
print 'Fixing User ' + @.v_dbuser
set @.sql = 'sp_change_users_login ''auto_fix'',' + @.v_dbuser
exec sp_executesql @.sql
fetch c1 into @.v_dbuser
END
CLOSE C1
DEALLOCATE C1|||Nice little script! I have always done that manually...now next time we create or sync a dev database, I'll whip out your little script and be the instant envy of my peers!

Yeah, I know...I need a new set of peers.

One could probably also just add a "check every database" loop outside of this one and handle all the databases on a new server at once...though I suppose that is a pretty infrequent occurrence and probably not a justifiable expenditure of effort relative to the creation of a script to do it.

Is RDA Appropriate?

The scenario is this:

User on device enters data. Eventually sends it up to Server.

On Server, two possible things can happen to the data.

1) More data gets created (specifically a child table gets filled in)

2) Original data gets changed in ways that are probably pretty minor (error correction, etc).

The changes ultimately need to go back to the Device. More specifically, the user needs to decide what data he wants to be able to view on the device still.

RDA is intended as a one way synchronization though.

Is this a scenario I can handle programatically through RDA or is Merge Replication more appropriate?

In RDA the client side changes are tracked and the changes are sent to the Server. The server side changes are pulled down to the client table by table. All existing tables on the client are deleted and new tables are created. This is how bi-directional sync happens in RDA. In Merge incremental changes are replicated from the client to the server and vice-versa.

In your case you can use either RDA or Merge. Both are viable

Regards

Ambrish

IS Or type check on bit field

I have a table that has a bit field called PHI, the user will specift true
(1) or false (0) in the application, but I want to if it is true return all
1 and 0 marked fields and if false only the 0 bit marked fields. How would I
write a query to include both if true and only false ones if false? thanks!
my wear clause right now looks like this (PHI = @.PHI), which of course only
includes one or the other.PHI <= @.PHI
Jacco Schalkwijk
SQL Server MVP
"Brian Henry" <nospam@.nospam.com> wrote in message
news:%234zfS2qkFHA.3316@.TK2MSFTNGP14.phx.gbl...
>I have a table that has a bit field called PHI, the user will specift true
>(1) or false (0) in the application, but I want to if it is true return all
>1 and 0 marked fields and if false only the 0 bit marked fields. How would
>I write a query to include both if true and only false ones if false?
>thanks!
> my wear clause right now looks like this (PHI = @.PHI), which of course
> only includes one or the other.
>|||oh that was so simple... was thinking of booleans where you cant really say
that in some languages...
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23A2LJ7qkFHA.4000@.TK2MSFTNGP12.phx.gbl...
> PHI <= @.PHI
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Brian Henry" <nospam@.nospam.com> wrote in message
> news:%234zfS2qkFHA.3316@.TK2MSFTNGP14.phx.gbl...
>|||The bit datatype is not a Boolean datatype. It is just an integer class data
type with values
restricted to 0, 1 and NULL. The only confusing (and IMO bad) thing about th
is bit datatype is
below:
DECLARE @.b bit
SET @.b = 27
SELECT @.b --returns 1
All non-0 values are converted to 1. IMO, the SET should return a datatype c
onversion error instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Brian Henry" <nospam@.nospam.com> wrote in message news:ejy2HErkFHA.3256@.TK2MSFTNGP12.phx.g
bl...
> oh that was so simple... was thinking of booleans where you cant really sa
y that in some
> languages...
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wro
te in message
> news:%23A2LJ7qkFHA.4000@.TK2MSFTNGP12.phx.gbl...
>