Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Disable database user Expand / Collapse
Author
Message
Posted Thursday, July 18, 2013 9:49 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 7:58 PM
Points: 727, Visits: 1,411
Hello everyone,

We are working on a migration, a few db's a night for the next week. Part of the migration requires that we shutoff access to the database. This is a 3rd party application and, in their infinite wisdom, they created a single login that maps to a user in each database.

As such, I cannot disable the login. Is there a way to disable the user or unmap it from the login?

Note, I am not able to take the DB offline, rename it, etc, which complicates things more.

Thanks,

Fraggle
Post #1475108
Posted Thursday, July 18, 2013 9:57 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:24 AM
Points: 3,105, Visits: 7,819

Have you tried setting the database to Single_User mode?




Alvin Ramard
Memphis PASS Chapter

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.
Post #1475112
Posted Thursday, July 18, 2013 11:01 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 7:58 PM
Points: 727, Visits: 1,411
Can't do it. We are using Replication to migrate the databases.

Thanks,

Fraggle
Post #1475149
Posted Thursday, July 18, 2013 12:25 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:55 AM
Points: 12,884, Visits: 31,825
fraggle i just tested this myself;
i was hoping i could alter a user and remove their mapping to the existing login, but i found i had to crop and recreate the user without login instead.

if the user doesn't have a matching user, it wouldn't have access to that specific database anymore.

this was my test:
Create LOGIN [ClarkKent] WITH PASSWORD='NotTheRealPassword'

USE [WHATEVER]
Create USER [ClarkKent] FOR LOGIN [ClarkKent] ;
--didn't work:
--ALTER USER [ClarkKent] WITHOUT LOGIN;
--remove and put the user back without login, so we can
DROP USER [ClarkKent];
CREATE USER [ClarkKent] WITHOUT LOGIN;
--add back any roles, ie

CREATE ROLE [AlmostOwners]
EXEC sp_addrolemember N'db_ddladmin', N'AlmostOwners'
EXEC sp_addrolemember N'db_datareader', N'AlmostOwners'
EXEC sp_addrolemember N'db_datawriter', N'AlmostOwners'
--can the users EXECUTE procedures? comment out if false
GRANT EXECUTE TO [AlmostOwners]
--allow the users to see view proc and function definitions
Grant View Definition ON SCHEMA::[dbo] To [AlmostOwners]

--finally add our user to the role:
EXEC sp_addrolemember N'AlmostOwners', N'ClarkKent'


if needed then later we can ALTER USER [ClarkKent] FOR LOGIN [ClarkKent] to put him back.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1475170
Posted Thursday, July 18, 2013 3:03 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 529, Visits: 1,564
How about putting the user in the db_denydatareader Database security Role?
Post #1475226
Posted Friday, July 19, 2013 1:57 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:28 PM
Points: 786, Visits: 691
Fraggle-805517 (2013-07-18)
Is there a way to disable the user or unmap it from the login?


REVOKE CONNECT TO thisuser



Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1475310
Posted Saturday, July 20, 2013 10:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:57 AM
Points: 7,081, Visits: 12,575
There is no way to "disable a database user" per se. The way i do this is to remap the User i want to "disable" to a different Login, a temporary one that no one could ever usemto connect to the instance. Then when i want to "enable" the User again, i remap it to the original Login thereby reinstating access.

ALTER USER...WITH LOGIN =


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1475758
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse