Server Logins

Question

Server Logins

Viewing 14 posts - 16 through 28 (of 28 total)

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

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 1

Restore your master db backup as another name, say master_bk, and check the sys.server_principals view. It will give you all server logins. It won't give you their mappings onto each user db though.

For this you need to look at the sys.database_principals and sys.database_role_members views on each of your user databases. Check BOL for more info on these and other related views.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

VastSQL SSC-Dedicated Points: 36121 More actions · Answer 2

Will Restoring the master DB gives the mapping of all users to respective databases???

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

It should, yes.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

VastSQL SSC-Dedicated Points: 36121 More actions · Answer 4

I got error that the present version is lower than the backup of master.The present one had sp3 ,thinking that earlier one had sp4 i installed sp4 and now the server is showing present version is higher than the previous one ..wat to do??? :-((

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Try restoring the backup to another server as a normal user database, then scripting the logins from the syslogins table.

Please, in the future, put SQL 2000-related questions into the SQL 2000 forum. There are very different ways to do things in the two versions and if you put it here, we're going to assume it's sQL 2005 and make suggestions appropriate to that version.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

VastSQL SSC-Dedicated Points: 36121 More actions · Answer 6

Am really sorry for that and will definitely try to eliminate mistakes like this.

Thanks for your help

VastSQL SSC-Dedicated Points: 36121 More actions · Answer 7

From syslogin view am only able to get logins and database names.Is the database names are those to which they are mapped or just the default database in the logins.What about database users???

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Default database.

What you need to get out of the syslogins is the login name, the password (hashed) and the SID. The SID is what will link up the logins with the database users when you run the script on your recovered server.

Database users, as I said before in this thread, are in the individual databases, not in master.

You'll also need to find where the server-level permissions and rols are stored. Offhand, I don't know. Have a look in books Online, it should tell you.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

VastSQL SSC-Dedicated Points: 36121 More actions · Answer 9

Hi Gila ,

Am able to restore the master db and theservice is not starting now...reinstalled sql server 🙁

Can you please tell the order for restoring the databases.

I hope its like

1)install sql server

2)apply SP same as that of old server

3)restore master

4)restore msdb(is it neccessary to restore model database??)

5)attach all user databases

Am i right??

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

What was the error when the service didn't start? There would have been something in the SQL error log.

Install

Patch

Ensure that the user DB files are in the same location as they were on the other DB

Restore master, model (if you made any changes to it), MSDB

User database should have been picked up with the restore of master, providing the files were in the right place. If not, restore

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

VastSQL SSC-Dedicated Points: 36121 More actions · Answer 11

Files were not in the same path at ,first copied all files to that path and then run servr.exe.All databases were listed but in suspect mode, restored all databases and working fine now...:-).Can i restore model database now with the old backup??

Thanks a lot for you support GILA

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

You can. If you haven't made changes to the model database, it's not necessary.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

VastSQL SSC-Dedicated Points: 36121 More actions · Answer 13

Can i restore msdb now with the old msdb backup???Is ther issue in restoring msdb now after restoring master and all other databases??

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

You should be able to restore it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass