database collation: system

Showing posts with label system. Show all posts

Thursday, March 29, 2012

Changing the Table Type

Is there a to change the Type property on a table? I would like to be able
to change the Type from 'User' to 'System' through code.
Adv-thanks-anceWhat do you need this for?
True system tables have object id values less than 100, and there is no way
you can create a table with such an id.
If you are only talking about what shows up in the 'type' column when you
list the objects in Enterprise Manager, you can run the procedure
exec sp_MS_marksystemobject 'mytable'
However, it would not actually be a system table, even though Enterprise
Manager lists it as such. For example, it would still show 'user table' when
using sp_help, and you would not need to set any special flags to
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
news:A946CD68-5A88-4E55-B813-4D1ADBF49DA6@.microsoft.com...
> Is there a to change the Type property on a table? I would like to be
> able
> to change the Type from 'User' to 'System' through code.
> Adv-thanks-ance
>

Changing the system (master) collation setting in SQL Server 2005 Express Edition

Does anyone know how to do the above without having to re-install SQL Server?

Cheers

Simon.

This is documented in the Books Online Topic located at ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3242deef-6f5f-4051-a121-36b3b4da851d.htm or on MSDN at http://msdn2.microsoft.com/en-us/library/ms179254(en-US,SQL.90).aspx

Cheers,
Dan

Tuesday, March 27, 2012

Changing the ReportViewer control size.

I have an aspx page that uses the ReportViewer control. The dimensions are
set at runtime.
private void Page_Load(object sender, System.EventArgs e)
{
reportViewer.ServerUrl = "http://localhost/reportserver";
reportViewer.ReportPath = "/Samples/Company Sales";
reportViewer.Width = 900;
reportViewer.Height = 580;
}
Is it possible to change the size of the control everytime the browser
window is resized?
TIA.You can set the size to a percent.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"TechnoSpyke" <technospyke@.yahoo.com> wrote in message
news:OqzqmWrEFHA.2572@.tk2msftngp13.phx.gbl...
>I have an aspx page that uses the ReportViewer control. The dimensions are
>set at runtime.
> private void Page_Load(object sender, System.EventArgs e)
> {
> reportViewer.ServerUrl = "http://localhost/reportserver";
> reportViewer.ReportPath = "/Samples/Company Sales";
> reportViewer.Width = 900;
> reportViewer.Height = 580;
> }
> Is it possible to change the size of the control everytime the browser
> window is resized?
> TIA.
>|||Thank you.
This worked, but first I had to modify the ReportViewer.dll since, by
default, it doesn't accept precentage for dimensions.
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:ekIm4R5EFHA.3200@.TK2MSFTNGP10.phx.gbl...
> You can set the size to a percent.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "TechnoSpyke" <technospyke@.yahoo.com> wrote in message
> news:OqzqmWrEFHA.2572@.tk2msftngp13.phx.gbl...
>>I have an aspx page that uses the ReportViewer control. The dimensions
>>are set at runtime.
>> private void Page_Load(object sender, System.EventArgs e)
>> {
>> reportViewer.ServerUrl = "http://localhost/reportserver";
>> reportViewer.ReportPath = "/Samples/Company Sales";
>> reportViewer.Width = 900;
>> reportViewer.Height = 580;
>> }
>> Is it possible to change the size of the control everytime the browser
>> window is resized?
>> TIA.
>|||Great, I'm glad you got it working. Sometimes I forget all the steps when I
did something, but just remember that it works!!
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"TechnoSpyke" <technospyke@.yahoo.com> wrote in message
news:eoca6W5EFHA.2600@.TK2MSFTNGP09.phx.gbl...
> Thank you.
> This worked, but first I had to modify the ReportViewer.dll since, by
> default, it doesn't accept precentage for dimensions.
>
> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> news:ekIm4R5EFHA.3200@.TK2MSFTNGP10.phx.gbl...
>> You can set the size to a percent.
>> --
>> Cheers,
>> '(' Jeff A. Stucker
>> \
>> Business Intelligence
>> www.criadvantage.com
>> ---
>> "TechnoSpyke" <technospyke@.yahoo.com> wrote in message
>> news:OqzqmWrEFHA.2572@.tk2msftngp13.phx.gbl...
>>I have an aspx page that uses the ReportViewer control. The dimensions
>>are set at runtime.
>> private void Page_Load(object sender, System.EventArgs e)
>> {
>> reportViewer.ServerUrl = "http://localhost/reportserver";
>> reportViewer.ReportPath = "/Samples/Company Sales";
>> reportViewer.Width = 900;
>> reportViewer.Height = 580;
>> }
>> Is it possible to change the size of the control everytime the browser
>> window is resized?
>> TIA.
>>
>

Changing the MSSQLServer service account causes SQL Agent could not start

Hi...a SQL2005 enterprise under W2K3 server. All the SQL services are
running either a domain account or local system account, i.e.
MSSQLServer service is under a domain account, SQL Agent is under
local system.
For a reason, we need to switch the domain account to the other domain
account, the MSSQLServer service is started up with no problem but the
SQL Agent is not able to start up neither in local system or new
domain account, the error message is: "The SQL Server Agent
(MSSQLSERVER) service on Local Computer started and then stop
automatically if they have no work to do, for example, the Performance
Logs and Alerts service. "
Switch back to original domain account is fine, let the MSSQLServer
running on local system account is fine too for both MSSQLServer and
SQL Agent.
P.S. Both old/new domain accounts are in the server's local admin and
domain administrator groups and as well as SQL sa.
What is needed to use a domain account as the SQL service account?
Thanks,
Yvette.
Hi Yvette
"yvette.ye@.gmail.com" wrote:

