I am installing SQL server express on the machine using AdvancedInstaller. AdvancedInstaller calls the following script to (successfully) create the DB:
-- Inline SQL script
IF NOT EXISTS(SELECT name FROM master.sys.databases WHERE name = N'MyDB')
BEGIN
create database MyDB
END
As the installer continues, it also creates tables and stored procs (successfully) via the following SQL script:
USE[MyDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS(SELECT* FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Computers]') AND type in (N'U'))
BEGIN
CREATE TABLE[dbo].[Computers](
[ID]
[uniqueidentifier]
NOT NULL,
[Name] [nvarchar](50) NOT NULL,
CONSTRAINT[PK_Computers] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON[PRIMARY]
) ON[PRIMARY]
END
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS(SELECT* FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Computer_Add]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Computer_Add] AS'
END
GO
ALTER PROCEDURE[dbo].[Computer_Add]
-- Add the parameters for the stored procedure here
@Name nvarchar(50),
@ID uniqueidentifier output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
set @ID = NEWID();
-- insert the user into the DB.
insert into[Computers]
Values(
@ID,
@Name
)
END
To uses this DB, I have a C# DLL that has a 'Db.ConnectionWrapper' access class. That class has a method called 'OpenConnection' which contains this:
private SqlConnection _connection;
public SqlConnection Connection
{
get { return _connection; }
}
private void OpenConnection()
{
_connection = new SqlConnection(connectionString);
_connection.Open();
}
'connectionString' is a string property that contains the following:
Server=localhost\\SQL_EXPRESS;Integrated security = SSPI; database=MyDB;
Joe is in the Administrators group.User "Joe" runs the AdvancedInstaller program which first installs SQL server express and then AdvancedInstaller runs the script to create and install the DB. After the install is (successfully) completed, Joe then runs a windows desktop program that uses the DLL to access the DB. The code to access the DB works flawlessly. Joe then logs out from the server computer.
However, when "Bob" logs in to the server computer (again another user in the Administrator group) and runs the same windows desktop program, the following exception is thrown when the code tries to open the connection to the DB:
System.Data.SqlClient.SqlException(0x80131904): Cannot open database "MyDB" requested by the user.The login failed.
Login failed for user 'MPS\Bob'.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at Db.ConnectionWrapper.OpenConnection()
My question is why?
EDIT1:
Could it be because I do NOT have this statement during the creation of the DB:
ALTER DATABASE [PMInfrastructure] SET MULTI_USER
Update to EDIT1: Database is already in MULTI_USER mode. - so no is the answer to this.
EDIT2
SQL server is installed (successfully), by AdvancedInstaller, using a silent installation with ONLY these command line parameters:
/q /IACCEPTSQLSERVERLICENSETERMS /ACTION=Install /FEATURES=SQLEngine /INSTANCENAME=SQL_EXPRESS /UpdateEnabled=False
SQL actions taken
(per Raul Garcia's suggestion)
1> sqlcmd -E -S .\SQL_EXPRESS
2> go
1> USE [PMInfrastructure]
2> go
1> select name from sys.Login_token order by name
2> go
name
-------------------------------------------------
\Everyone
\LOCAL
BUILTIN\Administrators
BUILTIN\Users
BUILTIN\Users
CETRUS\SophosUser
MPS\Domain Users
MPS\SophosUser
NT AUTHORITY\Authenticated Users
NT AUTHORITY\INTERACTIVE
NT AUTHORITY\REMOTE INTERACTIVE LOGON
NT AUTHORITY\This Organization
public
(13 rows affected)
1>
Here is what I got when I ran the commands
1> ALTER ROLE [db_datareader] ADD MEMBER [BUILTIN\Administrators]
2> ALTER ROLE [db_datawriter] ADD MEMBER [BUILTIN\Administrators]
3> GO
Msg 15151, Level 16, State 1, Server CETRUS\SQL_EXPRESS, Line 1
Cannot add the principal 'BUILTIN\Administrators', because it does not exist or
you do not have permission.
Msg 15151, Level 16, State 1, Server CETRUS\SQL_EXPRESS, Line 2
Cannot add the principal 'BUILTIN\Administrators', because it does not exist or
you do not have permission.
1>
SQL actions #2
C:\Program Files\Microsoft SQL Server\MSSQL12.SQL_EXPRESS\MSSQL\Binn
sqlcmd -E -S .\SQL_EXPRESS
use master
GO
CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=PMInfrastructure
GO
USE [PMInfrastructure]
GO
ALTER ROLE [db_datareader] ADD MEMBER [BUILTIN\Administrators]
ALTER ROLE [db_datawriter] ADD MEMBER [BUILTIN\Administrators]
GO
quit
SQL Actions #2 Results:
C:\Program Files\Microsoft SQL Server\MSSQL12.SQL_EXPRESS\MSSQL\Binn>sqlcmd -E -S .\SQL_EXPRESS
1> use master
2> GO
Changed database context to 'master'.
1> CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=PMInfrastructure
2> GO
1> USE [PMInfrastructure]
2> GO
Changed database context to 'PMInfrastructure'.
1> ALTER ROLE [db_datareader] ADD MEMBER [BUILTIN\Administrators]
2> ALTER ROLE [db_datawriter] ADD MEMBER [BUILTIN\Administrators]
3> GO
Msg 15151, Level 16, State 1, Server CETRUS\SQL_EXPRESS, Line 1
Cannot add the principal 'BUILTIN\Administrators', because it does not exist or
you do not have permission.
Msg 15151, Level 16, State 1, Server CETRUS\SQL_EXPRESS, Line 2
Cannot add the principal 'BUILTIN\Administrators', because it does not exist or
you do not have permission.
SQL actions #3
I found this script:
USE master;
GO
CREATE LOGIN [BuiltIn\Users] FROM WINDOWS;
GO
USE PMInfrastructure;
GO
CREATE USER [BuiltIn\Users] FROM LOGIN [BuiltIn\Users];
GO
CREATE ROLE rSupport;
GO
EXEC sp_addrolemember 'rSupport', 'BuiltIn\Users';
GO
GRANT SELECT, INSERT,UPDATE, etc ON Mytable TO rSupport;
GO
I have two questions:
SQL_A_3_1: Since the sp_addRoleMember is deprecated I did this ?
ALTER ROLE rSupport ADD MEMBER FROM USER [BuiltIn\Users]
SQL_A_3_2: I then did this ?
GRANT ALL TO rSupport;
thanks
The most likely reason why the application works when “Joe” installed SQL & runs the app, and fails when “Bob” runs the app is that “Joe” is very likely a member of sysadmin
group while “Bob” is not.
I noticed that the script to create the database doesn’t include the creation of any user, which implies that only sysadmin members would be able to connect to the database (not a recommended setting).
To know if I am correct, try the following using sqlcmd
running as both Joe & Bob (i.e From the command line run sqlcmd -E -S .\sqlexpress
).
SELECT name, type, usage FROM sys.login_token order by usage, type, name
If my theory is correct, you will see that Joe has an entry similar to this one, indicating that he is a member of sysadmin, while Bob doesn’t include it:
sysadmin SERVER ROLE GRANT OR DENY
You can solve this by creating a user for Bob (or better yet for a Windows group that includes Bob & Joe), and grant him the necessary access to the database. For example:
USE [myDB];
go
CREATE USER [MPS\MyDB_group];
go
ALTER ROLE [db_datareader] ADD MEMBER [MPS\MyDB_group];
ALTER ROLE [db_datawriter] ADD MEMBER [MPS\MyDB_group];
Go
Please let us know if my original assumption is not correct, and we will recommend a different strategy to find out the root cause of the problem.
Thanks,
-Raul Garcia
SQL Security
Run Update-Database
command after add-Migration
. It will locate the database, and there won't be login failures anymore.
User contributions licensed under CC BY-SA 3.0