Hot answers tagged

20

When you have special characters in a name put []'s around it to let SQL know that it's an identifier. This is also how you manage special characters. So in your case Drop Login [John-PC\John]


17

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


16

No. While the documentation currently has the following arguably ambiguous statement about what this flag means: Password policy is checked. What it really means, and should say, is that the flag serves two purposes: The password policy might have been checked, but only if (a) the password policy was enabled at the time the password was last ...


15

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


12

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.


12

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


11

If you just have one login, then manually quoting it with [ ] will work. If you have many of them then you have to build a dynamic sql like below to progrmatically get the drop login [login_to_drop] from sys.server_principals using QUOTENAME() tsql DECLARE @sql NVARCHAR(MAX) = N''; SELECT @sql+= N'DROP LOGIN ' + QUOTENAME(name) + ';' FROM ...


10

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


9

My article will help if you set it up in advance, but not when the event happened in the past and you didn't have any kind of auditing mechanism set up. There is still hope, though. Let's say I did this: CREATE LOGIN flooberella WITH PASSWORD = N'x', CHECK_POLICY = OFF; This information is in the default trace under EventClass 104 (Audit Addlogin Event). ...


8

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


8

You should be able to do something like this EXECUTE AS login = 'loginname'; SELECT name FROM sys.databases WHERE HAS_DBACCESS(name) = 1 For reference see EXECUTE AS and HAS_DBACCESS


6

Is the TCP port SQL Server is listening on open globally? If so, yes I'd be concerned. If there is a password that can be brute-forced or guessed, or an exploit that allows someone to bypass authentication, eventually your database could be compromised. You'll also be vulnerable to attacks that don't require access, such as someone filling up the drive that ...


5

This should put you in the right direction: DECLARE @results TABLE ( database_name sysname, db_user_name sysname, login_name sysname ); INSERT @results EXEC sp_MsForEachDB ' USE [?]; SELECT DB_NAME() AS database_name, dp.name AS db_user_name, sp.name AS login_name FROM sys.database_principals dp INNER JOIN sys.server_principals sp ...


5

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


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

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

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

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

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

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

Per the following article, This information can be found in: Start -> All Programs -> Local Security Policy Then navigate to Account Policies -> Password policy You should see something similar to this: http://www.mssqltips.com/sqlservertip/1088/sql-server-login-properties-to-enforce-password-policies-and-expiration/


4

This isn't an answer to the actual question asked, but commentary too long for a comment. FWIW I don't create SQL Auth logins that obey Windows password policies. I can avoid that simply with: CREATE LOGIN foo WITH PASSWORD = N'bar', CHECK_POLICY = OFF; If I want logins to use Windows-based password policies, I'll make users log in with Windows ...


4

You should never try to update system tables directly, in most cases it is not going to let you as you have found. In your case you will want to build out a dynamic statement for the ALTER LOGIN without knowing how many there are on the instance. However, you will need to be cautious in doing this to ensure you do not touch logins you shouldn't. This query ...


4

I blogged about it exactly one month ago. Since link-only answers are discouraged and pasting here the whole blog post would be ridiculous, here is a summary of what you will find there. Basically, you have to record index usage in a user table, in order to make sure that server restarts and index maintenance don't delete entries for the databases you're ...


4

SQL Server will deny access to a login that has been disabled. This is by design. If it is your intention to allow the person access through the group login, you should simply remove the login that is linked to the Windows account. SQL Server limits principals' rights such that any rights that have been explicitly denied override rights that have been ...


3

Unless you are actually deleting the user accounts in Active Directory and re-creating them, you don't actually need to do anything to the logins on SQL Server. SQL Server references the accounts using the Active Directory SID, not the name. You can test this by renaming an account in Active Directory and attempting to login to SQL Server with it. ...


3

The action described in the question should not be a problem. I was able to create a Post Deployment script and add the following code: USE [tempdb]; GO PRINT DB_NAME(); IF (USER_ID(N'g') IS NULL) BEGIN PRINT 'Creating user [g]...'; CREATE USER [g] WITHOUT LOGIN; END; ELSE BEGIN PRINT 'Dropping user [g]...'; DROP USER [g]; END; It builds ...


3

No, if the user connects to SQL Server using a SQL Authentication login, there is no way to determine from that which Windows login was responsible. SQL Server can only record the information it has been provided, and when you use SQL Authentication, no Windows login / domain / group information is passed to SQL Server. You can look at DMVs like ...


3

As I understand it, this is because the SID's of the logins don't match between our two environments. You understand correctly! How could I make it so that the SID's of the sql server logins on our staging environment are the same as on our production environment going forward? My thought is to use CREATE LOGIN WITH SID ='SID value from PRD' ...



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