> Hi...a SQL2005 enterprise under W2K3 server. All the SQL services are
> running either a domain account or local system account, i.e.
> MSSQLServer service is under a domain account, SQL Agent is under
> local system.
> For a reason, we need to switch the domain account to the other domain
> account, the MSSQLServer service is started up with no problem but the
> SQL Agent is not able to start up neither in local system or new
> domain account, the error message is: "The SQL Server Agent
> (MSSQLSERVER) service on Local Computer started and then stop
> automatically if they have no work to do, for example, the Performance
> Logs and Alerts service. "
> Switch back to original domain account is fine, let the MSSQLServer
> running on local system account is fine too for both MSSQLServer and
> SQL Agent.
> P.S. Both old/new domain accounts are in the server's local admin and
> domain administrator groups and as well as SQL sa.
> What is needed to use a domain account as the SQL service account?
> Thanks,
> Yvette.
>
For the account types that can be used see
http://support.microsoft.com/kb/907557
and http://support.microsoft.com/kb/283811 describes the requirements for
the accounts if you don't use EM or SCM to change the account.
John
|||"John Bell" wrote:

> Hi Yvette
> For the account types that can be used see
> http://support.microsoft.com/kb/907557
> and http://support.microsoft.com/kb/283811 describes the requirements for
> the accounts if you don't use EM or SCM to change the account.
> John
Also try http://msdn2.microsoft.com/en-us/library/ms143504.aspx
John

Changing the MSSQLServer service account causes SQL Agent could not start

Hi...a SQL2005 enterprise under W2K3 server. All the SQL services are
running either a domain account or local system account, i.e.
MSSQLServer service is under a domain account, SQL Agent is under
local system.
For a reason, we need to switch the domain account to the other domain
account, the MSSQLServer service is started up with no problem but the
SQL Agent is not able to start up neither in local system or new
domain account, the error message is: "The SQL Server Agent
(MSSQLSERVER) service on Local Computer started and then stop
automatically if they have no work to do, for example, the Performance
Logs and Alerts service. "
Switch back to original domain account is fine, let the MSSQLServer
running on local system account is fine too for both MSSQLServer and
SQL Agent.
P.S. Both old/new domain accounts are in the server's local admin and
domain administrator groups and as well as SQL sa.
What is needed to use a domain account as the SQL service account?
Thanks,
Yvette.Hi Yvette
"yvette.ye@.gmail.com" wrote:
> Hi...a SQL2005 enterprise under W2K3 server. All the SQL services are
> running either a domain account or local system account, i.e.
> MSSQLServer service is under a domain account, SQL Agent is under
> local system.
> For a reason, we need to switch the domain account to the other domain
> account, the MSSQLServer service is started up with no problem but the
> SQL Agent is not able to start up neither in local system or new
> domain account, the error message is: "The SQL Server Agent
> (MSSQLSERVER) service on Local Computer started and then stop
> automatically if they have no work to do, for example, the Performance
> Logs and Alerts service. "
> Switch back to original domain account is fine, let the MSSQLServer
> running on local system account is fine too for both MSSQLServer and
> SQL Agent.
> P.S. Both old/new domain accounts are in the server's local admin and
> domain administrator groups and as well as SQL sa.
> What is needed to use a domain account as the SQL service account?
> Thanks,
> Yvette.
>
For the account types that can be used see
http://support.microsoft.com/kb/907557
and http://support.microsoft.com/kb/283811 describes the requirements for
the accounts if you don't use EM or SCM to change the account.
John|||"John Bell" wrote:
> Hi Yvette
> For the account types that can be used see
> http://support.microsoft.com/kb/907557
> and http://support.microsoft.com/kb/283811 describes the requirements for
> the accounts if you don't use EM or SCM to change the account.
> John
Also try http://msdn2.microsoft.com/en-us/library/ms143504.aspx
John

Changing the Logical/Physical name of a DB, or log

Sunday, March 25, 2012

Changing the Logical/Physical name of a DB, or log

The system I'm working with has many databases, almost all of which have a
logical name of "DBname_Data" and "DBname_Log" which have physical names if
"DBname_Data.MDF" and "DBname_Log.LDF".
All except one that is. It is named "DBname_dat" & "DBname_dat.MDF".
What do I have to do to change this, so it is consistant with all the others?
Thanks,
JayJust thought to add that I know how to do it with a backup and a restore with
move. Hoping for simpler.
"JayKon" wrote:
> The system I'm working with has many databases, almost all of which have a
> logical name of "DBname_Data" and "DBname_Log" which have physical names if
> "DBname_Data.MDF" and "DBname_Log.LDF".
> All except one that is. It is named "DBname_dat" & "DBname_dat.MDF".
> What do I have to do to change this, so it is consistant with all the others?
> Thanks,
> Jay|||try alter database with the modify file portion:
e.g, --> MODIFY FILE ( NAME = logical_file_name, FILENAME = '
new_path/os_file_name ' )
BOL does a good job of explaining this so just look under 'alter database'
Robert Towne
"JayKon" wrote:
> Just thought to add that I know how to do it with a backup and a restore with
> move. Hoping for simpler.
> "JayKon" wrote:
> > The system I'm working with has many databases, almost all of which have a
> > logical name of "DBname_Data" and "DBname_Log" which have physical names if
> > "DBname_Data.MDF" and "DBname_Log.LDF".
> >
> > All except one that is. It is named "DBname_dat" & "DBname_dat.MDF".
> >
> > What do I have to do to change this, so it is consistant with all the others?
> >
> > Thanks,
> > Jay|||Yes, it does.
Thank you.
"sql411@.nospam.com" wrote:
> try alter database with the modify file portion:
> e.g, --> MODIFY FILE ( NAME = logical_file_name, FILENAME = '
> new_path/os_file_name ' )
> BOL does a good job of explaining this so just look under 'alter database'
> Robert Towne
>
>
> "JayKon" wrote:
> > Just thought to add that I know how to do it with a backup and a restore with
> > move. Hoping for simpler.
> >
> > "JayKon" wrote:
> >
> > > The system I'm working with has many databases, almost all of which have a
> > > logical name of "DBname_Data" and "DBname_Log" which have physical names if
> > > "DBname_Data.MDF" and "DBname_Log.LDF".
> > >
> > > All except one that is. It is named "DBname_dat" & "DBname_dat.MDF".
> > >
> > > What do I have to do to change this, so it is consistant with all the others?
> > >
> > > Thanks,
> > > Jay

Tuesday, March 20, 2012

Changing table names. Navision 3.7 - need help

Hello
I am trying to create a report set for a company with changing table names. Meaning that for each company the erp system is in operation for, the table names are different.
Mening that for one company the GL table is named like this:
[Company X$GL] while for another company the GL table is like this:
[Company Y$GL]
Should I use a stored procedure to handle it, a function... any good ideas before I start experimenting..
I have the possibility of getting the company name from the system, so thats not a problem...
hope for some good advice.David,
You could acheive this by using two reports; The first would get the appropriate company/table name, this report would contain a sub-report, passing the company/table name as a parameter. The sub-report would use a dynamic query, built from the parameter, such as;
="Select * From " & Parameter!Company.Value
You may be able to do it from one report, depending on the order in which the datasets are handled.
Hope that helps
"david" wrote:
> Hello
> I am trying to create a report set for a company with changing table names. Meaning that for each company the erp system is in operation for, the table names are different.
> Mening that for one company the GL table is named like this:
> [Company X$GL] while for another company the GL table is like this:
> [Company Y$GL]
> Should I use a stored procedure to handle it, a function... any good ideas before I start experimenting..
> I have the possibility of getting the company name from the system, so thats not a problem...
> hope for some good advice.|||Terribly sorry for having bothered you all. I was missing a white space in my table name.... *ashamed beyond belief*
"david" wrote:
> Now I tried going another way. I found a text replacing program, that simply runs through all the rdl files and replaces the company X string with the company Y string, so as to fit the database tables names.
> Now when i open the reports and try to open a dataset in the data view:
> all tables are flat, and i can only mark the All columns. When I try running the command, i get the following error:
> ADO error: Invalid object name 'compname$Value Entry'.
> Invalid object name 'Compname@.Customer'.
> Staement(s) could not be prepared. Deferred prepare could not be completed.
>
> heeeeeeeelp
> "david" wrote:
> > Thanks for the reply.
> > I will elaborate a little more on the problem.
> >
> > a Select to get all accounts in account ledger is for example:
> >
> > SELECT No_, Name
> > FROM [Company X$G_L Account]
> >
> > There fore i need to concatenate the company name with the table name. And table names may have blank spaces, so I need the [ ]
> >
> > Besides that, in the books online for reporting services it specifically says that everything need to be inline, and I have som loong select statements... what 2 do?
> >
> >
> > "Chris McGuigan" wrote:
> >
> > > David,
> > > You could acheive this by using two reports; The first would get the appropriate company/table name, this report would contain a sub-report, passing the company/table name as a parameter. The sub-report would use a dynamic query, built from the parameter, such as;
> > > ="Select * From " & Parameter!Company.Value
> > > You may be able to do it from one report, depending on the order in which the datasets are handled.
> > >
> > > Hope that helps
> > >
> > > "david" wrote:
> > >
> > > > Hello
> > > > I am trying to create a report set for a company with changing table names. Meaning that for each company the erp system is in operation for, the table names are different.
> > > >
> > > > Mening that for one company the GL table is named like this:
> > > > [Company X$GL] while for another company the GL table is like this:
> > > > [Company Y$GL]
> > > > Should I use a stored procedure to handle it, a function... any good ideas before I start experimenting..
> > > >
> > > > I have the possibility of getting the company name from the system, so thats not a problem...
> > > >
> > > > hope for some good advice.|||David,
The 1 line restrictions was removed with SP1. Any way by 1 line they really mean one sentence, so;
="SELECT * " &
"FROM [" & Parameters!Company.Value & " X$G_L Account]"
would work OK.
Regards
Chris McGuigan
"david" wrote:
> Thanks for the reply.
> I will elaborate a little more on the problem.
> a Select to get all accounts in account ledger is for example:
> SELECT No_, Name
> FROM [Company X$G_L Account]
> There fore i need to concatenate the company name with the table name. And table names may have blank spaces, so I need the [ ]
> Besides that, in the books online for reporting services it specifically says that everything need to be inline, and I have som loong select statements... what 2 do?
>
> "Chris McGuigan" wrote:
> > David,
> > You could acheive this by using two reports; The first would get the appropriate company/table name, this report would contain a sub-report, passing the company/table name as a parameter. The sub-report would use a dynamic query, built from the parameter, such as;
> > ="Select * From " & Parameter!Company.Value
> > You may be able to do it from one report, depending on the order in which the datasets are handled.
> >
> > Hope that helps
> >
> > "david" wrote:
> >
> > > Hello
> > > I am trying to create a report set for a company with changing table names. Meaning that for each company the erp system is in operation for, the table names are different.
> > >
> > > Mening that for one company the GL table is named like this:
> > > [Company X$GL] while for another company the GL table is like this:
> > > [Company Y$GL]
> > > Should I use a stored procedure to handle it, a function... any good ideas before I start experimenting..
> > >
> > > I have the possibility of getting the company name from the system, so thats not a problem...
> > >
> > > hope for some good advice.sql

Changing System Time on SQL Server Hardware

I'm trying to test a web-based, time and attendance
application. In order o compress the testing window, I'd
like to adjust the system time. The application pulls all
time stamps from the database server so when I need to
adjust the system time, I will be adjusting the database
server (SQL Server) time forward and backward.
I'm being told that if I adjust the system time on the
database tier (SQL Server) that I will destabilize the
database (specifically causing trouble with log files) and
my results will not be reliable. In the past while
testing a time sensitive, client/server application, I
adjusted the system time forwards and backwards without
any negative repercussions.
I'd like to know what would cause this instability, if the
information that I'm receiving is accurate and if there
are any suggested workarounds.
Changing the time should not cause any instability issues that I am aware
of. Log records are identified by their LSN (Log Sequence Number) and are
written serially. A lot of servers for example automatically change the time
for Daylight savings etc with no ill effects
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Caleb" <anonymous@.discussions.microsoft.com> wrote in message
news:1c71c01c42238$78611dc0$a501280a@.phx.gbl...
> I'm trying to test a web-based, time and attendance
> application. In order o compress the testing window, I'd
> like to adjust the system time. The application pulls all
> time stamps from the database server so when I need to
> adjust the system time, I will be adjusting the database
> server (SQL Server) time forward and backward.
> I'm being told that if I adjust the system time on the
> database tier (SQL Server) that I will destabilize the
> database (specifically causing trouble with log files) and
> my results will not be reliable. In the past while
> testing a time sensitive, client/server application, I
> adjusted the system time forwards and backwards without
> any negative repercussions.
> I'd like to know what would cause this instability, if the
> information that I'm receiving is accurate and if there
> are any suggested workarounds.

Changing System Time on SQL Server Hardware

I'm trying to test a web-based, time and attendance
application. In order o compress the testing window, I'd
like to adjust the system time. The application pulls all
time stamps from the database server so when I need to
adjust the system time, I will be adjusting the database
server (SQL Server) time forward and backward.
I'm being told that if I adjust the system time on the
database tier (SQL Server) that I will destabilize the
database (specifically causing trouble with log files) and
my results will not be reliable. In the past while
testing a time sensitive, client/server application, I
adjusted the system time forwards and backwards without
any negative repercussions.
I'd like to know what would cause this instability, if the
information that I'm receiving is accurate and if there
are any suggested workarounds.Changing the time should not cause any instability issues that I am aware
of. Log records are identified by their LSN (Log Sequence Number) and are
written serially. A lot of servers for example automatically change the time
for Daylight savings etc with no ill effects
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Caleb" <anonymous@.discussions.microsoft.com> wrote in message
news:1c71c01c42238$78611dc0$a501280a@.phx
.gbl...
> I'm trying to test a web-based, time and attendance
> application. In order o compress the testing window, I'd
> like to adjust the system time. The application pulls all
> time stamps from the database server so when I need to
> adjust the system time, I will be adjusting the database
> server (SQL Server) time forward and backward.
> I'm being told that if I adjust the system time on the
> database tier (SQL Server) that I will destabilize the
> database (specifically causing trouble with log files) and
> my results will not be reliable. In the past while
> testing a time sensitive, client/server application, I
> adjusted the system time forwards and backwards without
> any negative repercussions.
> I'd like to know what would cause this instability, if the
> information that I'm receiving is accurate and if there
> are any suggested workarounds.

Changing System Time on SQL Server Hardware

I'm trying to test a web-based, time and attendance
application. In order o compress the testing window, I'd
like to adjust the system time. The application pulls all
time stamps from the database server so when I need to
adjust the system time, I will be adjusting the database
server (SQL Server) time forward and backward.
I'm being told that if I adjust the system time on the
database tier (SQL Server) that I will destabilize the
database (specifically causing trouble with log files) and
my results will not be reliable. In the past while
testing a time sensitive, client/server application, I
adjusted the system time forwards and backwards without
any negative repercussions.
I'd like to know what would cause this instability, if the
information that I'm receiving is accurate and if there
are any suggested workarounds.Changing the time should not cause any instability issues that I am aware
of. Log records are identified by their LSN (Log Sequence Number) and are
written serially. A lot of servers for example automatically change the time
for Daylight savings etc with no ill effects
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Caleb" <anonymous@.discussions.microsoft.com> wrote in message
news:1c71c01c42238$78611dc0$a501280a@.phx.gbl...
> I'm trying to test a web-based, time and attendance
> application. In order o compress the testing window, I'd
> like to adjust the system time. The application pulls all
> time stamps from the database server so when I need to
> adjust the system time, I will be adjusting the database
> server (SQL Server) time forward and backward.
> I'm being told that if I adjust the system time on the
> database tier (SQL Server) that I will destabilize the
> database (specifically causing trouble with log files) and
> my results will not be reliable. In the past while
> testing a time sensitive, client/server application, I
> adjusted the system time forwards and backwards without
> any negative repercussions.
> I'd like to know what would cause this instability, if the
> information that I'm receiving is accurate and if there
> are any suggested workarounds.

Changing system time and Next run time for Jobs

IF you change the System time to say 1 year or even a
couple days ahead, the SQL Server Job's Next Run Time
doesn't get updated. So the jobs never run again as an
affect. The only way to fix this is to stop and restart
SQL Agent or reboot the machine.
Does anyone know what to do with this?That is expected behavior. Changing the system time has no effect on the
entries stored in tables within SQL Server.
Rand
This posting is provided "as is" with no warranties and confers no rights.sql

Changing startup-account from admin- to system account

Hi, after changing the service-startup-account from an admin-account to the system-account the service starts fine but users cannot longer connect. They use a SQL-server login to connect. Authentication is set to 'sql-server and windows' The change was do
ne from enterprise manager on the server running the service, the server was rebooted. The operating system is Small Business Server 2000.
Any help appreciated.
Toni Santa
What error do the users get? What is your AuditLevel setting? You might
want to set it to "3",. so all attempts regardless of success or failure are
logged. Can you connect via say Query Analyzer when you are logged on
locally? Can you see the SQL Server box on the network?
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Toni Santa" <Toni Santa@.discussions.microsoft.com> wrote in message
news:B07674C0-EC3A-40B5-81B5-5F313147DF49@.microsoft.com...
> Hi, after changing the service-startup-account from an admin-account to
the system-account the service starts fine but users cannot longer connect.
They use a SQL-server login to connect. Authentication is set to 'sql-server
and windows' The change was done from enterprise manager on the server
running the service, the server was rebooted. The operating system is Small
Business Server 2000.
> Any help appreciated.
> Toni Santa
|||Hi Gregory, for now I don't know this. The service-start-account has been changed by the customer. For know it has been reset to an admin-account and so he is able to work. I will check this when I pass in his office, could be next week.
My application connects via the servername? I found a thread in the SQL Server Security group where someone states that after changing some security issue of SQL Server he could connect only via IP-address. I will try this, too and take you informed. Than
ks - Toni Santa
"Gregory A. Larsen" wrote:

> What error do the users get? What is your AuditLevel setting? You might
> want to set it to "3",. so all attempts regardless of success or failure are
> logged. Can you connect via say Query Analyzer when you are logged on
> locally? Can you see the SQL Server box on the network?
> --
> ----
> ----
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Toni Santa" <Toni Santa@.discussions.microsoft.com> wrote in message
> news:B07674C0-EC3A-40B5-81B5-5F313147DF49@.microsoft.com...
> the system-account the service starts fine but users cannot longer connect.
> They use a SQL-server login to connect. Authentication is set to 'sql-server
> and windows' The change was done from enterprise manager on the server
> running the service, the server was rebooted. The operating system is Small
> Business Server 2000.
>
>

Changing startup-account from admin- to system account

Hi, after changing the service-startup-account from an admin-account to the system-account the service starts fine but users cannot longer connect. They use a SQL-server login to connect. Authentication is set to 'sql-server and windows' The change was done from enterprise manager on the server running the service, the server was rebooted. The operating system is Small Business Server 2000.
Any help appreciated.
Toni SantaWhat error do the users get? What is your AuditLevel setting? You might
want to set it to "3",. so all attempts regardless of success or failure are
logged. Can you connect via say Query Analyzer when you are logged on
locally? Can you see the SQL Server box on the network?
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Toni Santa" <Toni Santa@.discussions.microsoft.com> wrote in message
news:B07674C0-EC3A-40B5-81B5-5F313147DF49@.microsoft.com...
> Hi, after changing the service-startup-account from an admin-account to
the system-account the service starts fine but users cannot longer connect.
They use a SQL-server login to connect. Authentication is set to 'sql-server
and windows' The change was done from enterprise manager on the server
running the service, the server was rebooted. The operating system is Small
Business Server 2000.
> Any help appreciated.
> Toni Santa|||Hi Gregory, for now I don't know this. The service-start-account has been changed by the customer. For know it has been reset to an admin-account and so he is able to work. I will check this when I pass in his office, could be next week.
My application connects via the servername? I found a thread in the SQL Server Security group where someone states that after changing some security issue of SQL Server he could connect only via IP-address. I will try this, too and take you informed. Thanks - Toni Santa
"Gregory A. Larsen" wrote:
> What error do the users get? What is your AuditLevel setting? You might
> want to set it to "3",. so all attempts regardless of success or failure are
> logged. Can you connect via say Query Analyzer when you are logged on
> locally? Can you see the SQL Server box on the network?
> --
> ----
> ----
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Toni Santa" <Toni Santa@.discussions.microsoft.com> wrote in message
> news:B07674C0-EC3A-40B5-81B5-5F313147DF49@.microsoft.com...
> > Hi, after changing the service-startup-account from an admin-account to
> the system-account the service starts fine but users cannot longer connect.
> They use a SQL-server login to connect. Authentication is set to 'sql-server
> and windows' The change was done from enterprise manager on the server
> running the service, the server was rebooted. The operating system is Small
> Business Server 2000.
> > Any help appreciated.
> > Toni Santa
>
>

Changing startup-account from admin- to system account

Hi, after changing the service-startup-account from an admin-account to the
system-account the service starts fine but users cannot longer connect. They
use a SQL-server login to connect. Authentication is set to 'sql-server and
windows' The change was do
ne from enterprise manager on the server running the service, the server was
rebooted. The operating system is Small Business Server 2000.
Any help appreciated.
Toni SantaWhat error do the users get? What is your AuditLevel setting? You might
want to set it to "3",. so all attempts regardless of success or failure are
logged. Can you connect via say Query Analyzer when you are logged on
locally? Can you see the SQL Server box on the network?
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Toni Santa" <Toni Santa@.discussions.microsoft.com> wrote in message
news:B07674C0-EC3A-40B5-81B5-5F313147DF49@.microsoft.com...
> Hi, after changing the service-startup-account from an admin-account to
the system-account the service starts fine but users cannot longer connect.
They use a SQL-server login to connect. Authentication is set to 'sql-server
and windows' The change was done from enterprise manager on the server
running the service, the server was rebooted. The operating system is Small
Business Server 2000.
> Any help appreciated.
> Toni Santa|||Hi Gregory, for now I don't know this. The service-start-account has been ch
anged by the customer. For know it has been reset to an admin-account and so
he is able to work. I will check this when I pass in his office, could be n
ext week.
My application connects via the servername? I found a thread in the SQL Serv
er Security group where someone states that after changing some security iss
ue of SQL Server he could connect only via IP-address. I will try this, too
and take you informed. Than
ks - Toni Santa
"Gregory A. Larsen" wrote:

> What error do the users get? What is your AuditLevel setting? You might
> want to set it to "3",. so all attempts regardless of success or failure a
re
> logged. Can you connect via say Query Analyzer when you are logged on
> locally? Can you see the SQL Server box on the network?
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Toni Santa" <Toni Santa@.discussions.microsoft.com> wrote in message
> news:B07674C0-EC3A-40B5-81B5-5F313147DF49@.microsoft.com...
> the system-account the service starts fine but users cannot longer connect
.
> They use a SQL-server login to connect. Authentication is set to 'sql-serv
er
> and windows' The change was done from enterprise manager on the server
> running the service, the server was rebooted. The operating system is Smal
l
> Business Server 2000.
>
>

Changing SQL Service Account

Has anyone ever converted from running SQL Server under the Local System account to running under a Domain User account?

I have often installed SQL using a Domain User account, but I am inheriting a couple of SQL Servers that were set up to run under Local System. I have never had to convert "on the fly" before.

If you have any input or insights, I would be grateful.

Regards,

hmscottI have done this several times, and have had no issues (knock on wood). Make sure that the Domain account has sufficient permissions on the local machine, and you shold be ok.|||Is "Power User" sufficient, or do I have to grant local admin to the account?

Regards,

hmscott|||If any of the jobs on the local involves deleting/creating files then better to give admin and its no harm is allocating this privilege for SQL service accounts.|||Originally posted by Satya
If any of the jobs on the local involves deleting/creating files then better to give admin and its no harm is allocating this privilege for SQL service accounts.

8-O

That's wrong! Basic tenet of security is least privileges of course. Required permissions are outlined in this article:

http://support.microsoft.com/?id=283811

Quote from the article: "...running SQL Server under such high user rights is not recommended."|||No such threat at our end, so far so good.
It purely depend how you secure the network and connections.|||Due to the nature of what our SQL Servers do, we make most of the machines run as LocalSystem. We basically make each machine run with the lowest level of privledge that it needs to do its job.

We do have one machine that is our interface/automation server that does all kinds of things like copying data from one server to another, runs DTS packages that affect multiple machines, etc that has privleges similar to a Domain Admin (because it must touch nearly every machine in the Data Center). Only the Domain Admins and a few select IT staff can even see this box, much less touch it!

-PatP

Monday, March 19, 2012

Changing SID of a USER

Hello can anybody out there help.

I am just getting ready to move a system to production using SQL 2005 and SQL Mirroring. The problem that i have is when the databases fails over my connection string user account losses its rights on the DB untill i run the SP_CHANGE_USERS_LOGIN script. I have been trying to come up with a method of getting around this so far the only fix that i have is to make my user account sysadmin but this is far from ideal for production, does anyone have any ideas i was thinking that changing the SID of the User so that they match might get over the problem.

Any help will be good

Matt.

Looks like the logins that map to those users are created with different SIDs in the mirrored instance. You should recreate them on the mirror using the same SID they have on the principal. Have a look at the SID clause of CREATE LOGIN.

Thanks
Laurentiu

|||

Thanks that has sorted the problem.

Matt

Friday, February 24, 2012

Changing login permissions

I am trying to set up a secure system and would like some advice on how to do it as it is a bit tricky.

The underlying security is Windows Authentication but I need to set various permission levels in the application.
What I want to do is to allow users read-only access to a users table. Once they are validated and their permission level is determined, then I want them to be assigned to the role that is set for their permission level.

I have looked at Application Roles but when I try to set the role up using the sp_setapprole then I get network errors (odd). There does not seem to be an SP which assigns a user to a role.

So I do I go about this?

Many thanks for your help.
Ian Logan

You can add a user to a role using sp_addrolemember.
The errors that you get for sp_setapprole are strange. Do you also get them when you're connected locally? Can you post these errors?

Thanks
Laurentiu|||

Can you please clarify how users are validated in your application? Is it handled by your aplication or SQLServer? If this is the latter, then you can assign permissions in advance to windows groups and your windows users who are the members of those groups will get those permissions upon logon to SQLserver. If this is former (i.e. you have applicatoin users, rather than windows or SQLServer principals) then you can
1) provide set of wrappers (such as table-valued functions) to access your data, which take application user name as an argument and have security logic inside; or
2) you can create a set of SQLServer users with necessary permission granted to them, map your application users to them using "EXECUTE AS user" feature on the TSQL procedure level or inside your application.

