Copy Permissions

Hi,
I've copied tables and views from one SQL Server to another, but the
permissions for each user/role are not copied.
How come.
Thanks,
B.Bart
--
EXEC databasename..sp_helpuser 'username'
SELECT OBJECT_NAME(id),*
FROM databasename..syspermissions
WHERE grantee=USER_ID('username')
If you use BACKUP/RESTORE command to copy your tables/views so please
search on internet for "sp_help_revlogin" two stored procedures provided by
MS to copy logins beween SQL Servers
"Bart Steur" <solnews@.xs4all.nl> wrote in message
news:OtwLA6myFHA.460@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've copied tables and views from one SQL Server to another, but the
> permissions for each user/role are not copied.
> How come.
> Thanks,
> B.
>

Copy Permissions

Hi,
I've copied tables and views from one SQL Server to another, but the
permissions for each user/role are not copied.
How come.
Thanks,
B.
Bart
EXEC databasename..sp_helpuser 'username'
SELECT OBJECT_NAME(id),*
FROM databasename..syspermissions
WHERE grantee=USER_ID('username')
If you use BACKUP/RESTORE command to copy your tables/views so please
search on internet for "sp_help_revlogin" two stored procedures provided by
MS to copy logins beween SQL Servers
"Bart Steur" <solnews@.xs4all.nl> wrote in message
news:OtwLA6myFHA.460@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've copied tables and views from one SQL Server to another, but the
> permissions for each user/role are not copied.
> How come.
> Thanks,
> B.
>

Copy paste from Excel - should this work?

