Tag Info

Hot answers tagged

9

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.


8

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. ...


6

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

You are attempting to pass Windows credentials in plain text from the connection string of an application. This simply isn't how Windows authentication works, and largely defeats the purpose. You also can't just create the same username with the same password in your own domain, and expect that to magically work. Domain name is still part of the validation ...


5

You can use the EXECUTE AS command: http://msdn.microsoft.com/en-us/library/ms181362.aspx


5

A user that was explicitly created with WITHOUT LOGIN cannot be mapped to a login after the fact. A user that was created with a login and then lost its association, e.g. because the database was moved to another server, can be remapped by first creating a new login (any name) and then using the ALTER USER ... WITH LOGIN = ... command. To demonstrate I am ...


4

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 ...


4

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 ...


4

These logins are created from a certificate. In fact, if you run the following query: select name, type_desc from sys.server_principals where type = 'c'; You will see that they are of type CERTIFICATE_MAPPED_LOGIN. They are used typically to sign code. And you cannot use a certificate mapped login to connect with SQL Server. Please see this ...


4

The hashing method is presumably an implementation detail which may or may not change in future releases (as it has at least once already). They're telling you not to do it in order to absolve themselves of breaking your scripts/automation if you try to run them on newer or older releases. It's partially supported purely to allow for migrating logins. At ...


4

That stored procedure sp_change_users_login only reports on SQL users, not Windows users. Here is the actual reporting query that the stored procedure uses (you can get the stored procedure text with sp_helptext 'sp_change_users_login'): select UserName = name, UserSID = sid from sysusers where issqluser = 1 and (sid is not null and sid <> 0x0) ...


4

Thomas has explained why that stored procedure isn't capturing orphaned Windows users, but here is how you can check: SELECT p.name FROM database_name.sys.database_principals AS p WHERE [type] IN (N'U', N'G') AND NOT EXISTS ( SELECT 1 FROM sys.server_principals AS sp WHERE sp.sid = p.sid ); If you need to do this for all databases, you can generate ...


3

Please stop looking at syslogins. This is a deprecated system table that is now a backward compatibility view and is only there for that purpose (to prevent breaking old code). The user is dbo probably because they created the database. You should be able to find the login the user is associated with using: SELECT login_name = sp.name, user_name = dp.name ...


3

The only way a login has rights to a server is if those rights have been granted. The question is likely: How did these rights get granted? If DOMAIN\USER_A is a member of some Windows group, then through the rights granted to the group the DOMAIN\USER_A login exists in the server. This is true even if there is no individual entry in ...


3

Well Management Studio, for example, by default, only asks for (and only requires) server name and credentials. You can override these if you bother going into Options, but most people don't. Do you really want all of your users connecting to master? In a lot of shops this can cause quite a bit of headache, especially with loose security. If users have the ...


3

to do it correctly is not very simple, although once setup, your done. Data users or application roles default have database scoped permissions. Database users are linked to Logins and therefore can have server wide permissions from their Login. Application roles aren't linked and can only get server wide permission by using a form of impersonation. Any ...


3

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 ...


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

My answer... From my comments: The issue was that the server was set to "Windows Authentication Mode" only. To fix this I Right click the server - > Properties Click "Security" in the left side of the "Server Properties" dialog Changed server Authentication to "SQL Server and Windows Authentication mode" Clicked "OK" Restarted Associated services. At ...


2

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 ...


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

The error indicates that the SQL Server Service does not have permissions to the folder that the mdf file is located in. Since SQL Server 2008 the account running SQL Server has by default a minimal permissions set. If you want it to access files in non default directories, you need to explicitly grant access to that location. In SQL Server 2012 things are ...


2

You could assign the current context to a cookie then revert to that cookie when you desire to.


2

As pointed above by Jeremy, permissions can almost always be found on the corresponding reference document within Books Online for SQL Server. On these reference documents, there is typically a section titled Permissions (typically towards the bottom of the document) which contains your desired data. CREATE LOGIN (copied directly from the reference) ...


2

For the record, the SQL Server 2012 hash format is: 0x200<4 byte salt><hash result> where the hash result is essentially: HASHBYTES('SHA2_512', CONVERT(VARBINARY,N'[password]') + CAST(0x[salt] AS BINARY(4))) SQL Server 2005 to 2008 R2 are identical except they use SHA-1 instead of SHA-512. Some reasons NOT to use the previously hashed ...


2

There is another way, which I now use in preference to the runas /netonly method. You can add the credentials to your profile in Windows using the Credential Manager found in the windows control panel. Open Credential Manager Click "Add A Windows Credential" Populate the "internet or network address" field with the name and port number of the SQL ...


2

Sure, here are the commands listed below. (Reformatted from original post for reading ease.) These can derived from clicking on Server Properties, then choosing the Security tab and changing the Login auditing settings and scripting out the code, instead of clicking on the OK button to close the dialog. This type of scripting is usually an option on ...



Only top voted, non community-wiki answers of a minimum length are eligible