SQL Account Loses Mapping

Question

SQL Account Loses Mapping

Viewing 9 posts - 16 through 23 (of 23 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

here's a simple script to search both the job steps in msdb as well as the current database's stored procedure definitions.

this might point you to something to investigate.

I actually had one stored proc in master that had "CREATE USER" in it.

DECLARE

@SearchString VARCHAR(255),

@IsEnabled BIT;

SET @SearchString = 'CREATE USER'; -- Enter Search String Here. Leave Blank for All

SET @IsEnabled = 1; -- 0 = Disabled, 1 = Enabled, 2 = All

SELECT

j.Name JobName,

j.Description JobDescription,

js.step_id StepID,

js.step_name StepName,

js.database_name DatabaseName,

js.command StepCommand

FROM msdb..sysjobs j

INNER JOIN msdb..sysjobsteps js

ON j.job_id = js.job_id

WHERE (j.enabled = @IsEnabled OR @IsEnabled = 2)

AND js.command LIKE '%' + @SearchString + '%'

UNION ALL

SELECT

OBJECT_NAME(OBJECT_ID),

'A Stored Procedure With the Possible String',

0,

'a proc!',

DB_NAME(),

definition

FROM sys.sql_modules

WHERE definition LIKE '%' + @SearchString + '%'

ORDER BY

[Name],

step_id;

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!

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

tmkruse (7/1/2011)
The server is a production server, SQL 2008 R2 Standard 10.50.1600 (no Auditing)

That is good to know. Sounds like someone or something is changing the permissions.

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 3

I've got the DDL Trigger set up and tested so I'll know when and who executes the drop user command. I work for a small company with a 3rd party IT (Not me) so it's a short list of who has access to modify any logins.

Thanks for everyone's help in walking me through my issue.

Thanks to Lowell for the script.

😀

Leo.Miller SSChampion Points: 13051 More actions · Answer 4

Did you ever find anything causing this? I've got a client of a partner company with the same problem, also on a NAV database.

One question though, since it's the user mapping to the db_datareader database role that is being lost, shouldn't you be looking for the sp_droprolemember string, not DROP_USER?

Cheers

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

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

Leo.Miller (8/8/2011)
Did you ever find anything causing this? I've got a client of a partner company with the same problem, also on a NAV database.
One question though, since it's the user mapping to the db_datareader database role that is being lost, shouldn't you be looking for the sp_droprolemember string, not DROP_USER?
Cheers
Leo

This hasn't happened since I created the DDL Trigger. The user was being dropped from the NAV db so DROP_USER is appropriate.

Thx

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 6

tmkruse (8/9/2011)
Leo.Miller (8/8/2011)
Did you ever find anything causing this? I've got a client of a partner company with the same problem, also on a NAV database.
One question though, since it's the user mapping to the db_datareader database role that is being lost, shouldn't you be looking for the sp_droprolemember string, not DROP_USER?
Cheers
Leo
This hasn't happened since I created the DDL Trigger. The user was being dropped from the NAV db so DROP_USER is appropriate.
Thx

Maybe the person droping the user was reading your post on the forum 😀

My blog

Chuck Hottle SSCarpal Tunnel Points: 4777 More actions · Answer 7

I believe there is a security synchronization process in NAV that can do this if a database user wasn't added through NAV.

Leo.Miller SSChampion Points: 13051 More actions · Answer 8

Chuck Hottle (8/9/2011)
I believe there is a security synchronization process in NAV that can do this if a database user wasn't added through NAV.

This is one of the possibilities told my "client" about. They haven't come back yet to confirm anything.

Cheers

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

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

Leo.Miller (8/9/2011)
Chuck Hottle (8/9/2011)
I believe there is a security synchronization process in NAV that can do this if a database user wasn't added through NAV.
This is one of the possibilities told my "client" about. They haven't come back yet to confirm anything.
Cheers
Leo

I've tried to recreate this process using the "Synchronize All Logins" feature in NAV, but that didn't do it. Please post your feedback from your "client".

Thanks,

T