Hi,
Now I'm worried as I showed this bodge to a colleague and he is using
it a lot! I found that you can copy paste directly from Excel into the
Enterprise Manager GUI (we're on v8.0, sql server 2000).
The way I do it is say you had some Excel data in cols B-D, highlight A
as well (leaving this column blank), click the arrow on the new row at
the bottom of your table (a table with 3 columns matching B-D) in
Enterprise Manager and voila a paste option appears in the right hand
mouse button menu.
Why? What is this blank column doing? Is it causing any harm
internally to the db to insert nulls (or empty excel strings more
correctly?) in this column?
Any help / warnings much appreciated!
CT
Hi
I don't seem to be able to get this to happen, maybe I am missing something!
What version of SQL Server are you using and are your client tools at the
same version?
If you need to regularly import files into SQL Server you may want to look
at using DTS to do this, you can have jobs that regularly check any files
dropped into a directory and upload them check out
http://www.sqldts.com/default.aspx for lots of information on how to do this.
John
"cheesey_toastie" wrote:

> Hi,
> Now I'm worried as I showed this bodge to a colleague and he is using
> it a lot! I found that you can copy paste directly from Excel into the
> Enterprise Manager GUI (we're on v8.0, sql server 2000).
> The way I do it is say you had some Excel data in cols B-D, highlight A
> as well (leaving this column blank), click the arrow on the new row at
> the bottom of your table (a table with 3 columns matching B-D) in
> Enterprise Manager and voila a paste option appears in the right hand
> mouse button menu.
> Why? What is this blank column doing? Is it causing any harm
> internally to the db to insert nulls (or empty excel strings more
> correctly?) in this column?
> Any help / warnings much appreciated!
> CT
>
|||Hi John,
Its SQL Server 2000 (sp4), the client tools are Enterprise Manager V8.0
I do use the DTS for files but sometimes this is quicker!! As I said
my main concern was accidently showing a user how to do this and now I
wonder what it is doing internally (if anything). I'm confused you cant
replicate this behaviour. We do have a later version of SQL server on
a test environment - I'll try it there and see if it still works.
CT
John Bell wrote:[vbcol=seagreen]
> Hi
> I don't seem to be able to get this to happen, maybe I am missing something!
> What version of SQL Server are you using and are your client tools at the
> same version?
> If you need to regularly import files into SQL Server you may want to look
> at using DTS to do this, you can have jobs that regularly check any files
> dropped into a directory and upload them check out
> http://www.sqldts.com/default.aspx for lots of information on how to do this.
> John
> "cheesey_toastie" wrote:
|||Hi
You can check to see if at least one of the file versions the exes for the
tools match the version returned by SELECT @.@.VERSION to see if the tools are
up to date. You may also want to post the ddl for your table.
From what you say an extra column is appearing when you choose paste from
the menus, if after you have pasted and saved the values the table
definition has not changed then I would expect things to be ok.
John
"cheesey_toastie" <bletchley_scum@.yahoo.co.uk> wrote in message
news:1160209997.605041.124600@.m73g2000cwd.googlegr oups.com...
> Hi John,
> Its SQL Server 2000 (sp4), the client tools are Enterprise Manager V8.0
> I do use the DTS for files but sometimes this is quicker!! As I said
> my main concern was accidently showing a user how to do this and now I
> wonder what it is doing internally (if anything). I'm confused you cant
> replicate this behaviour. We do have a later version of SQL server on
> a test environment - I'll try it there and see if it still works.
> CT
> John Bell wrote:
>

Copy paste from Excel - should this work?

Hi,
Now I'm worried as I showed this bodge to a colleague and he is using
it a lot! I found that you can copy paste directly from Excel into the
Enterprise Manager GUI (we're on v8.0, sql server 2000).
The way I do it is say you had some Excel data in cols B-D, highlight A
as well (leaving this column blank), click the arrow on the new row at
the bottom of your table (a table with 3 columns matching B-D) in
Enterprise Manager and voila a paste option appears in the right hand
mouse button menu.
Why? What is this blank column doing? Is it causing any harm
internally to the db to insert nulls (or empty excel strings more
correctly?) in this column?
Any help / warnings much appreciated!
CTHi
I don't seem to be able to get this to happen, maybe I am missing something!
What version of SQL Server are you using and are your client tools at the
same version?
If you need to regularly import files into SQL Server you may want to look
at using DTS to do this, you can have jobs that regularly check any files
dropped into a directory and upload them check out
http://www.sqldts.com/default.aspx for lots of information on how to do this
.
John
"cheesey_toastie" wrote:

> Hi,
> Now I'm worried as I showed this bodge to a colleague and he is using
> it a lot! I found that you can copy paste directly from Excel into the
> Enterprise Manager GUI (we're on v8.0, sql server 2000).
> The way I do it is say you had some Excel data in cols B-D, highlight A
> as well (leaving this column blank), click the arrow on the new row at
> the bottom of your table (a table with 3 columns matching B-D) in
> Enterprise Manager and voila a paste option appears in the right hand
> mouse button menu.
> Why? What is this blank column doing? Is it causing any harm
> internally to the db to insert nulls (or empty excel strings more
> correctly?) in this column?
> Any help / warnings much appreciated!
> CT
>|||Hi John,
Its SQL Server 2000 (sp4), the client tools are Enterprise Manager V8.0
I do use the DTS for files but sometimes this is quicker!! As I said
my main concern was accidently showing a user how to do this and now I
wonder what it is doing internally (if anything). I'm confused you cant
replicate this behaviour. We do have a later version of SQL server on
a test environment - I'll try it there and see if it still works.
CT
John Bell wrote:[vbcol=seagreen]
> Hi
> I don't seem to be able to get this to happen, maybe I am missing somethin
g!
> What version of SQL Server are you using and are your client tools at the
> same version?
> If you need to regularly import files into SQL Server you may want to look
> at using DTS to do this, you can have jobs that regularly check any files
> dropped into a directory and upload them check out
> http://www.sqldts.com/default.aspx for lots of information on how to do th
is.
> John
> "cheesey_toastie" wrote:
>|||Hi
You can check to see if at least one of the file versions the exes for the
tools match the version returned by SELECT @.@.VERSION to see if the tools are
up to date. You may also want to post the ddl for your table.
From what you say an extra column is appearing when you choose paste from
the menus, if after you have pasted and saved the values the table
definition has not changed then I would expect things to be ok.
John
"cheesey_toastie" <bletchley_scum@.yahoo.co.uk> wrote in message
news:1160209997.605041.124600@.m73g2000cwd.googlegroups.com...
> Hi John,
> Its SQL Server 2000 (sp4), the client tools are Enterprise Manager V8.0
> I do use the DTS for files but sometimes this is quicker!! As I said
> my main concern was accidently showing a user how to do this and now I
> wonder what it is doing internally (if anything). I'm confused you cant
> replicate this behaviour. We do have a later version of SQL server on
> a test environment - I'll try it there and see if it still works.
> CT
> John Bell wrote:
>

Copy paste from Excel - should this work?

Hi,
Now I'm worried as I showed this bodge to a colleague and he is using
it a lot! I found that you can copy paste directly from Excel into the
Enterprise Manager GUI (we're on v8.0, sql server 2000).
The way I do it is say you had some Excel data in cols B-D, highlight A
as well (leaving this column blank), click the arrow on the new row at
the bottom of your table (a table with 3 columns matching B-D) in
Enterprise Manager and voila a paste option appears in the right hand
mouse button menu.
Why? What is this blank column doing? Is it causing any harm
internally to the db to insert nulls (or empty excel strings more
correctly?) in this column?
Any help / warnings much appreciated!
CTHi
I don't seem to be able to get this to happen, maybe I am missing something!
What version of SQL Server are you using and are your client tools at the
same version?
If you need to regularly import files into SQL Server you may want to look
at using DTS to do this, you can have jobs that regularly check any files
dropped into a directory and upload them check out
http://www.sqldts.com/default.aspx for lots of information on how to do this.
John
"cheesey_toastie" wrote:
> Hi,
> Now I'm worried as I showed this bodge to a colleague and he is using
> it a lot! I found that you can copy paste directly from Excel into the
> Enterprise Manager GUI (we're on v8.0, sql server 2000).
> The way I do it is say you had some Excel data in cols B-D, highlight A
> as well (leaving this column blank), click the arrow on the new row at
> the bottom of your table (a table with 3 columns matching B-D) in
> Enterprise Manager and voila a paste option appears in the right hand
> mouse button menu.
> Why? What is this blank column doing? Is it causing any harm
> internally to the db to insert nulls (or empty excel strings more
> correctly?) in this column?
> Any help / warnings much appreciated!
> CT
>|||Hi John,
Its SQL Server 2000 (sp4), the client tools are Enterprise Manager V8.0
I do use the DTS for files but sometimes this is quicker!! As I said
my main concern was accidently showing a user how to do this and now I
wonder what it is doing internally (if anything). I'm confused you cant
replicate this behaviour. We do have a later version of SQL server on
a test environment - I'll try it there and see if it still works.
CT
John Bell wrote:
> Hi
> I don't seem to be able to get this to happen, maybe I am missing something!
> What version of SQL Server are you using and are your client tools at the
> same version?
> If you need to regularly import files into SQL Server you may want to look
> at using DTS to do this, you can have jobs that regularly check any files
> dropped into a directory and upload them check out
> http://www.sqldts.com/default.aspx for lots of information on how to do this.
> John
> "cheesey_toastie" wrote:
> > Hi,
> >
> > Now I'm worried as I showed this bodge to a colleague and he is using
> > it a lot! I found that you can copy paste directly from Excel into the
> > Enterprise Manager GUI (we're on v8.0, sql server 2000).
> >
> > The way I do it is say you had some Excel data in cols B-D, highlight A
> > as well (leaving this column blank), click the arrow on the new row at
> > the bottom of your table (a table with 3 columns matching B-D) in
> > Enterprise Manager and voila a paste option appears in the right hand
> > mouse button menu.
> >
> > Why? What is this blank column doing? Is it causing any harm
> > internally to the db to insert nulls (or empty excel strings more
> > correctly?) in this column?
> >
> > Any help / warnings much appreciated!
> >
> > CT
> >
> >|||Hi
You can check to see if at least one of the file versions the exes for the
tools match the version returned by SELECT @.@.VERSION to see if the tools are
up to date. You may also want to post the ddl for your table.
From what you say an extra column is appearing when you choose paste from
the menus, if after you have pasted and saved the values the table
definition has not changed then I would expect things to be ok.
John
"cheesey_toastie" <bletchley_scum@.yahoo.co.uk> wrote in message
news:1160209997.605041.124600@.m73g2000cwd.googlegroups.com...
> Hi John,
> Its SQL Server 2000 (sp4), the client tools are Enterprise Manager V8.0
> I do use the DTS for files but sometimes this is quicker!! As I said
> my main concern was accidently showing a user how to do this and now I
> wonder what it is doing internally (if anything). I'm confused you cant
> replicate this behaviour. We do have a later version of SQL server on
> a test environment - I'll try it there and see if it still works.
> CT
> John Bell wrote:
>> Hi
>> I don't seem to be able to get this to happen, maybe I am missing
>> something!
>> What version of SQL Server are you using and are your client tools at the
>> same version?
>> If you need to regularly import files into SQL Server you may want to
>> look
>> at using DTS to do this, you can have jobs that regularly check any files
>> dropped into a directory and upload them check out
>> http://www.sqldts.com/default.aspx for lots of information on how to do
>> this.
>> John
>> "cheesey_toastie" wrote:
>> > Hi,
>> >
>> > Now I'm worried as I showed this bodge to a colleague and he is using
>> > it a lot! I found that you can copy paste directly from Excel into the
>> > Enterprise Manager GUI (we're on v8.0, sql server 2000).
>> >
>> > The way I do it is say you had some Excel data in cols B-D, highlight A
>> > as well (leaving this column blank), click the arrow on the new row at
>> > the bottom of your table (a table with 3 columns matching B-D) in
>> > Enterprise Manager and voila a paste option appears in the right hand
>> > mouse button menu.
>> >
>> > Why? What is this blank column doing? Is it causing any harm
>> > internally to the db to insert nulls (or empty excel strings more
>> > correctly?) in this column?
>> >
>> > Any help / warnings much appreciated!
>> >
>> > CT
>> >
>> >
>sqlsql

Copy or insert Columns from a table in another Database, How?

Hallo,
I thought it is easily done, but no. I have 3 columns of hundreds of data i
n a table in another Database within thesame server that i want to move to m
y production Database. I have tried Select into, copy, insert method... wit
hout success.
Please help.
ThanksHi,
If the structure of the table in production database is identical then use
Insert into
Insert into productiondatabase..table_name(col1,col2,col3) select
col1,col2,col3 from devdb..table
Above command will fail incase the same table have any other column which
will not allow "NOT NULL"
I have tried Select into, copy, insert method... without success.
Tell the error you are getting.
Thanks
Hari
MCDBA
"Ototofioto" <anonymous@.discussions.microsoft.com> wrote in message
news:6ED03A97-26F9-483A-93B9-3BFB8F28C87D@.microsoft.com...
> Hallo,
> I thought it is easily done, but no. I have 3 columns of hundreds of data
in a table in another Database within thesame server that i want to move to
my production Database. I have tried Select into, copy, insert method...
without success.
> Please help.
> Thanks

Copy one row from tableA to tableB

I want to copy all columns from tblA into tblB where tblA.UniqueID =
parameter @.ID without having to list each column name in an insert
statement. Is this possible?
Thanks,
lqI forgot to mention all column names and data types are identical in
tblA and tblB except for the UniqueID which is PK in tblA and not PK in
tblB.
lq|||Sure,

INSERT INTO tblB
SELECT *
FROM tblA
WHERE UniqueID = @.ID

Stu|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> I want to copy all columns from tblA into tblB where tblA.UniqueID =
> parameter @.ID without having to list each column name in an insert
> statement. Is this possible?

Under some circumstances, yes.

Then again, in application code, I think it is very bad practice to say:

INSERT tbl2 SELECT * FROM tbl1

SELECT * itself is bad practice, so are INSERT statements without listing
of target columns.

Why are this bad:

* You cannot see if a column is actually used.
* If the table defintion changes, the result of the SELECT changes.
* And for INSERT it is likely to result in the INSERT statment failing.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp