Hot answers tagged logins
8
My understanding is that if you aren't using Contained Databases, you will have to ensure logins are created on other instances manually.
Something like this script from SQLSoldier, originally posted as Transferring Logins to a Database Mirror, should do the trick.
5
This means that the login [R2Server\AAOUser] is already mapped to a user in that database. Or, in other words, another database user is using this login. You can see what database user is using your login with the following query:
use YourDB
go
SELECT su.name as DatabaseUser
FROM sys.sysusers su
join sys.syslogins sl on sl.sid = su.sid
where sl.name = ...
5
State codes and their meaning.
1 'Account is locked out'
2 'User id is not valid'
3-4 'Undocumented'
5 'User id is not valid'
6 'Undocumented'
7 'The login being used is disabled'
8 'Incorrect password'
9 'Invalid password'
10 'Related to a SQL login being bound to Windows domain password policy enforcement.
...
3
Per my original comment, it appears the SUSER_SID function just grabs whatever sid was recorded when the login was created, and doesn't actually query Active Directory (makes sense, as that could be expensive -- I even tried restarting the server service).
Here is a C# console application that accomplishes the task, allowing you to audit the logins that ...
2
Take a look at the configuration manager and look at the following. It will be in "start menu->All Programs->Microsoft SQL Server 2005->Configuration tools".
Go into the SQL Server 2005 configuration manager. Select "SQL Server Network Configuration" and then "Protocols for MSSQLSERVER". Right click on "tcp/ip" and select enabled. This will ensure that the ...
2
Yes, you can use SQL Server Management Studio, even if it's an Express version to connect to any SQL Server,you have TCP/IP connectivity to. Just launch it, enter the DNS host name or IP address in the 'Server Name' box and hit Connect.
Two things may prevent this from working:
Your SQL Server isn't set up for TCP/IP connectivity. This is the default ...
2
What you will have to do is connect to the instance that has a mis-matched SID, and you'll have to recreate the login and specify an explicit SID. For instance, on the instance where you have the orphaned user and the following returns the user:
exec sp_change_users_login 'report';
go
Copy the SID from the column UserSID. And if you already have an ...
2
In SQL Server, as long as a LOGIN is valid, it will have access to the master database in a public role. This is both for historical and practical reasons. For example, stored procedures beginning with "sp_" are searched first in the master database and will execute from there. The implication is that users need to first have access to the master database ...
2
Well, the problem is: you cannot backup a SQL Server database that's used with the AttachDbFileName= property in the connection string:
Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.mdf;
Integrated Security=True;User Instance=True;Database=ASPNETDB";
If you want to use SMO to backup your database, the database must be attached to the ...
2
You can leverage xp_logininfo for this process. This extended stored procedure can be used to provide information from Active Directory for Windows logins in SQL Server. The procedure returns an error if no login exists, so we can put a TRY/CATCH block around it to provide SQL for logins that are no longer valid when the procedure errors:
declare @user ...
1
Use a database role. Database roles are database specific (obviously) so you can't create a role that grant's permissions to multiple databases at once. However within the database you create a role either through the GUI or using the command CREATE ROLE <rolename>. Once it is created you can then grant the role permissions just like you would a ...
1
Fixed this - VSS Writer had fallen over for some reason, and the upshot of that was that a load of stuff (BACKUP amongst them) could not complete (BlkBy column in sp_who2 showed this up). A reboot of the server fixed this issue (services.msc wouldn't let me kill VSS Writer, and neither would the KILL command)...only thing left to do is find out WHY it ...
1
You must either use a Contained Database, or you must recreate the users on the other server(s) with the same password hash and SID.
A script to do this is provided by Microsoft:
How to transfer logins and passwords between instances of SQL Server
Mark's solution was partially right however his recommended solution was for Mirrored databases, as opposed to ...
Only top voted, non community-wiki answers of a minimum length are eligible