You can use sp_setapprole for 2, but I" believe it can be fully replaced by "EXECUTE AS"
sp_addrolemember is also available to add users to db roles, but itis not recommended to call it during your application on permanent basis, both due to security and reformance reasons.

|||

I am passing the Role Name and the Password to sp_setapprole. The role does exist in the database and it has permissions set for the various objects that I want to use.

I seem to be able to run sp_setapprole from the application, but as soon as I try to access the database again then the following error arises:

"System.Data.SqlClient.SqlException: General Network Error. Check your network documentation"

Note that I am using the Microsoft.Practices.EnterpriseLibrary.Data application block for all database interfacing. The application is obviously .NET and it is SQL 2000.

Note also that the code I am using works fine if I do not use sp_setapprole, i.e. the user has Windows Authentication and Public rights. (I have yet to REVOKE ALL FROM PUBLIC).

Many Thanks
Ian

Code:

Public Shared Function SetDBRole() As String

' Create the Database object, using the default database service. The

' default database service is determined through configuration.

Dim db As Database = DatabaseFactory.CreateDatabase()

Dim sqlCommand As String = "sp_setapprole"

Dim dbCommandWrapper As DBCommandWrapper = db.GetStoredProcCommandWrapper(sqlCommand)

' Add paramters

' Input parameters can specify the input value

dbCommandWrapper.AddInParameter("@.RoleName", DbType.String, "MyAppRole")

dbCommandWrapper.AddInParameter("@.Password", DbType.String, "MyPassword")

db.ExecuteNonQuery(dbCommandWrapper)

End Function

|||Ruslan

The users are to be validated by the application. However Windows Authentication is being used to allow them access to SQL Server in the first place. The users must logon separately to the application using a different user name and password from their Windows logon.

The application requires three tiers of user access (data entry, supervisor, etc) and the users of the application, plus their access level, are set up within the application.

The plan would be that then a new user is created then sp_addrolemember would be used to add then to the role. Then when they log in the users table would be looked up to determine their access level (role) and then sp_setapprole would be run to set their permissions.
Initially users would only have read-only access to the users table and no access to anything else. Actually, to be pedantic, nobody will have access to tables as I am using stored procedures for data access.

As for EXECUTE AS, I am using SQL 2000 and have not come across this. Is is 2005?

Kind Regards
Ian|||Ian, I don't understand why you both add users to a role and also calling sp_setapprole. I expect you could just use roles, have permissions assigned to them as appropriate, and then just have the users added to the appropriate role. The users would not be granted any permission directly, they would get their permissions from the role that they belong to. Wouldn't this address your security requirements?

