Disable database user

Question

Post reply

Disable database user

Fraggle-805517

SSCrazy Eights

Points: 8907
More actions
July 18, 2013 at 9:49 am

#280545

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

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 1

Have you tried setting the database to Single_User mode?

Alvin Ramard
Memphis PASS Chapter[/url]

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.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Fraggle-805517 SSCrazy Eights Points: 8907 More actions · Answer 2

Can't do it. We are using Replication to migrate the databases.

Thanks,

Fraggle

Lowell SSC Guru Points: 323526 More actions · Answer 3

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

dan-572483 SSCoach Points: 15870 More actions · Answer 4

How about putting the user in the db_denydatareader Database security Role?

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 5

Fraggle-805517 (2013-07-18)
Is there a way to disable the user or unmap it from the login?

REVOKE CONNECT TO thisuser

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 6

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

Disable database user

Cookies on SQLServerCentral