Showing posts with label dear. Show all posts
Showing posts with label dear. Show all posts

Wednesday, March 21, 2012

Database migration and security issues

Dear all,
I have 2 questions:
(1) I would like to migrate a SQL database (named "myDB") from SQL server A
to SQL server B. I am planning to create a new database named "myDB" in
server B, then go back to server A to do the backup. Then restore it in
server B. Is it feasible? Anyone has a better way to do it?
(2) The second question deals with security issue and it makes me
frustrated. There is a database which is maintained by an end-user in my
company currently. He has the "db_owner" rights on this database. Now we
(MIS) are going to take over the admin work of this database. We will assign
"db_reader" & "db_writer" to him only. However, this user wrote some VB
programs to access this database (using ADO) and this programs involves some
actions of deleteing and creating temp tables in the database. If he only ha
s
"db_reader" & "db_writer" rights, then the program would fail to work. It is
not reasonable to create another SQL server login id with "db_owner" right t
o
this user, this would lose control on our admin work again. I am quite
frustrated by this... can anyone help?
Thanks a lot.
Ivan1. create backup on server A then restore on server B. You must transfer
logins from server A to B
2. ">>this programs involves some
> actions of deleteing and creating temp tables in the database""
create temp tables in tembdb, not in your prod db
OR
add this user in db_ddladmin
--
Aleksandar Grbic
MCDBA, Senior Database Administrator
"Ivan" wrote:

> Dear all,
> I have 2 questions:
> (1) I would like to migrate a SQL database (named "myDB") from SQL server
A
> to SQL server B. I am planning to create a new database named "myDB" in
> server B, then go back to server A to do the backup. Then restore it in
> server B. Is it feasible? Anyone has a better way to do it?
> (2) The second question deals with security issue and it makes me
> frustrated. There is a database which is maintained by an end-user in my
> company currently. He has the "db_owner" rights on this database. Now we
> (MIS) are going to take over the admin work of this database. We will assi
gn
> "db_reader" & "db_writer" to him only. However, this user wrote some VB
> programs to access this database (using ADO) and this programs involves so
me
> actions of deleteing and creating temp tables in the database. If he only
has
> "db_reader" & "db_writer" rights, then the program would fail to work. It
is
> not reasonable to create another SQL server login id with "db_owner" right
to
> this user, this would lose control on our admin work again. I am quite
> frustrated by this... can anyone help?
> Thanks a lot.
> Ivan|||Dear Aleksandar,
How to transfer logins ?
Thanks!!
"Aleksandar Grbic" wrote:
[vbcol=seagreen]
> 1. create backup on server A then restore on server B. You must transfer
> logins from server A to B
> 2. ">>this programs involves some
> create temp tables in tembdb, not in your prod db
> OR
> add this user in db_ddladmin
> --
> Aleksandar Grbic
> MCDBA, Senior Database Administrator
>
> "Ivan" wrote:
>|||Hi Ivan,
(i) U can restore then database by backup
(ii) copy the mdf file and attach the database.
(iii) Use copy database wizard.
(iv) script the database and run the script on the server and transfer
the data.
Go to DTS and use transfer logins .
defien source from where u want to transfer the logins and destination
to where ti want to copy.
but before u tranfer u create or restore or attach the database on that
srever.
hope this hel u
from
killer|||Search Knowledgebase for
sp_help_revlogin
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ivan" <Ivan@.discussions.microsoft.com> wrote in message
news:5E32FF17-7866-40CB-A5CE-6AE5EE0FD64C@.microsoft.com...[vbcol=seagreen]
> Dear Aleksandar,
> How to transfer logins ?
> Thanks!!
> "Aleksandar Grbic" wrote:
>|||show BOL for help, "sp_addlogin"
you must create login on server B with same SID and password as logins on
server A
see examples in Books Online
OR
see "HOW TO: Transfer Logins and Passwords Between Instances of SQL Server"
at http://support.microsoft.com/defaul...kb;en-us;246133
Aleksandar Grbic
MCDBA, Senior Database Administrator
"Ivan" wrote:
[vbcol=seagreen]
> Dear Aleksandar,
> How to transfer logins ?
> Thanks!!
> "Aleksandar Grbic" wrote:
>