Thanks
Laurentiu

PS: Yes, EXECUTE AS is a SQL 2005 feature.|||Laurentiu

Users would be added to a role when they were created within the application. However when they log in to the application then how is their role activated? I thought that you had to use sp_setapprole to do this.

As you may note from other parts to this thread, I am having problems using sp_setapprole. When I run it from SQL Query Analyser ( sp_setapprole "MyRole", "mypassword" ) then I get Msg 2762 saying that it has been invoked incorrectly. When I run it from .NET then I get the odd network error message as described in this thread.

I reckon that if I can get sp_setapprole to work then I should be there...

Kind Regards
Ian

|||

I've tried sp_setapprole on SQL 2000, and I would get error 2762 whenever I would try to set the approle again after it was already set. You can verify whether the approle is already set by executing:

select user_name()

This will return the approle name if it is already set.

If a SQL user is a member of a SQL database role, the role membership takes effect when the user connects to the database.

I still don't understand how authentication and authorization is handled by your app. You mentioned that:

"The users are to be validated by the application. However Windows Authentication is being used to allow them access to SQL Server in the first place. The users must logon separately to the application using a different user name and password from their Windows logon."

Does the app connect to SQL Server using the user's Windows credentials or by using some other Windows credentials? How are the users that you are adding to roles related to the users that connect to the app? Could you explain the steps that are involved when a person that uses the app logs in to it?

