SQL 2008 DB Lost Security Users / Permissions

Windows 2008 R2 Server
SQL 2008 Enterprise
SharePoint 2010

Have a SharePoint SQL User Profile Service SyncDB  the other day my SharePoint had errors. After research I found that the Farm Account was missing from the Database Security Users.

So I added the account back in and added connect permissions and the SharePoint server started working.
I have other issues and I came back to SQL and found that my Security Users may not be correct.

I compared to my other SharePoint databases.  The Users are displayed differently I posted a screen image.

I ran this on the SQL instance "exec sp_change_users_login 'Report'  that produced no records.

The account SPFARM is in the Security Logins section

Other Databases have the same account and accounts listed.

No matter how I try to add the accounts back to this database it just does not look right and I think that is why I am having other issues,

Is the Database corrupted?

Any help would be appreciated.

Thanks in advance
SQL-Security-Issue.png
LVL 23
Thomas GrassiSystems AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anoo S PillaiCommented:
You can see users itself means that the database is not corrupted,

I am not clear about your requirement, BUT the spfarm in first instance is a Windows login ( Windows user from machine/domain OUR ) and the second instance is a SQL Login ( not related to Windows account ) ..

Anoo
0
Thomas GrassiSystems AdministratorAuthor Commented:
Anoo

I would like to get the users to match the same as in the first DB shown.

How do I do that?
0
Anoo S PillaiCommented:
Could you please run

CREATE USER [OUR\spfarm] FOR LOGIN [OUR\spfarm]
GO

in second database and check the users.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Thomas GrassiSystems AdministratorAuthor Commented:
Anoo

Ran this got this error

CREATE USER [OUR\spfarm] FOR LOGIN [OUR\spfarm]
 GO

Msg 15063, Level 16, State 1, Line 1
The login already has an account under a different user name.
0
Thomas GrassiSystems AdministratorAuthor Commented:
I went into the Security > Logins

clicked on Properties on our\spfarm

Went to User Mapping

Found the Database in question

Unchecked it and then rechecked it now it put our\spfarm account

then I had to manually add the correct default schema our\spfarm

now looks good.

any further thoughts?
0
Anoo S PillaiCommented:
Now the remaining database users too are to be added in the same way. Probably you might have done it already.

The next is to assign required permissions to each of these database users.
If the privileges are inherited from server from roles of SQL Login, Then everything is set now.

Since we are not sure about it,
1) identify the database roles in which each of the users belong to. Add them back to the role.
2) There could be some object lever permissions set, identify them and set them for each user.

From the previous reply, I think you have enough expertise to do this. Hence not providing a lengthy step by step process. ( BTW, there are scripts available in internet to generate permission scripts for database users, you can make use of that too) ..


Anoo
0
Thomas GrassiSystems AdministratorAuthor Commented:
Anoo

Thanks for the update

One thing I just noticed is that the Schema owned by this user are different than the other databases
I should have a Owned Schema of OUR\spfarm  it does not exist

Thoughts

If you have a link to one of those permission scripts that would be helpful too

Thank you
0
Anoo S PillaiCommented:
I was thinking about default schema and ownership.  BUT noting your statement "then I had to manually add the correct default schema our\spfarm" , I came to know that you are not a newbie in SQL Server :) , Hence did not comment on schema. I think schema is correctly set as of now ? Am n't I right on this ?

I am copying three scripts for your reference -
https://gallery.technet.microsoft.com/scriptcenter/Get-logins-databases-816f66b2
http://www.sql-server-performance.com/2002/object-permission-scripts/
http://vyaskn.tripod.com/scripting_permissions_in_sql_server_2005.htm

I could not personally verify the scripts by running it since the machine currently I am in does not have SQL installed :( , I am on Sat/Sun holiday
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Thomas GrassiSystems AdministratorAuthor Commented:
Thanks for the scripts.

I deleted the databases and recreated them in sharepoint
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.