Forcing Users to log out of SQL 2000

I am a begginer to SQL. I am truly limited with SQL language.

Every month we have to do a month end closing for our company. In order for us to do this we need to get everyone off the database to proceed. This process is horrible and generally takes at least 1/2hr to 45 min. to do so. i am looking for a command that i can use in query analyzer if possible to force the users out of the database. if that isnt an option can you recommend another program in which i can do this.? i am at a loss and need some help..... thanks

Did you have a look in the BOL for ALTER DATABASE ?

ALTER DATABASE SomeDatabase
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE

or from the syntax with a delay of x seconds:

<termination> ::=
{
ROLLBACK AFTER integer [ SECONDS ]
| ROLLBACK IMMEDIATE
| NO_WAIT
}

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

i am unsure of what you mean. as i stated i am truly a beginner with SQL.

can you explain it to me like i am 4? lol

thanks

|||

SUre thisis quite easy, the command kills all the user sessions immediately. If you want to wait a respective moment you can use the WAIT FOR keyword, but with the keyword IMMEDIATELY all User will be banned out of the database and their transactions will be roled back (if any transaction is open)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

what is the command?

can i use it in query analyze?

thanks again

|||You can use it in QA to force the users (as I mentioned before) to log out, current transactions will be rolled back. Make sure if this complies with your maintaince strategy (not every user is pleased to be logged out while he is doing a transaction :-) )

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

ok thank you i will try this at the end of the month

i do appriciate your time!!

thanks

Forcefully Disconnect All Users and Drop a Database

Hey all,

I am trying to write a function to drop a specific database no matter the connection status. I have tried

con = new ServerConnection(sql);

con.Connect();

Server srv = new Server(con);

srv.KillDatabase("Name");

,and also tried

Database db= Database (srv, "Name");

Database db=srv.Databases["Name"];

db.Drop();

None of these worked. That surprises me because KillDatabase is supposed to disconnect all activity to the database, at least that's what it says it does.

Thanks.

Hi,

that was worth a Blog entry, there you are :-)

http://www.sqlserver2005.de/SQLServer2005/Default.aspx?tabid=56&EntryID=9

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks!|||Awesome! I was just logging in the forum to ask that very same question. Thanks a lot! :)|||You all most had it all you need is to add the following line and it will work.,

Database db= Database (srv, "Name");

con = new ServerConnection(sql);

con.Connect();

Server srv = new Server(con);

Database db=srv.Databases["Name"];

//Add this line
db.DatabaseOptions.UserAccess = DatabaseUserAccess.Restricted;

db.Drop();

|||Update to all, the SMO classes were changed to the following:

collection1.Add(string.Format(SmoApplication.DefaultCulture, "ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE", new object[] { SqlSmoObject.MakeSqlBraket(database) }));
base.ExecutionManager.ExecuteNonQuery(collection1);
this.Databases[database].Drop();

which is pretty close to my suggestion :-) :

s.Databases["master"].ExecuteNonQuery(string.Format("ALTER DATABASE {0} SET SINGLE_USER with ROLLBACK IMMEDIATE", databaseName));
s.Databases[databaseName].Drop();

-Jens.

Forcefully Disconnect All Users and Drop a Database

Hey all,

I am trying to write a function to drop a specific database no matter the connection status. I have tried

con = new ServerConnection(sql);

con.Connect();

Server srv = new Server(con);

srv.KillDatabase("Name");

,and also tried

Database db= Database (srv, "Name");

Database db=srv.Databases["Name"];

db.Drop();

None of these worked. That surprises me because KillDatabase is supposed to disconnect all activity to the database, at least that's what it says it does.

Thanks.

Hi,

that was worth a Blog entry, there you are :-)

http://www.sqlserver2005.de/SQLServer2005/Default.aspx?tabid=56&EntryID=9

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks!|||Awesome! I was just logging in the forum to ask that very same question. Thanks a lot! :)|||You all most had it all you need is to add the following line and it will work.,

Database db= Database (srv, "Name");

con = new ServerConnection(sql);

con.Connect();

Server srv = new Server(con);

Database db=srv.Databases["Name"];

//Add this line
db.DatabaseOptions.UserAccess = DatabaseUserAccess.Restricted;

db.Drop();

|||Update to all, the SMO classes were changed to the following:

collection1.Add(string.Format(SmoApplication.DefaultCulture, "ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE", new object[] { SqlSmoObject.MakeSqlBraket(database) }));
base.ExecutionManager.ExecuteNonQuery(collection1);
this.Databases[database].Drop();

which is pretty close to my suggestion :-) :

s.Databases["master"].ExecuteNonQuery(string.Format("ALTER DATABASE {0} SET SINGLE_USER with ROLLBACK IMMEDIATE", databaseName));
s.Databases[databaseName].Drop();

-Jens.

Forcefully Disconnect All Users and Drop a Database

Hey all,

I am trying to write a function to drop a specific database no matter the connection status. I have tried

con = new ServerConnection(sql);

con.Connect();

Server srv = new Server(con);

srv.KillDatabase("Name");

,and also tried

Database db= Database (srv, "Name");

Database db=srv.Databases["Name"];

db.Drop();

None of these worked. That surprises me because KillDatabase is supposed to disconnect all activity to the database, at least that's what it says it does.

Thanks.

Hi,

that was worth a Blog entry, there you are :-)

http://www.sqlserver2005.de/SQLServer2005/Default.aspx?tabid=56&EntryID=9

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks!|||Awesome! I was just logging in the forum to ask that very same question. Thanks a lot! :)|||You all most had it all you need is to add the following line and it will work.,

Database db= Database (srv, "Name");

con = new ServerConnection(sql);

con.Connect();

Server srv = new Server(con);

Database db=srv.Databases["Name"];

//Add this line
db.DatabaseOptions.UserAccess = DatabaseUserAccess.Restricted;

db.Drop();

|||Update to all, the SMO classes were changed to the following:

collection1.Add(string.Format(SmoApplication.DefaultCulture, "ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE", new object[] { SqlSmoObject.MakeSqlBraket(database) }));
base.ExecutionManager.ExecuteNonQuery(collection1);
this.Databases[database].Drop();

which is pretty close to my suggestion :-) :

s.Databases["master"].ExecuteNonQuery(string.Format("ALTER DATABASE {0} SET SINGLE_USER with ROLLBACK IMMEDIATE", databaseName));
s.Databases[databaseName].Drop();

-Jens.

sql

Force users/connections to disconnect from db

Hello,
I need to attach and detach 50 DB's, for which I have wrote a simple script. How can I make sure that there are no connections or users connected to the DB's, if there any users how can I forcefully disconnect them.
Thanks
DakkiOriginally posted by Dakki
Hello,

I need to attach and detach 50 DB's, for which I have wrote a simple script. How can I make sure that there are no connections or users connected to the DB's, if there any users how can I forcefully disconnect them.

Thanks
Dakki

sp_detachDB does detach a db even if users are logged in. Only if transactions are running, dettach will fail. sp_who shows you for every db on this server all logged in users.

Hope this helps
Peter|||Originally posted by peterdbd
sp_detachDB does detach a db even if users are logged in. Only if transactions are running, dettach will fail. sp_who shows you for every db on this server all logged in users.

Hope this helps
Peter

Excellent, I shall try this...

many thanks

dakki|||peterdbd,

i don't think it's an accurate statement, because you'll get this error if there is at least 1 connection open for the database you're trying to detach, even if this connection has not performed a single operation:

Server: Msg 3701, Level 16, State 1, Line 1
Cannot detach the database 'db_name' because it is currently in use.|||guru, you are right, I had to re run the script, since some of the db's had connections to it...

thanks again, to you both ...|||list all users with sp_who2
and find out sid of all the db u r looking at

using sa , kill all sid

eg:- kill sid

then proceed with detach.

Note:-
Do not any sid doing updates...|||Take help from this link (http://www.sql-server-performance.com/q&a37.asp) which includes the script to kill are users that are connected to a database that needs to be dropped/detached etc. etc.:cool:|||there are 3 problems with the script:

- based on stored procedure. i wouldn't recommend leaving such tool so handy

- based on cursor, - simply no need

- does not take into account the possibility of attempting to kill yourself (not that you'll succeed though)

there is a simpler way:

declare @.cmd varchar(100)
while (select count(*)
from master.dbo.sysprocesses (nolock)
where spid != @.@.spid and db_name(dbid) = 'your_db_name') > 0 begin
set @.cmd = 'kill ' +(select cast(min(spid) as varchar(25))
from master.dbo.sysprocesses (nolock)
where spid != @.@.spid and db_name(dbid) = 'mci2k')
exec ( @.cmd )
end

Force user to Change their password

Is there a way in sql server 2000 to force users to change their passwords periodically?Hi,

There is now setting you can configure to do so. But master..syslogins has a column (updatedate) so you could make a job that select every login that havent change their password for, let say for a, month.

Select loginname from master..syslogins where datediff(Month,updatedate,getdate())>1


Now you can decide if you make a cursor that change all the password (which givs you the problem too tell users) or you email them ask them to do so.

I hope this is useful
/Mada|||I think it's master.dbo.sysxlogins which contains a field named xdate2 which is basically the last time the password was changed.But the fact is that there is no other way to do this,unless you email the users about their password expiration.Sybase has a "Sysetmwide password configuration option" which was very useful in defining the serverwide password expiration dyas.|||Hi Old hand,

If you use NT-account in your sql server you can set a expiration period in the user manager.

/Mada

Forbid Excel access for users

It seems that people can use Excel to browse the Cube, thats all good and fun. But we do not like for all the users to be able to go through the cube using excel (only a select group). The problem is that i have to create roles for the users to set their security for the report server. So that they can open the reports i've created. But if i'm not mistaking by granting users access to the cube to be able to use my reports I automaticly give them access through Excel ?!

Is there a way to give people access to the cube via report server and block their access for Excel ?

Please help me you experts

Hello! It is possible to block the access from an Excel client by using a dedicated SSAS2005 role for reporting services.

You create a single windows account for SSRS2005 execution of SSAS2005 reports, add that account to a cube role and stop using windows integrated security.

In this way it is only this dedicated SSRS2005 account that have access to the cube.

I assume that you do not use any detailed security in the cubes like dimension restrictions and cell security.

HTH

Thomas Ivarsson

|||

That would be a nice solutions but its not possible in my case because i need to set security for users with restructions as you also posted below, i have like 30 different roles allowed groups of people to access certain dimensions. It would be much easier if there was an option in the roles to allow or disallow Excel, but i guess there is no such thing ? So basicly i'm stuck ?|||

In SQL Server you have application roles but I have never heard about that in SSAS2005.

One way can be to remove the OLEDB for OLAP 9 from each Excel client if your users are on the same LAN.

I can not see any other solution than the ones outside of SSAS2005.

Regards

Thomas Ivarsson

|||

We're dealing with a similar issue. One approach is to define a users dimension with a many-to-many relation to a security measure group. The linkages between the security measure and the various dimension members would allow you to redefine all your security roles as follows:

Everyone who should have direct access to the cube for Excel, ProClarity etc, would have a role attached to his particular username in the user dimension.

THen the Reporting Services reports would be run under the privileged account. The RS queries would have to specify the username perhaps a hidden parameter you can fill from either a data driven subscription or from an extension to the web browser.

Its' complicated. It's not all that efficient (many-to-many dimensions are not wonderful). It requires either a data driven subscription or a custom web viewer control to fill the parameter. But I think it will work.

|||

Darn so there is no "easy" solution

I suggest Microsoft brings out a servicepack 3 with application roles in it

FOR XML in web page- OK what's next

(1) I need to select records from a SQL database and create a XML document which I then need to write to a users directory. I am using SQL express with VWD 2005. I located the FOR XML and can execute in VWD's SQL graphical tool. I haven't tried in a Web Form yet but I assume it will work ok. But then how do I write the xml results from the SQL query to local user's directory?

(2) I can execute the FOR XML in the SQL graphical tool but how do i Connect to the DB in the Web Form? Use data.sqlClient.Connection and use .SQLcommand to perform the SQL query? If so, then what?

Following is an example I found that shows use of a NameSpace

WITH XMLNAMESPACES (DEFAULT 'urn:example.com/doc'

, 'urn:example.com/customer' as "c"

, 'urn:example.com/order' as"o"

)

SELECT CustomerID as "@.ID",

(SELECT OrderID as "@.OrderID"

from Orders

where Customers.CustomerID=Orders.CustomerID

FOR XML PATH('o:Order'), TYPE

) as "c:Orders",

CompanyName as "c:CompanyName",

ContactTitle as "c:ContactName/@.ContactTitle",

ContactName as "c:ContactName/text()",

PostalCode as "c:Address/@.ZIP",

Address as "c:Address/c:Street",

City as "c:Address/c:City"

FROM Customers

FOR XML PATH('c:Customer'), ROOT('doc')

My research turns up nothing on the above. Is FOR SQL the best way to go? I see that the SQLXML is not available in SQL server express 2005.

Thanks for any help.

Pauley

Once you have the data in a dataset you can write the XML to a file(froma datatable as well if you want) Link is here;

http://msdn2.microsoft.com/en-us/library/zx8h06sz.aspx

To do this you do not need to use for xml or even the XML-DT on the server.