lost all permissions

Question

Post reply

lost all permissions

keith.justice

SSC Veteran

Points: 280
More actions
October 1, 2010 at 5:02 am

#387917

SQL 2008 R2. I was using restore with a database (not attach) and had a program having connection issues, left SQL open (quit RDP session) came back next day and all permissions were gone for all databases. Would using restore have lost all my SQL perms where I couldn't log in to SQL? Or might have something else been happening behind the scenes I didn't catch?
I had 2 databases that were moved (restored) from a 2000 SQL server amd 2005 Express into 2008R2. The first appeared fine and the 2nd (from Express) is where the prob;em appeared to come in. These were not system DBs.

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

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

Lowell SSC Guru Points: 323546 More actions · Answer 1

if you restored a database that came from a different server, this is a known issue;

USERS in the database are mapped to LOGINS in the master database;

the sql user "bob" on my server is not the same "bob" on your server;

behind the scenes each login has a unique SID in my master.dbo.syslogins;

the user in [yourdatabase].sys.users either does not have a matching login at all in master.dbo.syslogins, or the login has a different SID than expected. This is often called an orphaned user, and if the login exists, you can use the procedure sp_change_users_login

http://www.fileformat.info/tip/microsoft/sql_orphan_user.htm
First, make sure that this is the problem. This will lists the orphaned users:
EXEC sp_change_users_login 'Report'
If you already have a login id and password for this user, fix it by doing:
EXEC sp_change_users_login 'Auto_Fix', 'user'
If you want to create a new login id and password for this user, fix it by doing:
EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

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!

keith.justice SSC Veteran Points: 280 More actions · Answer 2

The known issue I get but what happened is it lost ALL SQL permissions. The only thing I saw in the logs from that time was that the SQL account for services had failed login for 1minute. I also saw that SQL AD Integration service had become enabled started-stopped and then disabled again. Could that have done it?

Perry Whittle SSC Guru Points: 234064 More actions · Answer 3

Hi Keith

are we talking SQL server user accounts or Windows user\group accounts?

The only way you'll lose permissions for the user in the database is to drop the database user! When moving the database to another server (as Lowell pointed out) you will need to re map users unless they are already snychronised between servers.

For windows accounts\groups, If the servers are not in the same domain and the windows account is not a domain account the mappings will be lost.

The local database catalog view sys.database_principals contains the database users

The global catalog view sys.server_principals contains the server logins

compare the output of these for the user account(s) in question and check the SIDs match.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