The tag has no usage guidance.

learn more… | top users | synonyms

10
votes
1answer
405 views

Does sys.sql_logins.is_policy_checked mean that the policy has been checked?

When I look in sys.sql_logins, I see a column called is_policy_checked. Can I trust that my password policy has been checked for all of the logins where this column is 1?
10
votes
1answer
13k views

The login already has an account under a different user name

When I execute this SQL: USE ASPState GO IF NOT EXISTS(SELECT * FROM sys.sysusers WHERE NAME = 'R2Server\AAOUser') CREATE USER [R2Server\AAOUser] FOR LOGIN [R2Server\AAOUser]; GO I get the ...
9
votes
2answers
30k views

Connect to SQL Server with Windows Authentication in a different domain

I am trying to connect to a remote SQL Server on a VPN in a different domain. When I enter the Server name on the SQL Server and choose Additional Connection Parameters to add some extra stuff needed ...
9
votes
3answers
4k views

Is there a way to find out who changed the password for a login?

I am trying to find out who changed the password for a login in SQL Server 2008 R2. I have already checked default trace - and it does not log that event. The default trace will include these ...
8
votes
1answer
20k views

Login failed for user - Error 18456 - Severity 14, State 38

Message that SQL Server Log File Viewer shows: Login failed for user [User] Error: 18456, Severity: 14, State 38 What it actually means: Failed to open the explicitly specified database My ...
8
votes
1answer
54k views

ODBC Data Source SQL Server Connection - Login Failed For User

I have moved my database from an SQL 2005 to a server with SQL 2008. I am now trying to create an ODBC Data Source. I am using "With SQL Server authentication using a login ID and password ...
6
votes
3answers
6k views

Logins aren't syncing across Availability Groups

We have 2 servers in an AlwaysOn group. While the user accounts within each synchronized database exist on both servers, the database instance level logins only exist on one of the servers. Ie ...
6
votes
4answers
5k views

SQL Server script to delete accounts no longer in Active Directory

We have a SQL Server 2000 that will shortly be migrated to SQL Server 2005. It has years of Windows Authentication accounts created that no longer exist in Active Directory, which prevent the Copy ...
6
votes
1answer
163 views

Azure: Many Failed Login Attempts Logged

I run a SQL Server on an Azure Virtual Machine. I have noticed in the Event Logs of the server lots of failed SQL Login attempts using a ton of different usernames (none of which exist): Login ...
6
votes
4answers
4k views

T-SQL command to log in as a different user?

Is there a T-sql command, like the Oracle "Connect" which lets me change the user that I am logged in as?
5
votes
2answers
227 views

How to monitor history of logins to database if login auditing is not enabled?

I have a number of databases on multiple servers that don't seem to be used any longer. Can't find their owners, there are no processes linked to them, nothing got written to the tables for a few ...
4
votes
2answers
819 views

Why shouldn't a pre-hashed password be used with CREATE LOGIN all the time?

(For the purpose of this question, assume that I only need to support SQL Server 2008 and higher. Or to future-proof this question, let's say recent, current, and near-future versions.) The ...
4
votes
2answers
81 views

Rebinding a SQL Server user to its login after a restore with this user

After performing a restore of a SQL Server (v12.0.2000) database, I figured that a user needs to be remapped to its login (this issue seems to be known as orphan users). And one of the simplest way to ...
4
votes
2answers
926 views

How to change default database to all logins?

I'm bit new to SQL Server and I'm trying to script out Microsoft SQL Server (2005-2014) post installation activities and I got bit stuck on altering logins. Corporate policy states that none login ...
4
votes
1answer
3k views

How to link users and logins in an Availability Group?

I've got an Availability Group going and need to map some logins to database users on an active secondary replica. (Node2) Unfortunately, when I try to exec sp_change_users_login or alter user with ...
4
votes
2answers
903 views

Integrated Security Success on Master, Fails on Other Database

I'm a bit perplexed as to why my connection string is selectively succeeding on the master database in SQL Server 2005. Here are the details of my app. Any ideas where to look? I'm clearly missing ...
3
votes
2answers
341 views

Getting all databases which a sql logon can connect

I need to know all databases in a SQL Server on which a user can connect. With sys.databases I get all databases and with sys.server_principals or sys.syslogins I get all server logons. But I can't ...
3
votes
3answers
308 views

Login lifespan for password on SQL Server

What is the "lifespan" for a login with the option Enforce Password Expiration Enabled? Where can I find configuration/option to change the time?
3
votes
2answers
691 views

Database User not reported as orphaned after Windows login dropped

I have dropped a SQL Server Windows Login. I then ran the below code to check for orphaned database users. However, the database user corresponding to the dropped Windows login does not appear as an ...
3
votes
1answer
1k views

Are passwordless logins like ##MS_SQLResourceSigningCertificate## , ##MS_AgentSigningCertificate## a vulnerability?

I'm new to MS SQL Server. Colleagues working in IT security, have run an scan showing come DB users with null password. Some of them are ( # included ) ##MS_SQLResourceSigningCertificate## ...
3
votes
2answers
53 views

Prevent logins loss when restoring a database

I'm backuping and restoring databases accross two sql server (v12.0.2000). But it looks like whenever I restore a .bak file, the login I created on the database that was backuped is no more existent ...
3
votes
1answer
28 views

How to get the AD account associated to a Proxy?

on the picture below, you can see, I have a sql server job called "Backup SSAS DBS_" that Run as: PowerShell_Proxy How can I find out the AD (active directory) account associated with that Proxy ...
3
votes
1answer
59 views

How to create a login and user that can only access one schema in one database

I have executed the following: USE [MyDatabase]; GO CREATE LOGIN [MyDomain\MyAccount] FROM WINDOWS WITH DEFAULT_DATABASE=[MyDatabase]; CREATE USER [MyDomain\MyAccount] FOR LOGIN [MyDomain\MyAccount]; ...
3
votes
1answer
614 views

SQl Server 2008 R2 - sa can login but not access database

Up until this morning I had no problems accessing our database. This morning, something has changed and I can no longer access our database (called EMS_Main) using the sa login. I can log into the ...
3
votes
1answer
662 views

SQL Script to Drop old AD logins, Inactive SQL Logins and all related Users

Related Question: SQL Server script to delete accounts no longer in Active Directory Whilst the above question points out the rather handy sp_validatelogins procedure, i'd like to extend this ...
2
votes
2answers
3k views

SQL Server db user mapped to windows login not in SQL Server logins

I am on a SQL Server 2008 where I login with DOMAIN\USER_A On my database I can see this login is mapped to the user dbo of that database. What I don't understand is that DOMAIN\USER_A is nowhere to ...
2
votes
2answers
92 views

How to revert multiple impersonations?

When there are nested impersonations (as in exec as user = 'user1'; exec as user = 'user2'; exec as user = 'user1';), is there a way to revert all of them without having to use the revert command ...
2
votes
1answer
2k views

SQL admin Scripts - Enabling read only access to all databases

I would like a script that will allow our web developers read only access to all databases on our SQL server. Currently we are having to do this manually as we have no SQL expert / DB Admin. Would ...
2
votes
2answers
51 views

How to identify database users and their owned schemas before dropping server logins?

While deleting logins, there might be a possibility that there are users in databases along with chances that they own schemas in those databases. Is there a way to find this out with a single ...
2
votes
1answer
39 views

SQL Server Active Directory login groups with limitation

I'm DBA in my organization and use SQL Server 2012 and 2014. I requestd to define multiple group in active directory such as Developer, DBA, supporter and ets... and add my users to this groups. after ...
2
votes
1answer
127 views

How to programatically change the login audit settings?

Is it possible to change the login auditing settings of a SQL Server database by script, rather than through the Server Properties Security setting in the SSMS UI? I need to programatically change ...
2
votes
1answer
597 views

Handling user logins while using a multiple schema design to support multi-tenancy

I'm creating a web application (in PHP and PostreSQL) that will be used by organisations and their staff and I want to use multiple schemas to support multi-tenancy. There will be one database and I ...
2
votes
1answer
3k views

Copy SQL Logins and Permissions From One Server to Another

I need to duplicate all Logins and their associated database-level permissions from one server to another. I am using SQL Server 2008 R2 for Server A and SQL Server 2012 for Server B I am able to find ...
2
votes
1answer
1k views

Cannot Login to Fresh Installation of SQL Server 2012

I just deployed a fresh installation of SQL Server 2012 on a Windows Server 2012 Datacenter R2 environment. The installation completed with 0 warnings and 0 failures. During the installation I ...
2
votes
1answer
1k views

Why do connections to my database fail, but only sometimes?

I am having some trouble diagnosing the cause of a problem I am having with Microsoft SQL Server 2008 R2. There is an application written in C#.net that connects to the database. Occasionally the ...
2
votes
2answers
164 views

MySQL login after secure installation

I have installed mysql5.5 via secure installation procedure by using: sudo mysql_secure_installation Now when I am trying to login in MySQL from the terminal by using: sudo -u root -p The ...
2
votes
2answers
3k views

Unable to access sql database with login and password [closed]

I'm writing Windows Service in C# and inside of it I'm unable to connect to the database. This is the question I've asked recently: SqlException: Cannot open database "path\db.mdf" requested by the ...
2
votes
1answer
798 views

Mysql Getting Access denied for user 'root'@'localhost' (using password: YES) constantly in log

I have MonYog running and it is monitoring my DB Server. It gives me constant alerts that the number of refused connections has exceeded the threshold etc. Looking in the LySQL.Log I can see constant ...
2
votes
2answers
491 views

How to login in database if we have lost password? [duplicate]

Possible Duplicate: gain admin access to sql express 2008 Is there any idea if we lost our password to login in database using sql express management studio? Is there any place where ...
2
votes
1answer
62 views

Should I delete disabled windows Active Directory accounts from SQL 2008?

Is there a risk of not deleting unused Windows logins that have been disabled in AD or is it simply a housekeeping issue? e.g. Windows authentication is used to provide database access. A user leaves ...
1
vote
1answer
56 views

Preventing orphaned users in SQL Server 2008 R2

I've recently had a situation where a database was refreshed from production to staging, and the users on the database ended up orphaned. These are sql server users, not mapped to windows ...
1
vote
1answer
142 views

What is the purpose of a user's default database?

As I understand it the default database is specified so that in case you don't specify one in the connection string, it will know where to start. Is this correct? I've never seen a connection string ...
1
vote
1answer
665 views

sp_setapprole and creating users / logins

We have set up SQL Server and our Application in such a way that an SQL Server login has … well no permissions when accessing the database using any tool, except our application. This is needed ...
1
vote
1answer
199 views

Create USER and LOGIN permissions

What permission should I give to a user or role so that it can Create Login or Create User in database.
1
vote
1answer
78 views

SQL logins Audit for SQL Server 2005/2008

For the Security Audit of SQL servers 2005/8 R2, We are looking for a method which can list us SQL accounts which have not changed password for last X days. For a single login we can do it via ...
1
vote
1answer
19 views

Can I minimize maintenance of duplicated Windows + SQL Auth logins?

In our environment, SQL authentication is the norm, but Windows auth is possible when users are using RDP. As a result, each physical user has two logical logins for each instance and two logical ...
1
vote
1answer
44 views

SQL Server Builtin Logins Migration

I am in the process of scripting existing SQL Server 2008R2 logins for migration to a new server instance. I see that the existing logins contain the below... Windows Groups: ...
1
vote
2answers
117 views

ownership of users

Two related questions: Using SQL Server 2012, how does one find out who/what owns a user, and how can you set the ownership for users, if at all? To add a couple of questions: 'how to' for both of ...
1
vote
0answers
31 views

Log shipped database server user SID is created automatically

We have a production server "prod" which has a user say 'test' that lets the users connect to the application and a logon trigger which stops them from connecting to the server through SSMS. I ...
1
vote
2answers
32 views

removing sql logins from one server, after migrating to another server, but all the databases were not migra

Here are the details of the production environment: I have 2 serevrs, A1 and A2. I have completely transfered 100 logins from server A1 to A2. Server A1 contains 5 DATABASES. But I moved only 2 ...