Thanks
Laurentiu

|||Laurentiu

I have discovered an article which explains some of the problems with sp_setapprole. It is 229564, and basically the connection pooling has to be disabled for sp_setapprole. Also, another post indicated that running sp_setapprole in SQL Query does not work, I think it was again due to connection issues. This looks like there may be a problem because connection pooling is useful and switching it off is a backward step.

This instance of SQL Server has only Windows Authentication.

1. The app connects using Windows Authentication.
2. On clicking OK on the app's Login, a user will be validated against a Users table in the database. This table will also contain their access level (1-3).
3. They will then be allocated one of three roles, based on their access level.

The user name and password in this table will be quite independent of their Windows user name and password as we want to make this as secure as possible. (We also need to encrypt at least one table as well - but that is another story).

The app will control the setting up of the users for the Users table. Only a superuser of the app will be able to do this. They will enter the user name, password and access level, and these will be stored in the Users table.

Kind regards
Ian
|||

I think I begin to understand. When you say roles, you really mean application roles, right, not database roles? You allocate a user to a role by calling sp_setapprole. I thought you were using both database roles and application roles.

I'll try to see if I can find anything else related to the limitations of using sp_setapprole with connection pooling.

Thanks
Laurentiu

|||Laurentiu

Any further thoughts on the connection pooling issue?

Kind Regards
Ian|||I inquired and there is no workaround on SQL Server 2000 to make sp_setapprole work with connection pooling.

Thanks
Laurentiu|||OK, many thanks. I will raise the connection pooling issue in another forum as I have some further questions about it.

Kind Regards
Ian Logan Smile

Friday, February 10, 2012

Changing data directory

How to change data directory for all databeses including system ones
(master, temdb etc.)
Can't do it by update sysfiles because (I can't figure why) this object
cannot be modified -
Regards
Piotr
Inidivisual database names can be changed via detaching and attaching,
chaging the path of the system databases needs a registry hack for that,
because the service will start them.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ MSSQLServer\Parameters
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"piotr" <ksswd@.poczta.fm> schrieb im Newsbeitrag
news:eqXDppacFHA.2736@.TK2MSFTNGP12.phx.gbl...
> How to change data directory for all databeses including system ones
> (master, temdb etc.)
> Can't do it by update sysfiles because (I can't figure why) this object
> cannot be modified -
> Regards
> Piotr
>
|||In the registry I can only change master database path and I can't detach
model, tempdb nor msdb databases
Piotr
Uytkownik "Jens Smeyer"
<Jens@.Remove_this_For_Contacting.sqlserver2005.de> napisa w wiadomoci
news:%23YK9K2acFHA.2756@.tk2msftngp13.phx.gbl...
> Inidivisual database names can be changed via detaching and attaching,
> chaging the path of the system databases needs a registry hack for that,
> because the service will start them.
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ MSSQLServer\Parameters
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "piotr" <ksswd@.poczta.fm> schrieb im Newsbeitrag
> news:eqXDppacFHA.2736@.TK2MSFTNGP12.phx.gbl...
>
|||hi Piotr,
piotr wrote:
> In the registry I can only change master database path and I can't
> detach model, tempdb nor msdb databases
>
please have a look at http://support.microsoft.com/kb/224071/EN-US/
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Finally made it, thanks a lot.
Uytkownik "Andrea Montanari" <andrea.sqlDMO@.virgilio.it> napisa w
wiadomoci news:3has31Ffpu70U1@.individual.net...
> hi Piotr,
> piotr wrote:
> please have a look at http://support.microsoft.com/kb/224071/EN-US/
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>

Thursday, March 29, 2012

Changing the Table Type

Changing the system (master) collation setting in SQL Server 2005 Express Edition

Tuesday, March 27, 2012

Changing the ReportViewer control size.

Changing the MSSQLServer service account causes SQL Agent could not start

Changing the MSSQLServer service account causes SQL Agent could not start

Changing the Logical/Physical name of a DB, or log

Sunday, March 25, 2012

Changing the Logical/Physical name of a DB, or log

Changing the Logical/Physical name of a DB, or log

Tuesday, March 20, 2012

Changing table names. Navision 3.7 - need help

Changing System Time on SQL Server Hardware

Changing System Time on SQL Server Hardware

Changing System Time on SQL Server Hardware

Changing system time and Next run time for Jobs

Changing startup-account from admin- to system account

Changing startup-account from admin- to system account

Changing startup-account from admin- to system account

Changing SQL Service Account

Monday, March 19, 2012

Changing SID of a USER

Friday, February 24, 2012

Changing login permissions

Friday, February 10, 2012

Changing data directory

database collation

Blog Archive

About Me