SQL Account Loses Mapping

Question

Post reply

SQL Account Loses Mapping

tmkruse

Mr or Mrs. 500

Points: 596
More actions
July 1, 2011 at 10:37 am

#243579

I have an SQL Server account that has lost the mapping to a database twice now. There are only a couple of people who have access to change this. The account is a datareader that I use to access the database from SSRS. It's a MS NAV database.
What's the best type of monitoring to put in place to see when this is happens.

Viewing 15 posts - 1 through 15 (of 23 total)

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

When you say that an Account lost it's mapping to the Database could you be more specific?

Specifically what happens and when does it happen?

Do you have orphan users?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

There wasn't a database restore done was there?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

tmkruse Mr or Mrs. 500 Points: 596 More actions · Answer 3

There was no database restore. When I say mapping, under user mapping on the Login properties the map field was unchecked. This is alos a MS NAV database so I'm not aware of any utilities in NAV that would do this.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

tmkruse (7/1/2011)
There was no database restore. When I say mapping, under user mapping on the Login properties the map field was unchecked. This is alos a MS NAV database so I'm not aware of any utilities in NAV that would do this.

Yes. I asked about the restore because that can cause the very issue you are referencing. It sounded a lot like orphaned users so wanted to verify.

To find who changed it, you could check the default trace.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

Someone or something removed the permission to the database for these Logins.

It did not happen by itself.

Auditing?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Welsh Corgi (7/1/2011)
Someone or something removed the permission to the database for these Loins.

You had access too? 😉

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

tmkruse Mr or Mrs. 500 Points: 596 More actions · Answer 7

I'm looking at setting up auditing now, any good references?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

You could start here.

http://msdn.microsoft.com/en-us/library/ms190378.aspx

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

tmkruse Mr or Mrs. 500 Points: 596 More actions · Answer 9

Thanks Jason, I guess I just got too used to using Idera's auditing tool. Looking to set this up on a dev box today.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 10

Is the machine that you are loosing the mappings on a Dev & QA Box?

Please answer!

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

tmkruse Mr or Mrs. 500 Points: 596 More actions · Answer 11

The server is a production server, SQL 2008 R2 Standard 10.50.1600 (no Auditing)

I'm setting up a DDL Trigger (in dev first) on the DROP_USER event that will log the user and ddl statement in a table and send an email to our IT group.

Thanks for everyone's help.

Lowell SSC Guru Points: 323518 More actions · Answer 12

Lowell (7/1/2011)
any chance there is a script or job doing something like this?
since you said it happens "regularly, maybe there's an obscure snippet in a job or something
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'YOURROLE' AND type = 'S')
DROP USER YOURROLE
GO
CREATE USER YOURROLE

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!

tmkruse Mr or Mrs. 500 Points: 596 More actions · Answer 13

That's what I'm looking for Lowell and the DDL trigger looks like it will work to monitor.

Thanks,

T

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 14

Are there any deployments happening to the server just before the mapping is lost.

It could be possible that some code meant to be in defensive coding format has been poorly written.

Jayanth Kurup[/url]