Database migration and security issues

Dear all,
I have 2 questions:
(1) I would like to migrate a SQL database (named "myDB") from SQL server A
to SQL server B. I am planning to create a new database named "myDB" in
server B, then go back to server A to do the backup. Then restore it in
server B. Is it feasible? Anyone has a better way to do it?
(2) The second question deals with security issue and it makes me
frustrated. There is a database which is maintained by an end-user in my
company currently. He has the "db_owner" rights on this database. Now we
(MIS) are going to take over the admin work of this database. We will assign
"db_reader" & "db_writer" to him only. However, this user wrote some VB
programs to access this database (using ADO) and this programs involves some
actions of deleteing and creating temp tables in the database. If he only has
"db_reader" & "db_writer" rights, then the program would fail to work. It is
not reasonable to create another SQL server login id with "db_owner" right to
this user, this would lose control on our admin work again. I am quite
frustrated by this... can anyone help?
Thanks a lot.
Ivan
1. create backup on server A then restore on server B. You must transfer
logins from server A to B
2. ">>this programs involves some
> actions of deleteing and creating temp tables in the database""
create temp tables in tembdb, not in your prod db
OR
add this user in db_ddladmin
Aleksandar Grbic
MCDBA, Senior Database Administrator
"Ivan" wrote:

> Dear all,
> I have 2 questions:
> (1) I would like to migrate a SQL database (named "myDB") from SQL server A
> to SQL server B. I am planning to create a new database named "myDB" in
> server B, then go back to server A to do the backup. Then restore it in
> server B. Is it feasible? Anyone has a better way to do it?
> (2) The second question deals with security issue and it makes me
> frustrated. There is a database which is maintained by an end-user in my
> company currently. He has the "db_owner" rights on this database. Now we
> (MIS) are going to take over the admin work of this database. We will assign
> "db_reader" & "db_writer" to him only. However, this user wrote some VB
> programs to access this database (using ADO) and this programs involves some
> actions of deleteing and creating temp tables in the database. If he only has
> "db_reader" & "db_writer" rights, then the program would fail to work. It is
> not reasonable to create another SQL server login id with "db_owner" right to
> this user, this would lose control on our admin work again. I am quite
> frustrated by this... can anyone help?
> Thanks a lot.
> Ivan
|||Dear Aleksandar,
How to transfer logins ?
Thanks!!
"Aleksandar Grbic" wrote:
[vbcol=seagreen]
> 1. create backup on server A then restore on server B. You must transfer
> logins from server A to B
> 2. ">>this programs involves some
> create temp tables in tembdb, not in your prod db
> OR
> add this user in db_ddladmin
> --
> Aleksandar Grbic
> MCDBA, Senior Database Administrator
>
> "Ivan" wrote:
|||Hi Ivan,
(i) U can restore then database by backup
(ii) copy the mdf file and attach the database.
(iii) Use copy database wizard.
(iv) script the database and run the script on the server and transfer
the data.
Go to DTS and use transfer logins .
defien source from where u want to transfer the logins and destination
to where ti want to copy.
but before u tranfer u create or restore or attach the database on that
srever.
hope this hel u
from
killer
|||Search Knowledgebase for
sp_help_revlogin
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ivan" <Ivan@.discussions.microsoft.com> wrote in message
news:5E32FF17-7866-40CB-A5CE-6AE5EE0FD64C@.microsoft.com...[vbcol=seagreen]
> Dear Aleksandar,
> How to transfer logins ?
> Thanks!!
> "Aleksandar Grbic" wrote:
|||show BOL for help, "sp_addlogin"
you must create login on server B with same SID and password as logins on
server A
see examples in Books Online
OR
see "HOW TO: Transfer Logins and Passwords Between Instances of SQL Server"
at http://support.microsoft.com/default...b;en-us;246133
Aleksandar Grbic
MCDBA, Senior Database Administrator
"Ivan" wrote:
[vbcol=seagreen]
> Dear Aleksandar,
> How to transfer logins ?
> Thanks!!
> "Aleksandar Grbic" wrote:

Database migration and security issues

Dear all,
I have 2 questions:
(1) I would like to migrate a SQL database (named "myDB") from SQL server A
to SQL server B. I am planning to create a new database named "myDB" in
server B, then go back to server A to do the backup. Then restore it in
server B. Is it feasible? Anyone has a better way to do it?
(2) The second question deals with security issue and it makes me
frustrated. There is a database which is maintained by an end-user in my
company currently. He has the "db_owner" rights on this database. Now we
(MIS) are going to take over the admin work of this database. We will assign
"db_reader" & "db_writer" to him only. However, this user wrote some VB
programs to access this database (using ADO) and this programs involves some
actions of deleteing and creating temp tables in the database. If he only has
"db_reader" & "db_writer" rights, then the program would fail to work. It is
not reasonable to create another SQL server login id with "db_owner" right to
this user, this would lose control on our admin work again. I am quite
frustrated by this... can anyone help?
Thanks a lot.
Ivan1. create backup on server A then restore on server B. You must transfer
logins from server A to B
2. ">>this programs involves some
> actions of deleteing and creating temp tables in the database""
create temp tables in tembdb, not in your prod db
OR
add this user in db_ddladmin :(
--
Aleksandar Grbic
MCDBA, Senior Database Administrator
"Ivan" wrote:
> Dear all,
> I have 2 questions:
> (1) I would like to migrate a SQL database (named "myDB") from SQL server A
> to SQL server B. I am planning to create a new database named "myDB" in
> server B, then go back to server A to do the backup. Then restore it in
> server B. Is it feasible? Anyone has a better way to do it?
> (2) The second question deals with security issue and it makes me
> frustrated. There is a database which is maintained by an end-user in my
> company currently. He has the "db_owner" rights on this database. Now we
> (MIS) are going to take over the admin work of this database. We will assign
> "db_reader" & "db_writer" to him only. However, this user wrote some VB
> programs to access this database (using ADO) and this programs involves some
> actions of deleteing and creating temp tables in the database. If he only has
> "db_reader" & "db_writer" rights, then the program would fail to work. It is
> not reasonable to create another SQL server login id with "db_owner" right to
> this user, this would lose control on our admin work again. I am quite
> frustrated by this... can anyone help?
> Thanks a lot.
> Ivan|||Dear Aleksandar,
How to transfer logins ?
Thanks!!
"Aleksandar Grbic" wrote:
> 1. create backup on server A then restore on server B. You must transfer
> logins from server A to B
> 2. ">>this programs involves some
> > actions of deleteing and creating temp tables in the database""
> create temp tables in tembdb, not in your prod db
> OR
> add this user in db_ddladmin :(
> --
> Aleksandar Grbic
> MCDBA, Senior Database Administrator
>
> "Ivan" wrote:
> > Dear all,
> >
> > I have 2 questions:
> >
> > (1) I would like to migrate a SQL database (named "myDB") from SQL server A
> > to SQL server B. I am planning to create a new database named "myDB" in
> > server B, then go back to server A to do the backup. Then restore it in
> > server B. Is it feasible? Anyone has a better way to do it?
> >
> > (2) The second question deals with security issue and it makes me
> > frustrated. There is a database which is maintained by an end-user in my
> > company currently. He has the "db_owner" rights on this database. Now we
> > (MIS) are going to take over the admin work of this database. We will assign
> > "db_reader" & "db_writer" to him only. However, this user wrote some VB
> > programs to access this database (using ADO) and this programs involves some
> > actions of deleteing and creating temp tables in the database. If he only has
> > "db_reader" & "db_writer" rights, then the program would fail to work. It is
> > not reasonable to create another SQL server login id with "db_owner" right to
> > this user, this would lose control on our admin work again. I am quite
> > frustrated by this... can anyone help?
> > Thanks a lot.
> >
> > Ivan|||Hi Ivan,
(i) U can restore then database by backup
(ii) copy the mdf file and attach the database.
(iii) Use copy database wizard.
(iv) script the database and run the script on the server and transfer
the data.
Go to DTS and use transfer logins .
defien source from where u want to transfer the logins and destination
to where ti want to copy.
but before u tranfer u create or restore or attach the database on that
srever.
hope this hel u
from
killer|||Search Knowledgebase for
sp_help_revlogin
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ivan" <Ivan@.discussions.microsoft.com> wrote in message
news:5E32FF17-7866-40CB-A5CE-6AE5EE0FD64C@.microsoft.com...
> Dear Aleksandar,
> How to transfer logins ?
> Thanks!!
> "Aleksandar Grbic" wrote:
>> 1. create backup on server A then restore on server B. You must transfer
>> logins from server A to B
>> 2. ">>this programs involves some
>> > actions of deleteing and creating temp tables in the database""
>> create temp tables in tembdb, not in your prod db
>> OR
>> add this user in db_ddladmin :(
>> --
>> Aleksandar Grbic
>> MCDBA, Senior Database Administrator
>>
>> "Ivan" wrote:
>> > Dear all,
>> >
>> > I have 2 questions:
>> >
>> > (1) I would like to migrate a SQL database (named "myDB") from SQL server A
>> > to SQL server B. I am planning to create a new database named "myDB" in
>> > server B, then go back to server A to do the backup. Then restore it in
>> > server B. Is it feasible? Anyone has a better way to do it?
>> >
>> > (2) The second question deals with security issue and it makes me
>> > frustrated. There is a database which is maintained by an end-user in my
>> > company currently. He has the "db_owner" rights on this database. Now we
>> > (MIS) are going to take over the admin work of this database. We will assign
>> > "db_reader" & "db_writer" to him only. However, this user wrote some VB
>> > programs to access this database (using ADO) and this programs involves some
>> > actions of deleteing and creating temp tables in the database. If he only has
>> > "db_reader" & "db_writer" rights, then the program would fail to work. It is
>> > not reasonable to create another SQL server login id with "db_owner" right to
>> > this user, this would lose control on our admin work again. I am quite
>> > frustrated by this... can anyone help?
>> > Thanks a lot.
>> >
>> > Ivan|||show BOL for help, "sp_addlogin"
you must create login on server B with same SID and password as logins on
server A
see examples in Books Online
OR
see "HOW TO: Transfer Logins and Passwords Between Instances of SQL Server"
at http://support.microsoft.com/default.aspx?scid=kb;en-us;246133
Aleksandar Grbic
MCDBA, Senior Database Administrator
"Ivan" wrote:
> Dear Aleksandar,
> How to transfer logins ?
> Thanks!!
> "Aleksandar Grbic" wrote:
> > 1. create backup on server A then restore on server B. You must transfer
> > logins from server A to B
> >
> > 2. ">>this programs involves some
> > > actions of deleteing and creating temp tables in the database""
> > create temp tables in tembdb, not in your prod db
> > OR
> > add this user in db_ddladmin :(
> > --
> > Aleksandar Grbic
> > MCDBA, Senior Database Administrator
> >
> >
> > "Ivan" wrote:
> >
> > > Dear all,
> > >
> > > I have 2 questions:
> > >
> > > (1) I would like to migrate a SQL database (named "myDB") from SQL server A
> > > to SQL server B. I am planning to create a new database named "myDB" in
> > > server B, then go back to server A to do the backup. Then restore it in
> > > server B. Is it feasible? Anyone has a better way to do it?
> > >
> > > (2) The second question deals with security issue and it makes me
> > > frustrated. There is a database which is maintained by an end-user in my
> > > company currently. He has the "db_owner" rights on this database. Now we
> > > (MIS) are going to take over the admin work of this database. We will assign
> > > "db_reader" & "db_writer" to him only. However, this user wrote some VB
> > > programs to access this database (using ADO) and this programs involves some
> > > actions of deleteing and creating temp tables in the database. If he only has
> > > "db_reader" & "db_writer" rights, then the program would fail to work. It is
> > > not reasonable to create another SQL server login id with "db_owner" right to
> > > this user, this would lose control on our admin work again. I am quite
> > > frustrated by this... can anyone help?
> > > Thanks a lot.
> > >
> > > Ivansql

Sunday, February 19, 2012

Database Mail

Dear Folks,
I am trying to finish my configuration of Database Mail on my sp1 build 2153 sql server. I believe to have set it up properly but i still can't get a test message through. My error message reads:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2006-08-09T15:14:00). Exception Message: Cannot send mails to mail server. (Transaction failed. The server response was: Mail from <account email address> rejected for policy reasons.). )

Everything that needs to be enabled, i.e. service broker, SQL Server Agent properties, deafult profile in msdb table, services restarted. Is this message a result of Sql server policy or the smtp server? Is there a process in the transaction Sql server initiates that could be causing the policy violation that i need to tell the email admin about?
Thanks for your reply.
-C
This turned out to be a DNS or relay issue. When i got the right IP address, it worked fine. I tried IP previously, the same message came back. The mail server is in another domain, which complicated the issue.

Friday, February 17, 2012

database log growth too fast

Dear all

My database log growth is too fast. I want to stop this situation. I am a rookie in database administration. Please give me some suggestions. Thanks a lot.

Alex

Perhaps you are able to shrink this from time to time within a maintaince plan, or a manually setup jon in SQL Server ?

http://www.aspfaq.com/show.asp?id=2471

HTH, jens Suessmeyer.|||thx

Tuesday, February 14, 2012

Database Level trigger is possible in SQL 2000?

Dear Freinds,

we are having an requirement to log the unauthorized backend update on database.Is there any other way ,apart from going and creating the triggers at each table to log the backend update.

Note :Is Database base level trigger is possible on SQL Server 2000?

Looking for your reply.

With Cheers :beer: ,
Sathesh.MNo, database-level triggers are not implemented in 2000.

And the way to handle unauthorized backend updates is with proper security.|||Dear Blindman,

We are having all level of database security,there are 40 support people
working on this pro.,we want to track there updation also.

With Regards,
Sathesh.m|||You'll need to put triggers on the individual tables.

Every table I create contains columns for MODIFIED and MODIFIER, with an update trigger to set them to GETDATE() and SUSER_SNAME().

Database level setting : Allow user input chinese character~

Dear all,

To allow users enter chinese character into table, I did try to change the field type to nchar and this is workable. But I have few hundred tables.. Is there anyway to change the setting in easier way? by instead change the field type one by one for each table?

I tried change the collation to Chinese_PRC_90_BIN for the database, but it is not support chinese input..

Any Idea?

Thanks.

You will have to ALTER each table, changing the datatype from char()/varchar() to nchar()/nvarchar() on each column that will contain Chinese characters.

There is a undocumented system stored procedure, sp_MSForEachTable that may be useful in combination with using dynamic SQL.

There have been recent postings related to a similar issue, perhaps a search in the forums for 'sp_MSForEachTable', as well as a Google? search would also be useful.

|||

Hi,

you can use the following script to change every char / varchar column to contain unicode data. The statement will create ALTER statements which can be examined (in the case that you do not want to change every columns and want to sort out the appropiate ones)


SELECT
'ALTER TABLE [' + T.TABLE_NAME + ']' +
' ALTER COLUMN' + '[' + COLUMN_NAME + '] N' + DATA_TYPE + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(50)) + ');'
FROM [INFORMATION_SCHEMA].[COLUMNS] C
INNER JOIN [INFORMATION_SCHEMA].[TABLES] t
ON c.TABLE_SCHEMA = t.TABLE_SCHEMA AND
C.TABLE_NAME = t.TABLE_NAME
WHERE data_type IN ('CHAR','VARCHAR')
AND t.TABLE_TYPE = 'BASE TABLE'

Jens K. Suessmeyer.


http://www.sqlserver2005.de