Hi;
We have a program (ASP.NET) that requires a database as it's back end. We
are trying to create an install that requires as little expertise as
possible. In other words, no DBA required.
For creating the database itself we are at that point. We use the registry
to find the location of osql.exe and use that to run the schema that creates
the database. I'ld prefer an API we could call so we can more cleanly handle
errors but this works fine 98% of the time.
The remaining problem is ownership of the created database.
1) Is there a way (in .NET 2.0) to query if the database is mixed mode
authentication.
2) And if it is a way to both get a list of all users
3) And to create a user?
We can already enum all domain users. So with the above we could give them a
list of all users they can choose from as the owner and also let then create
a new one - without the user ever having to run any SqlServer tool or even
have any installed.
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm> The remaining problem is ownership of the created database.
> 1) Is there a way (in .NET 2.0) to query if the database is mixed mode
> authentication.
It is SERVER property not a databases
SELECT SERVERPROPERTY('IsIntegratedSecurityOnly
') AS
[IsIntegratedSecurityOnly]
> 2) And if it is a way to both get a list of all users
EXEC northwind..sp_helpuser
> 3) And to create a user?
Create Database mydb
go
use mydb
go
sp_addlogin 'mydbuser','monitor','mydb'
go
sp_adduser 'mydbuser'
go
sp_Addrolemember 'db_datawriter','mydbuser'
go
sp_Addrolemember 'db_datareader','mydbuser'
go
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:25882AC6-F4EF-422A-8B68-63ABE75AACF3@.microsoft.com...
> Hi;
> We have a program (ASP.NET) that requires a database as it's back end. We
> are trying to create an install that requires as little expertise as
> possible. In other words, no DBA required.
> For creating the database itself we are at that point. We use the registry
> to find the location of osql.exe and use that to run the schema that
> creates
> the database. I'ld prefer an API we could call so we can more cleanly
> handle
> errors but this works fine 98% of the time.
> The remaining problem is ownership of the created database.
> 1) Is there a way (in .NET 2.0) to query if the database is mixed mode
> authentication.
> 2) And if it is a way to both get a list of all users
> 3) And to create a user?
> We can already enum all domain users. So with the above we could give them
> a
> list of all users they can choose from as the owner and also let then
> create
> a new one - without the user ever having to run any SqlServer tool or even
> have any installed.
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> Cubicle Wars - http://www.windwardreports.com/film.htm
>|||Hi David,
I am afraid that the database may have some synchronous problem now. Our
yesterday's replies cannot be seen from Web and we also cannot see your
replies.
So I post it again from Outlook Express and hope you could see it now. Sorry
for bringing you any inconvenience.
I understand that your application used osql.exe to create the database and
you have three questions on the ownership of the created database now:
1. How to query (in .NET 2.0) if the database is with mixed authentication
mode?
2. How to get a list of all users?
3. How to create a user?
If I have misunderstood, please let me know.
For your three questions and even your creating database function, you can
fully resolve the questions by using the SQL Server SMO component for .NET
2.0.
1. You can just create a Server object like this:
//Server Name
string strConn = "(local)";
//Instantiate SMO Server Object
Server svr = new Server(strConn);
Console.Writeline(svr.Settings.LoginMode.ToString());
2. To get the list of all users, you can use:
Database db = server.Databases["your_db_name"];
UserCollection users = db.Users;
3. To create a user, you can use:
//Instantiate SMO Login object
Login l = new Login(svr, loginName);
//If Login doesn't already exist
if (!svr.Logins.Contains(loginName))
{
//Login should be of type Sql Login
l.LoginType = LoginType.SqlLogin;
//Create the Login on the SQL Server with password: pa$$w0rd
l.Create("pa$$w0rd");
//Add the login to the sysadmin role
l.AddToRole("sysadmin");
}
//Instantiate a new database object
Database db = new Database(svr, "Fizoo2");
//Make SQL Server create the database
db.Create();
//Instantiate a new User object
User u = new User(db, "SQL_Login_user");
//associated it with the login "SQL_Login"
u.Login = loginName;
//Make SQL Server create the user
u.Create();
For more information, you can refer to the following references:
User Privileges View & Create User Tool
http://forums.microsoft.com/MSDN/Sh...840637&SiteID=1
How to: Create a Visual C# SMO Project in Visual Studio .NET
http://msdn2.microsoft.com/it-it/library/ms162129.aspx
How to: Modify SQL Server Settings in Visual Basic .NET
http://msdn2.microsoft.com/en-us/library/ms162131.aspx
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:25882AC6-F4EF-422A-8B68-63ABE75AACF3@.microsoft.com...
> Hi;
> We have a program (ASP.NET) that requires a database as it's back end. We
> are trying to create an install that requires as little expertise as
> possible. In other words, no DBA required.
> For creating the database itself we are at that point. We use the registry
> to find the location of osql.exe and use that to run the schema that
> creates
> the database. I'ld prefer an API we could call so we can more cleanly
> handle
> errors but this works fine 98% of the time.
> The remaining problem is ownership of the created database.
> 1) Is there a way (in .NET 2.0) to query if the database is mixed mode
> authentication.
> 2) And if it is a way to both get a list of all users
> 3) And to create a user?
> We can already enum all domain users. So with the above we could give them
> a
> list of all users they can choose from as the owner and also let then
> create
> a new one - without the user ever having to run any SqlServer tool or even
> have any installed.
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> Cubicle Wars - http://www.windwardreports.com/film.htm
>|||Hi Dave,
I understand that your application used osql.exe to create the database and
you have three questions on the ownership of the created database now:
1. How to query (in .NET 2.0) if the database is with mixed authentication
mode?
2. How to get a list of all users?
3. How to create a user?
If I have misunderstood, please let me know.
For your three questions and even your creating database function, you can
fully resolve the questions by using the SQL Server SMO component for .NET
2.0.
1. You can just create a Server object like this:
//Server Name
string strConn = "(local)";
//Instantiate SMO Server Object
Server svr = new Server(strConn);
Console.Writeline(svr.Settings.LoginMode.ToString());
2. To get the list of all users, you can use:
Database db = server.Databases["your_db_name"];
UserCollection users = db.Users;
3. To create a user, you can use:
//Instantiate SMO Login object
Login l = new Login(svr, loginName);
//If Login doesn't already exist
if (!svr.Logins.Contains(loginName))
{
//Login should be of type Sql Login
l.LoginType = LoginType.SqlLogin;
//Create the Login on the SQL Server with password: pa$$w0rd
l.Create("pa$$w0rd");
//Add the login to the sysadmin role
l.AddToRole("sysadmin");
}
//Instantiate a new database object
Database db = new Database(svr, "Fizoo2");
//Make SQL Server create the database
db.Create();
//Instantiate a new User object
User u = new User(db, "SQL_Login_user");
//associated it with the login "SQL_Login"
u.Login = loginName;
//Make SQL Server create the user
u.Create();
For more information, you can refer to the following references:
User Privileges View & Create User Tool
http://forums.microsoft.com/MSDN/Sh...840637&SiteID=1
How to: Create a Visual C# SMO Project in Visual Studio .NET
http://msdn2.microsoft.com/it-it/library/ms162129.aspx
How to: Modify SQL Server Settings in Visual Basic .NET
http://msdn2.microsoft.com/en-us/library/ms162131.aspx
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============
Showing posts with label hiwe. Show all posts
Showing posts with label hiwe. Show all posts
Thursday, March 29, 2012
Saturday, February 25, 2012
Autogrowth values keep changing
Hi!
We have 2 dbs on our dw-server and the autogrowth values for both the data- and logfiles on both of these dbs changes about once a month. The data autogrowth value changes from 10megs to a percentage value between 3200 and 6400 and the log-file value changes from 10 percent to a percentage value between 3200 and 6400. Resulting in huge files and filling the drive.
What am I missing here?
BR John
The value does not change on its own. Someone/process must be changing this. Perhaps, you should turn on audit to see who's responsible.|||Just did it. Created a trace with the SQL Profiler to catch the SQL Batch-events. Now we just have to wait it out...
John
|||Not a helpful reply, it can and it does.
I have now experienced the same bug myself. SQL 2005 on x64, has been running okay for months. Last month the database jumped from 3Gb to something like 60Gb, I found the autogrowth setting had switched from xxMB to 2048%. I thought I must have done this by mistake, so I shrank the DB and reset the autogrowth values.
This morning I log in, database is now 204GB and the autogrowth is set to 32768% !!! It stopped at 204Gb as there was no diskspace left. I've reset the autogrow again also set "maxsize" to 10GB.
This is definitely a bug and quite a serious one - it could quite easily take out an operational server by eating up disk space.
|||Yes this is quite serious. It stops our ETL-process because of the drive being full.
Nothing in our tracelog yet...
|||It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177
a) it only seems to affect databases upgraded from SQL 2000
b) it only manifests after the SQL server has been restarted
c) It has not been fixed in SP1 so here's hoping for SP2.
You won't see anything in the trace logs, it's a bug in the SQL engine. When it stores the settings for autogrow it does not correctly set one flag for percentage or non-percentage growth. If you restart SQL server you'll see the incorrect value/
Workarounds:
set Max size of the file to prevent the file from growing too large
turn off AutoGrow
use % based autogrow
|||Thanks!
BR John
|||HowardRichards wrote:
It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177
a) it only seems to affect databases upgraded from SQL 2000
b) it only manifests after the SQL server has been restarted
c) It has not been fixed in SP1 so here's hoping for SP2.
You won't see anything in the trace logs, it's a bug in the SQL engine. When it stores the settings for autogrow it does not correctly set one flag for percentage or non-percentage growth. If you restart SQL server you'll see the incorrect value/
Workarounds:
set Max size of the file to prevent the file from growing too large
turn off AutoGrow
use % based autogrow
I was not aware of this bug. Thanks for the correction.|||
It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177
a) it only seems to affect databases upgraded from SQL 2000
Regarding above comment ... I ran into that exact bug with a fresh install of SQL Server 2005 and SP1 on a completely new server. And it shut down my new 1 TB server!!!
Here's hoping SP2 does address it, as I have no interest in having my production servers needing that much coddling.
|||Yes - we've been hit by this as well.
We had it set to autogrow by 250MB, until after a reboot where it changed to 32000%. Unfortunately we didn't spot this until we filled up our log file disk.
The annoying thing was that SQL Server Management Studio doesn't let you change back a value that has erroneously gone to 32000%, because it complains that the value exceeds the maximum allowed!!
So you need to change it using the alter database statement:
alter database dbname modify file (name = filename, filegrowth = 10%)
Roll on SP2...
|||We just upgraded to SP2 for that reason (the fix is supposedly in there), but we're still unable to correct the value.
When attempting to change the value, the following error appears:
Value of ‘32768’ is not valid for “value’. “Value” should be between ‘Minimum’ and “Maximum’ Parameter name Value (System. Windows.Forms)
from the SQL Server 2005 SP2 fix list:
Can anyone advise if running this statement will correct this issue permanently?
alter database MyTestDB
Modify File
(name=mytestdb, filegrowth = 500 mb)
Thanks!
|||Executing an alter database statement does not appear to prevent the behavior from resurfacing.Autogrowth values keep changing
Hi!
We have 2 dbs on our dw-server and the autogrowth values for both the data- and logfiles on both of these dbs changes about once a month. The data autogrowth value changes from 10megs to a percentage value between 3200 and 6400 and the log-file value changes from 10 percent to a percentage value between 3200 and 6400. Resulting in huge files and filling the drive.
What am I missing here?
BR John
The value does not change on its own. Someone/process must be changing this. Perhaps, you should turn on audit to see who's responsible.|||Just did it. Created a trace with the SQL Profiler to catch the SQL Batch-events. Now we just have to wait it out...
John
|||Not a helpful reply, it can and it does.
I have now experienced the same bug myself. SQL 2005 on x64, has been running okay for months. Last month the database jumped from 3Gb to something like 60Gb, I found the autogrowth setting had switched from xxMB to 2048%. I thought I must have done this by mistake, so I shrank the DB and reset the autogrowth values.
This morning I log in, database is now 204GB and the autogrowth is set to 32768% !!! It stopped at 204Gb as there was no diskspace left. I've reset the autogrow again also set "maxsize" to 10GB.
This is definitely a bug and quite a serious one - it could quite easily take out an operational server by eating up disk space.
|||Yes this is quite serious. It stops our ETL-process because of the drive being full.
Nothing in our tracelog yet...
|||It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177
a) it only seems to affect databases upgraded from SQL 2000
b) it only manifests after the SQL server has been restarted
c) It has not been fixed in SP1 so here's hoping for SP2.
You won't see anything in the trace logs, it's a bug in the SQL engine. When it stores the settings for autogrow it does not correctly set one flag for percentage or non-percentage growth. If you restart SQL server you'll see the incorrect value/
Workarounds:
set Max size of the file to prevent the file from growing too large
turn off AutoGrow
use % based autogrow
|||Thanks!
BR John
|||It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177
a) it only seems to affect databases upgraded from SQL 2000
b) it only manifests after the SQL server has been restarted
c) It has not been fixed in SP1 so here's hoping for SP2.
You won't see anything in the trace logs, it's a bug in the SQL engine. When it stores the settings for autogrow it does not correctly set one flag for percentage or non-percentage growth. If you restart SQL server you'll see the incorrect value/
Workarounds:
set Max size of the file to prevent the file from growing too large
turn off AutoGrow
use % based autogrow
I was not aware of this bug. Thanks for the correction.|||
It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177
a) it only seems to affect databases upgraded from SQL 2000
Regarding above comment ... I ran into that exact bug with a fresh install of SQL Server 2005 and SP1 on a completely new server. And it shut down my new 1 TB server!!!
Here's hoping SP2 does address it, as I have no interest in having my production servers needing that much coddling.
|||Yes - we've been hit by this as well.
We had it set to autogrow by 250MB, until after a reboot where it changed to 32000%. Unfortunately we didn't spot this until we filled up our log file disk.
The annoying thing was that SQL Server Management Studio doesn't let you change back a value that has erroneously gone to 32000%, because it complains that the value exceeds the maximum allowed!!
So you need to change it using the alter database statement:
alter database dbname modify file (name = filename, filegrowth = 10%)
Roll on SP2...
|||We just upgraded to SP2 for that reason (the fix is supposedly in there), but we're still unable to correct the value.
When attempting to change the value, the following error appears:
Value of ‘32768’ is not valid for “value’. “Value” should be between ‘Minimum’ and “Maximum’ Parameter name Value (System. Windows.Forms)
from the SQL Server 2005 SP2 fix list:
Can anyone advise if running this statement will correct this issue permanently?
alter database MyTestDB
Modify File
(name=mytestdb, filegrowth = 500 mb)
Thanks!
|||Executing an alter database statement does not appear to prevent the behavior from resurfacing.Autogrowth values keep changing
Hi!
We have 2 dbs on our dw-server and the autogrowth values for both the data- and logfiles on both of these dbs changes about once a month. The data autogrowth value changes from 10megs to a percentage value between 3200 and 6400 and the log-file value changes from 10 percent to a percentage value between 3200 and 6400. Resulting in huge files and filling the drive.
What am I missing here?
BR John
The value does not change on its own. Someone/process must be changing this. Perhaps, you should turn on audit to see who's responsible.|||Just did it. Created a trace with the SQL Profiler to catch the SQL Batch-events. Now we just have to wait it out...
John
|||Not a helpful reply, it can and it does.
I have now experienced the same bug myself. SQL 2005 on x64, has been running okay for months. Last month the database jumped from 3Gb to something like 60Gb, I found the autogrowth setting had switched from xxMB to 2048%. I thought I must have done this by mistake, so I shrank the DB and reset the autogrowth values.
This morning I log in, database is now 204GB and the autogrowth is set to 32768% !!! It stopped at 204Gb as there was no diskspace left. I've reset the autogrow again also set "maxsize" to 10GB.
This is definitely a bug and quite a serious one - it could quite easily take out an operational server by eating up disk space.
|||Yes this is quite serious. It stops our ETL-process because of the drive being full.
Nothing in our tracelog yet...
|||It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177
a) it only seems to affect databases upgraded from SQL 2000
b) it only manifests after the SQL server has been restarted
c) It has not been fixed in SP1 so here's hoping for SP2.
You won't see anything in the trace logs, it's a bug in the SQL engine. When it stores the settings for autogrow it does not correctly set one flag for percentage or non-percentage growth. If you restart SQL server you'll see the incorrect value/
Workarounds:
set Max size of the file to prevent the file from growing too large
turn off AutoGrow
use % based autogrow
|||Thanks!
BR John
|||HowardRichards wrote:
It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177
a) it only seems to affect databases upgraded from SQL 2000
b) it only manifests after the SQL server has been restarted
c) It has not been fixed in SP1 so here's hoping for SP2.
You won't see anything in the trace logs, it's a bug in the SQL engine. When it stores the settings for autogrow it does not correctly set one flag for percentage or non-percentage growth. If you restart SQL server you'll see the incorrect value/
Workarounds:
set Max size of the file to prevent the file from growing too large
turn off AutoGrow
use % based autogrow
I was not aware of this bug. Thanks for the correction.|||
It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177
a) it only seems to affect databases upgraded from SQL 2000
Regarding above comment ... I ran into that exact bug with a fresh install of SQL Server 2005 and SP1 on a completely new server. And it shut down my new 1 TB server!!!
Here's hoping SP2 does address it, as I have no interest in having my production servers needing that much coddling.
|||Yes - we've been hit by this as well.
We had it set to autogrow by 250MB, until after a reboot where it changed to 32000%. Unfortunately we didn't spot this until we filled up our log file disk.
The annoying thing was that SQL Server Management Studio doesn't let you change back a value that has erroneously gone to 32000%, because it complains that the value exceeds the maximum allowed!!
So you need to change it using the alter database statement:
alter database dbname modify file (name = filename, filegrowth = 10%)
Roll on SP2...
|||We just upgraded to SP2 for that reason (the fix is supposedly in there), but we're still unable to correct the value.
When attempting to change the value, the following error appears:
Value of ‘32768’ is not valid for “value’. “Value” should be between ‘Minimum’ and “Maximum’ Parameter name Value (System. Windows.Forms)
from the SQL Server 2005 SP2 fix list:
Can anyone advise if running this statement will correct this issue permanently?
alter database MyTestDB
Modify File
(name=mytestdb, filegrowth = 500 mb)
Thanks!
|||Executing an alter database statement does not appear to prevent the behavior from resurfacing.Autogrowth values keep changing
Hi!
We have 2 dbs on our dw-server and the autogrowth values for both the data- and logfiles on both of these dbs changes about once a month. The data autogrowth value changes from 10megs to a percentage value between 3200 and 6400 and the log-file value changes from 10 percent to a percentage value between 3200 and 6400. Resulting in huge files and filling the drive.
What am I missing here?
BR John
The value does not change on its own. Someone/process must be changing this. Perhaps, you should turn on audit to see who's responsible.|||Just did it. Created a trace with the SQL Profiler to catch the SQL Batch-events. Now we just have to wait it out...
John
|||Not a helpful reply, it can and it does.
I have now experienced the same bug myself. SQL 2005 on x64, has been running okay for months. Last month the database jumped from 3Gb to something like 60Gb, I found the autogrowth setting had switched from xxMB to 2048%. I thought I must have done this by mistake, so I shrank the DB and reset the autogrowth values.
This morning I log in, database is now 204GB and the autogrowth is set to 32768% !!! It stopped at 204Gb as there was no diskspace left. I've reset the autogrow again also set "maxsize" to 10GB.
This is definitely a bug and quite a serious one - it could quite easily take out an operational server by eating up disk space.
|||Yes this is quite serious. It stops our ETL-process because of the drive being full.
Nothing in our tracelog yet...
|||It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177
a) it only seems to affect databases upgraded from SQL 2000
b) it only manifests after the SQL server has been restarted
c) It has not been fixed in SP1 so here's hoping for SP2.
You won't see anything in the trace logs, it's a bug in the SQL engine. When it stores the settings for autogrow it does not correctly set one flag for percentage or non-percentage growth. If you restart SQL server you'll see the incorrect value/
Workarounds:
set Max size of the file to prevent the file from growing too large
turn off AutoGrow
use % based autogrow
|||Thanks!
BR John
|||It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177
a) it only seems to affect databases upgraded from SQL 2000
b) it only manifests after the SQL server has been restarted
c) It has not been fixed in SP1 so here's hoping for SP2.
You won't see anything in the trace logs, it's a bug in the SQL engine. When it stores the settings for autogrow it does not correctly set one flag for percentage or non-percentage growth. If you restart SQL server you'll see the incorrect value/
Workarounds:
set Max size of the file to prevent the file from growing too large
turn off AutoGrow
use % based autogrow
I was not aware of this bug. Thanks for the correction.|||
It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177
a) it only seems to affect databases upgraded from SQL 2000
Regarding above comment ... I ran into that exact bug with a fresh install of SQL Server 2005 and SP1 on a completely new server. And it shut down my new 1 TB server!!!
Here's hoping SP2 does address it, as I have no interest in having my production servers needing that much coddling.
|||Yes - we've been hit by this as well.
We had it set to autogrow by 250MB, until after a reboot where it changed to 32000%. Unfortunately we didn't spot this until we filled up our log file disk.
The annoying thing was that SQL Server Management Studio doesn't let you change back a value that has erroneously gone to 32000%, because it complains that the value exceeds the maximum allowed!!
So you need to change it using the alter database statement:
alter database dbname modify file (name = filename, filegrowth = 10%)
Roll on SP2...
|||We just upgraded to SP2 for that reason (the fix is supposedly in there), but we're still unable to correct the value.
When attempting to change the value, the following error appears:
Value of ‘32768’ is not valid for “value’. “Value” should be between ‘Minimum’ and “Maximum’ Parameter name Value (System. Windows.Forms)
from the SQL Server 2005 SP2 fix list:
Can anyone advise if running this statement will correct this issue permanently?
alter database MyTestDB
Modify File
(name=mytestdb, filegrowth = 500 mb)
Thanks!
|||Executing an alter database statement does not appear to prevent the behavior from resurfacing.
Subscribe to:
Posts (Atom)