November 1, 2017 at 4:56 am
We have an application which uses a specific db user to connect which is called say Appdbuser
Appdbuser is a database user in the database it needs, however there is no login account of that name os SID that matches.
In AD Appdbuser is a grandchild of an account called say SysGlobalUser.
SysGlobalUser has a login and has fixed and explicit permissions to databases within the system.
I have used profiler and the NT user name is showing as Appdbuser which is connecting and working correctly.
Can anybody explain or point me to the relevent documentation on how this works
November 1, 2017 at 5:24 am
AD and group permissions are cumulative; which is why this works.
So, you have a User, which is part of the AD Group SQLUsers. That Group has permission to login into the SQL Server and also have the db_reader role on the databases Finances and Customer. A member of that group is the user NT/Thom (that's me!). That person has a user on the database StaffRecords and Finances. In the StaffRecords database they are a db_owner, however, they have DENY Select on the Customer database. If NT/Thom (I) were to try to run a SELECT statement on the datbase Customer, I would receive a DENY; as a DENY over rules a GRANT. On the other hand, if I were to CREATE a TABLE on the database StaffRecords, that would be fine, as I have db_owner permissions.
I would be able to log in, as I am part of SQLUsers AD group.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 1, 2017 at 5:52 am
Hi Thom
Thank you for the reply.
In your test scenario NT/Thom is a database user of database StaffRecords.
Is there a sql server user for NT/Thom, and if not how does one create an orphaned user in a database?
The reason for asking this question, using your scenario, is that the system database msdb has a number of generic sql routines and tables used by multiple databases. The AD group SQL Users has been given explicit permissions to insert intro msdb tables and select/execute the sql routines in the msdb database. There is also a Sequence set up which one of the routines uses.
This what happens, the next sequence is used by user NT/Thom in one of the sql routines but the sql in the sql routines is not run and not logged. However if I run the same calling procedure as a different user with permissions on sql server it runs correctly.
I realise without the real code this may be difficult to answer.
KR Graham
November 1, 2017 at 6:56 am
No, there is no SQL Login for NT\Thom.
So, as a quick test, I logged into our Sandbox server and ran the following (some details obscured):CREATE LOGIN [NT\TestUsers] FROM WINDOWS WITH DEFAULT_DATABASE=master, DEFAULT_LANGUAGE=British;
TestUsers being an AD Group.
I then did the following:CREATE USER [NT\tuser] FOR LOGIN [NT\tuser] WITH DEFAULT_SCHEMA=dbo;
GO
ALTER ROLE db_owner ADD MEMBER [NT\tuser];
GO
tuser is a member of the group TestUsers. Notice I did NOT create a login for that user.
I then started up a Powershell Terminal, running as the user tuser and ran the following:PS C:\Windows> sqlcmd -S srvsql2012dev\Sandbox
1> USE Sandbox;
2> GO
Changed database context to 'Sandbox'.
1> CREATE TABLE test (string varchar(10));
2> GO
1> INSERT INTO test
2> VALUES ('hello,'),('did'),('this'),('work?');
3> GO
(4 rows affected)
1> SELECT *
2> FROM test;
3> GO
string
----------
hello,
did
this
work?
(4 rows affected)
1> DROP TABLE test;
2> GO
1> SELECT name
2> FROM sys.tables;
3> GO
name
--------------------------------------------------------------------------------
------------------------------------------------
(0 rows affected)
1> exit
Notice that all the commands ran successfully. That's because, firstly, the Domain User authenticated to the Server via the AD Group, and was permitted to login. Then, when I went to use the Sandbox database, it authenticated as NT\tuser. That user exists in that database, with the Windows Credentials, thus authenticated successfully. As the user was then part of the db_owner role, they could create tables, insert, select, etc.
All make sense?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 1, 2017 at 7:43 am
Hi Thom
Thanks for this.
So as you have stated you can create an orphaned user in a database.(tuser)
If that user has inherited AD permissions from another group (Testusers) ,which does have a logon, the orphaned user will show as hitting the database as the orphaned user and will logon with credentials from group testUsers
However a question from the scenario from your last post can the tuser only manipulate objects in the Sandbox database, although TestUser may have permissions in other databases. ie tuser uses TestUsers to login only and reverts to its permissions only in the database of which its a user.
If the answer to the above is yes, I presume the routines in msdb will need to be put into the database of which tuser has permission as database user tuser is not known outside of database Sandbox.
Thank you very much for taking the time to show your responses with code examples
KR Graham
November 1, 2017 at 8:04 am
As I said before, permissions are cumulative.
If the Group NT\TestUsers has a User on another database, such as msdb, then NT\tuser will have those permissions; they are a member of NT\TestUsers. If there were another database, Customers, which did not have the user NT\TestUsers or the user NT\tuser, then NT\tuser would be unable to access that database.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply