Deny login for SQL Server Authenticated accounts

Hi,
I want to prevent users from logging into a database temporarily (once a
weekly for a few hours), and once I've updated the tables in the database, I
need to allow all users access again.
I have looked at the system stored procedure sp_denylogin, but this only
works for NT Authenticated accounts. Is there an equivalent system stored
procedure for denying logins for accounts created with AQL Server
Authentication?
Your help will be greatly appreciated.In the script that updates this, place at the top:
ALTER DATABASE databasename SET SINGLE_USER WITH ROLLBACK IMMEDIATE
At the end of the script, put:
ALTER DATABASE databasename SET MULTI_USER
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:EE89773B-5F28-4760-B1AF-8C0347A69DF9@.microsoft.com...
> Hi,
> I want to prevent users from logging into a database temporarily (once a
> weekly for a few hours), and once I've updated the tables in the database,
I
> need to allow all users access again.
> I have looked at the system stored procedure sp_denylogin, but this only
> works for NT Authenticated accounts. Is there an equivalent system stored
> procedure for denying logins for accounts created with AQL Server
> Authentication?
> Your help will be greatly appreciated.
>|||Hi,
I recommend you to start the database in SINGLE USER mode or set the
database to RESTRICTED mode before changing rhe schema.
ALTER DATABASE <DBNAME> set SINGLE_USER
or
ALTER DATABASE <DBNAME> set RESTRICTED_USER
Once the schema change is over you set back to multi user mode.
ALTER DATABASE <DBNAME> set MULTI_USER
Thanks
Hari
SQL Server MVP
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:EE89773B-5F28-4760-B1AF-8C0347A69DF9@.microsoft.com...
> Hi,
> I want to prevent users from logging into a database temporarily (once a
> weekly for a few hours), and once I've updated the tables in the database,
> I
> need to allow all users access again.
> I have looked at the system stored procedure sp_denylogin, but this only
> works for NT Authenticated accounts. Is there an equivalent system stored
> procedure for denying logins for accounts created with AQL Server
> Authentication?
> Your help will be greatly appreciated.
>|||Hi Hari,
Tried running this in the SQL Query Analyser, and appears to take forever.
It has now been running for over 30 minutes and still has a status of
"executing query batch"
Is this normal?
Eric
"Hari Prasad" wrote:
> Hi,
> I recommend you to start the database in SINGLE USER mode or set the
> database to RESTRICTED mode before changing rhe schema.
>
> ALTER DATABASE <DBNAME> set SINGLE_USER
> or
> ALTER DATABASE <DBNAME> set RESTRICTED_USER
>
> Once the schema change is over you set back to multi user mode.
> ALTER DATABASE <DBNAME> set MULTI_USER
>
> --
> Thanks
> Hari
> SQL Server MVP
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:EE89773B-5F28-4760-B1AF-8C0347A69DF9@.microsoft.com...
> > Hi,
> >
> > I want to prevent users from logging into a database temporarily (once a
> > weekly for a few hours), and once I've updated the tables in the database,
> > I
> > need to allow all users access again.
> >
> > I have looked at the system stored procedure sp_denylogin, but this only
> > works for NT Authenticated accounts. Is there an equivalent system stored
> > procedure for denying logins for accounts created with AQL Server
> > Authentication?
> >
> > Your help will be greatly appreciated.
> >
> >
>
>|||It can be if someone is using the database. You can force
the connections to be killed with the termination clause in
the alter database:
ALTER DATABASE YourDatabase
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
-Sue
On Mon, 25 Oct 2004 17:49:03 -0700, "Eric"
<Eric@.discussions.microsoft.com> wrote:
>Hi Hari,
>Tried running this in the SQL Query Analyser, and appears to take forever.
>It has now been running for over 30 minutes and still has a status of
>"executing query batch"
>Is this normal?
>Eric
>
>"Hari Prasad" wrote:
>> Hi,
>> I recommend you to start the database in SINGLE USER mode or set the
>> database to RESTRICTED mode before changing rhe schema.
>>
>> ALTER DATABASE <DBNAME> set SINGLE_USER
>> or
>> ALTER DATABASE <DBNAME> set RESTRICTED_USER
>>
>> Once the schema change is over you set back to multi user mode.
>> ALTER DATABASE <DBNAME> set MULTI_USER
>>
>> --
>> Thanks
>> Hari
>> SQL Server MVP
>> "Eric" <Eric@.discussions.microsoft.com> wrote in message
>> news:EE89773B-5F28-4760-B1AF-8C0347A69DF9@.microsoft.com...
>> > Hi,
>> >
>> > I want to prevent users from logging into a database temporarily (once a
>> > weekly for a few hours), and once I've updated the tables in the database,
>> > I
>> > need to allow all users access again.
>> >
>> > I have looked at the system stored procedure sp_denylogin, but this only
>> > works for NT Authenticated accounts. Is there an equivalent system stored
>> > procedure for denying logins for accounts created with AQL Server
>> > Authentication?
>> >
>> > Your help will be greatly appreciated.
>> >
>> >
>>