Hi,
I have a role created in one db. I need to copy that role to another db same
strucxture in the same server.
How do I do that. Role without allthe permissions
Thanks
On 6 Jul, 19:39, "mecn" <mecn2...@.yahoo.com> wrote:
> Hi,
> I have a role created in one db. I need to copy that role to another db same
> strucxture in the same server.
> How do I do that. Role without allthe permissions
> Thanks
A role without permissions is nothing more than a role name. So just
do:
sp_addrole 'role_name';
If you require the members as well then script it using Enterprise
Manager: All Tasks > Generate Script, on the Options tab select
"Script database users and database roles".
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Thanks,
I need to copy the role with all object permissions assign ed the the role
in one db to another db with the same structrue.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1183747694.859143.26150@.n2g2000hse.googlegrou ps.com...
> On 6 Jul, 19:39, "mecn" <mecn2...@.yahoo.com> wrote:
> A role without permissions is nothing more than a role name. So just
> do:
> sp_addrole 'role_name';
> If you require the members as well then script it using Enterprise
> Manager: All Tasks > Generate Script, on the Options tab select
> "Script database users and database roles".
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
Showing posts with label copy. Show all posts
Showing posts with label copy. Show all posts
Friday, March 30, 2012
role in sql2000
Hi,
I have a role created in one db. I need to copy that role to another db same
strucxture in the same server.
How do I do that. Role without allthe permissions
ThanksOn 6 Jul, 19:39, "mecn" <mecn2...@.yahoo.com> wrote:
> Hi,
> I have a role created in one db. I need to copy that role to another db same
> strucxture in the same server.
> How do I do that. Role without allthe permissions
> Thanks
A role without permissions is nothing more than a role name. So just
do:
sp_addrole 'role_name';
If you require the members as well then script it using Enterprise
Manager: All Tasks > Generate Script, on the Options tab select
"Script database users and database roles".
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks,
I need to copy the role with all object permissions assign ed the the role
in one db to another db with the same structrue.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1183747694.859143.26150@.n2g2000hse.googlegroups.com...
> On 6 Jul, 19:39, "mecn" <mecn2...@.yahoo.com> wrote:
>> Hi,
>> I have a role created in one db. I need to copy that role to another db
>> same
>> strucxture in the same server.
>> How do I do that. Role without allthe permissions
>> Thanks
> A role without permissions is nothing more than a role name. So just
> do:
> sp_addrole 'role_name';
> If you require the members as well then script it using Enterprise
> Manager: All Tasks > Generate Script, on the Options tab select
> "Script database users and database roles".
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
I have a role created in one db. I need to copy that role to another db same
strucxture in the same server.
How do I do that. Role without allthe permissions
ThanksOn 6 Jul, 19:39, "mecn" <mecn2...@.yahoo.com> wrote:
> Hi,
> I have a role created in one db. I need to copy that role to another db same
> strucxture in the same server.
> How do I do that. Role without allthe permissions
> Thanks
A role without permissions is nothing more than a role name. So just
do:
sp_addrole 'role_name';
If you require the members as well then script it using Enterprise
Manager: All Tasks > Generate Script, on the Options tab select
"Script database users and database roles".
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks,
I need to copy the role with all object permissions assign ed the the role
in one db to another db with the same structrue.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1183747694.859143.26150@.n2g2000hse.googlegroups.com...
> On 6 Jul, 19:39, "mecn" <mecn2...@.yahoo.com> wrote:
>> Hi,
>> I have a role created in one db. I need to copy that role to another db
>> same
>> strucxture in the same server.
>> How do I do that. Role without allthe permissions
>> Thanks
> A role without permissions is nothing more than a role name. So just
> do:
> sp_addrole 'role_name';
> If you require the members as well then script it using Enterprise
> Manager: All Tasks > Generate Script, on the Options tab select
> "Script database users and database roles".
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
role in sql2000
Hi,
I have a role created in one db. I need to copy that role to another db same
strucxture in the same server.
How do I do that. Role without allthe permissions
ThanksOn 6 Jul, 19:39, "mecn" <mecn2...@.yahoo.com> wrote:
> Hi,
> I have a role created in one db. I need to copy that role to another db sa
me
> strucxture in the same server.
> How do I do that. Role without allthe permissions
> Thanks
A role without permissions is nothing more than a role name. So just
do:
sp_addrole 'role_name';
If you require the members as well then script it using Enterprise
Manager: All Tasks > Generate Script, on the Options tab select
"Script database users and database roles".
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks,
I need to copy the role with all object permissions assign ed the the role
in one db to another db with the same structrue.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1183747694.859143.26150@.n2g2000hse.googlegroups.com...
> On 6 Jul, 19:39, "mecn" <mecn2...@.yahoo.com> wrote:
> A role without permissions is nothing more than a role name. So just
> do:
> sp_addrole 'role_name';
> If you require the members as well then script it using Enterprise
> Manager: All Tasks > Generate Script, on the Options tab select
> "Script database users and database roles".
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
I have a role created in one db. I need to copy that role to another db same
strucxture in the same server.
How do I do that. Role without allthe permissions
ThanksOn 6 Jul, 19:39, "mecn" <mecn2...@.yahoo.com> wrote:
> Hi,
> I have a role created in one db. I need to copy that role to another db sa
me
> strucxture in the same server.
> How do I do that. Role without allthe permissions
> Thanks
A role without permissions is nothing more than a role name. So just
do:
sp_addrole 'role_name';
If you require the members as well then script it using Enterprise
Manager: All Tasks > Generate Script, on the Options tab select
"Script database users and database roles".
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks,
I need to copy the role with all object permissions assign ed the the role
in one db to another db with the same structrue.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1183747694.859143.26150@.n2g2000hse.googlegroups.com...
> On 6 Jul, 19:39, "mecn" <mecn2...@.yahoo.com> wrote:
> A role without permissions is nothing more than a role name. So just
> do:
> sp_addrole 'role_name';
> If you require the members as well then script it using Enterprise
> Manager: All Tasks > Generate Script, on the Options tab select
> "Script database users and database roles".
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
Wednesday, March 21, 2012
richie robles
How do you copy a 'production' database from the same group to a development database. I've been told that a restore is the hot ticket, but I'm afraid of overriding data.
Hi
create a backup of your development database and then do the restore of the production db. Keep the .bak safe somewhere :)
Quote:
Originally Posted by Richie Robles
How do you copy a 'production' database from the same group to a development database. I've been told that a restore is the hot ticket, but I'm afraid of overriding data.
Hi
create a backup of your development database and then do the restore of the production db. Keep the .bak safe somewhere :)
Monday, March 12, 2012
reverse merge repliction
hi
I have notice that i missed few columns in my merge repliction and i wonder is it possiblie to reverse a replicated database or copy to normal SQL database?
after that i would do a new Publication.
I need to have the data that is in the Database.
If this is SQL Server 2005, you can always use alter table and add the new columns to the table. They should automatically propagate to the subscriber.
If you wish you can drop the publication, add the columns and reset the publication too.
The data in the tables will be intact.
reverse merge repliction
hi
I have notice that i missed few columns in my merge repliction and i wonder is it possiblie to reverse a replicated database or copy to normal SQL database?
after that i would do a new Publication.
I need to have the data that is in the Database.
If this is SQL Server 2005, you can always use alter table and add the new columns to the table. They should automatically propagate to the subscriber.
If you wish you can drop the publication, add the columns and reset the publication too.
The data in the tables will be intact.
Reverse data processed...
Hi All,
I have a very big database (110GB+). It takes a long time to restore or
even just copy...
Now, I have a process that will alter a lot of data in this DB over a lot of
tables. And this process has a bug in it... In order to debug this
process, I would need to have to keep restoring this DB into its initial
state. The process takes about 45 min to 1 hour.
Would someone be kind and tell me:
1) Is there a way to reverse the changes this "Process" made quickly? i.e.
Quick restore. Obviously, I have a full backup.
2) A good old restore takes about 4 hours from a local drive
3) A good old file copy takes about 2 hours.
4) What I am really looking for, is a way to undo the DB to certain point
in time. Like a restore point...
Anyway idea?
Thanks,
AlexHello Alex,
Depending on your process, you may be able to successfully accomplish what
you need by building some error handling into your process that will
ROLLBACK TRANSACTION when an error is encountered. Note that you can not
ROLLBACK once a transaction has been committed, so this would have to
happen before a COMMIT TRAN statement. It may also be a good idea to
create a log entry in the NT Event Logs to provide more information about
the details of the error. You can use RAISERROR to call messages stored in
the sysmessages table of your database, which can be configured using
sp_addmessage. For example:
BEGIN TRAN T1
..statements...
IF @.@.ERROR <> 0
BEGIN
RAISERROR ('Error Message Text', 16, 1) WITH LOG
ROLLBACK TRANSACTION
END
..statements...
COMMIT TRAN
For more information about using RAISERROR to provide specific information
about the error that you're receiving, please refer to Books Online and
search for RAISERROR. This will fully describe the parameters that are
available with RAISERROR.
Another way to manage your issue, depending on your testing requirements,
might be to debug your process against a smaller data set. By pulling a
subset of data from the VLDB you should be able to test you process, break
your data, and restore your data much faster. You can use DTS to transfer
the data over to a new database and then run your process against that.
If working against a smaller dataset is not an option for you, another
method would be to move the affected tables to a different filegroup. Once
you have run your process you could restore just that one filegroup, which
should be smaller (and faster) than a full restore.
Restore points and checkpoints are used by MS SQL Server only during the
restore process, so these will not be helpful for you as a "rollback to X
point" option. There may be third party solutions available on the market
that provide this level of functionality.
Please let me know if you have any other concerns, or need anything else.
Hope this helps!
Sincerely,
Dana Brash
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Reply-To: "Smartikat" <Smartikat@.online.nospam>
>From: "Smartikat" <Smartikat@.online.nospam>
>Subject: Reverse data processed...
>Date: Tue, 25 Jan 2005 16:40:20 -0800
>Lines: 26
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-RFC2646: Format=Flowed; Original
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>Message-ID: <#PSre#zAFHA.4004@.tk2msftngp13.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.programming
>NNTP-Posting-Host: 63.171.237.69
>Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13
.phx.gbl
>Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.programming:499552
>X-Tomcat-NG: microsoft.public.sqlserver.programming
>Hi All,
>I have a very big database (110GB+). It takes a long time to restore or
>even just copy...
>Now, I have a process that will alter a lot of data in this DB over a lot
of
>tables. And this process has a bug in it... In order to debug this
>process, I would need to have to keep restoring this DB into its initial
>state. The process takes about 45 min to 1 hour.
>Would someone be kind and tell me:
>1) Is there a way to reverse the changes this "Process" made quickly?
i.e.
>Quick restore. Obviously, I have a full backup.
>2) A good old restore takes about 4 hours from a local drive
>3) A good old file copy takes about 2 hours.
>4) What I am really looking for, is a way to undo the DB to certain point
>in time. Like a restore point...
>Anyway idea?
>Thanks,
>Alex
>
>|||If you have the disk space, detach the database using sp_detach_db, make
copies of the files, then just keep making a fresh copy of the file every
time and then use sp_attach_db to reattach it. Should be much faster.
You might also just consider wrapping the whole thing in a transaction and
step through the process a few statements at a time and rollback when you
find a bug. If it is a single statement taking a bulk of the time, post it
if you can and someone might be able to help you find your problem.
This might sound preachy, but you might want to execute the code "manually"
by walking through and tracing through all of the joins mentally and on a
white board. It can really make the difference if you can wrap your head
around it, you may even find errors that don't crop up in simple testing.
Happy hunting!
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Smartikat" <Smartikat@.online.nospam> wrote in message
news:%23PSre%23zAFHA.4004@.tk2msftngp13.phx.gbl...
> Hi All,
> I have a very big database (110GB+). It takes a long time to restore or
> even just copy...
> Now, I have a process that will alter a lot of data in this DB over a lot
> of tables. And this process has a bug in it... In order to debug this
> process, I would need to have to keep restoring this DB into its initial
> state. The process takes about 45 min to 1 hour.
> Would someone be kind and tell me:
> 1) Is there a way to reverse the changes this "Process" made quickly?
> i.e. Quick restore. Obviously, I have a full backup.
> 2) A good old restore takes about 4 hours from a local drive
> 3) A good old file copy takes about 2 hours.
> 4) What I am really looking for, is a way to undo the DB to certain point
> in time. Like a restore point...
> Anyway idea?
> Thanks,
> Alex
>
I have a very big database (110GB+). It takes a long time to restore or
even just copy...
Now, I have a process that will alter a lot of data in this DB over a lot of
tables. And this process has a bug in it... In order to debug this
process, I would need to have to keep restoring this DB into its initial
state. The process takes about 45 min to 1 hour.
Would someone be kind and tell me:
1) Is there a way to reverse the changes this "Process" made quickly? i.e.
Quick restore. Obviously, I have a full backup.
2) A good old restore takes about 4 hours from a local drive
3) A good old file copy takes about 2 hours.
4) What I am really looking for, is a way to undo the DB to certain point
in time. Like a restore point...
Anyway idea?
Thanks,
AlexHello Alex,
Depending on your process, you may be able to successfully accomplish what
you need by building some error handling into your process that will
ROLLBACK TRANSACTION when an error is encountered. Note that you can not
ROLLBACK once a transaction has been committed, so this would have to
happen before a COMMIT TRAN statement. It may also be a good idea to
create a log entry in the NT Event Logs to provide more information about
the details of the error. You can use RAISERROR to call messages stored in
the sysmessages table of your database, which can be configured using
sp_addmessage. For example:
BEGIN TRAN T1
..statements...
IF @.@.ERROR <> 0
BEGIN
RAISERROR ('Error Message Text', 16, 1) WITH LOG
ROLLBACK TRANSACTION
END
..statements...
COMMIT TRAN
For more information about using RAISERROR to provide specific information
about the error that you're receiving, please refer to Books Online and
search for RAISERROR. This will fully describe the parameters that are
available with RAISERROR.
Another way to manage your issue, depending on your testing requirements,
might be to debug your process against a smaller data set. By pulling a
subset of data from the VLDB you should be able to test you process, break
your data, and restore your data much faster. You can use DTS to transfer
the data over to a new database and then run your process against that.
If working against a smaller dataset is not an option for you, another
method would be to move the affected tables to a different filegroup. Once
you have run your process you could restore just that one filegroup, which
should be smaller (and faster) than a full restore.
Restore points and checkpoints are used by MS SQL Server only during the
restore process, so these will not be helpful for you as a "rollback to X
point" option. There may be third party solutions available on the market
that provide this level of functionality.
Please let me know if you have any other concerns, or need anything else.
Hope this helps!
Sincerely,
Dana Brash
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Reply-To: "Smartikat" <Smartikat@.online.nospam>
>From: "Smartikat" <Smartikat@.online.nospam>
>Subject: Reverse data processed...
>Date: Tue, 25 Jan 2005 16:40:20 -0800
>Lines: 26
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-RFC2646: Format=Flowed; Original
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>Message-ID: <#PSre#zAFHA.4004@.tk2msftngp13.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.programming
>NNTP-Posting-Host: 63.171.237.69
>Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13
.phx.gbl
>Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.programming:499552
>X-Tomcat-NG: microsoft.public.sqlserver.programming
>Hi All,
>I have a very big database (110GB+). It takes a long time to restore or
>even just copy...
>Now, I have a process that will alter a lot of data in this DB over a lot
of
>tables. And this process has a bug in it... In order to debug this
>process, I would need to have to keep restoring this DB into its initial
>state. The process takes about 45 min to 1 hour.
>Would someone be kind and tell me:
>1) Is there a way to reverse the changes this "Process" made quickly?
i.e.
>Quick restore. Obviously, I have a full backup.
>2) A good old restore takes about 4 hours from a local drive
>3) A good old file copy takes about 2 hours.
>4) What I am really looking for, is a way to undo the DB to certain point
>in time. Like a restore point...
>Anyway idea?
>Thanks,
>Alex
>
>|||If you have the disk space, detach the database using sp_detach_db, make
copies of the files, then just keep making a fresh copy of the file every
time and then use sp_attach_db to reattach it. Should be much faster.
You might also just consider wrapping the whole thing in a transaction and
step through the process a few statements at a time and rollback when you
find a bug. If it is a single statement taking a bulk of the time, post it
if you can and someone might be able to help you find your problem.
This might sound preachy, but you might want to execute the code "manually"
by walking through and tracing through all of the joins mentally and on a
white board. It can really make the difference if you can wrap your head
around it, you may even find errors that don't crop up in simple testing.
Happy hunting!
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Smartikat" <Smartikat@.online.nospam> wrote in message
news:%23PSre%23zAFHA.4004@.tk2msftngp13.phx.gbl...
> Hi All,
> I have a very big database (110GB+). It takes a long time to restore or
> even just copy...
> Now, I have a process that will alter a lot of data in this DB over a lot
> of tables. And this process has a bug in it... In order to debug this
> process, I would need to have to keep restoring this DB into its initial
> state. The process takes about 45 min to 1 hour.
> Would someone be kind and tell me:
> 1) Is there a way to reverse the changes this "Process" made quickly?
> i.e. Quick restore. Obviously, I have a full backup.
> 2) A good old restore takes about 4 hours from a local drive
> 3) A good old file copy takes about 2 hours.
> 4) What I am really looking for, is a way to undo the DB to certain point
> in time. Like a restore point...
> Anyway idea?
> Thanks,
> Alex
>
Subscribe to:
Posts (Atom)