Strange user access issue in SQL 2014!!!!

Question

Post reply

Strange user access issue in SQL 2014!!!!

theflyingdba

Mr or Mrs. 500

Points: 555
More actions
February 25, 2016 at 3:00 am

#305028

I recently promoted a database from a SQL 2008 R2 development server into a SQL 2014 production server, keeping compatibility for the database as 2008R2.
I also created the user AD group login with read/write access only using datareader/writer.
The database was restored as a non contained database, so required both instance and database logins/users to be linked for access to be obtained, as per the norm.
A user came down today to say that he has been able to create a new view in production, when he thought he shouldn't be allowed to.
I carried out an investigation and found that an admin AD group was still defined within the database itself and was a member of dbowner for this particular database (a hang over from development!), however there was no associated login within the sys.syslogins view in master?!?
So, my question is, how can what is effectively an orphaned user in a new environment still gain access to the database and carry out operations as the dbowner?
Is this a "feature" in 2014 that I missed, or is there a more simple explanation?
I have now removed the orphaned user in the database and removed it membership of the dbowner role for that database, so all is well... but just wanted to find out how this happened?
Thanks a lot in advance...
Haden
Lead level SQL DBA currently working for a well established Insurance organisation in London.
www.linkedin.com/in/hadenkingslandleaddba

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

As far as I can remember, it has always been like that. At least from 2005.

You can reproduce the behaviour easily:

1. Create a windows group 'testGroup' in lusrmgr.msc

2. Create a windows user 'testUser' and add it to the group

3. Run this script:

CREATE DATABASE test

GO

USE test

GO

CREATE USER [COMPUTER\testGroup]

-- User created, no matching login, but the SID is taken from windows

SELECT * FROM sys.database_principals

ALTER ROLE db_owner ADD MEMBER [COMPUTER\testGroup]

CREATE LOGIN [COMPUTER\testUSer] FROM WINDOWS

-- Again, no matching login

SELECT svp.name AS login_name, dbp.name AS user_name

FROM sys.database_principals AS dbp

INNER JOIN sys.server_principals AS svp

ON dbp.sid = svp.sid;

Now open a command prompt and run

runas /USER:COMPUTER\testUser cmd

Type the password and a new command window will open.

Now type:

sqlcmd /Syourservername

In the sqlcmd window type

CREATE VIEW someView AS SELECT 1 AS One

And it will work.

Bottom line:

There is a login that lets the windows user authenticate and there is a user for the windows group that grants privileges to the user.

-- Gianluca Sartori

theflyingdba Mr or Mrs. 500 Points: 555 More actions · Answer 2

Thanks... I thought that the ability to do this didn't exist from within SSMS, which is where the user issues the create view query.

I have never come across it, as all unwanted logins/groups are removed when promoted to production, but this one was missed!

thanks a lot...

Haden.

Lead level SQL DBA currently working for a well established Insurance organisation in London.
www.linkedin.com/in/hadenkingslandleaddba