Find Schema Associated with SYSTEM_USER or Current User of Script 1

[kjv1611]

Is there a way I can run a script to find the correct schema that is associated with a certain individual? The exact scenario I'm thinking of is in a Sandbox database. So generally speaking, I know the format of the users, but it doesn't always line up SYSTEM_USER = Schema. Apparently, there is a set definition that should be followed, but it is not always followed.

So basically, if the user's name is something like 'JusticeLeague\SuperMan' but the Schema is only 'SuperMan' for some reason, I'd like to be able to get the actual schema.

Is this possible at all? Any hints?

This seems to work, assuming the only difference is using the domain or not using the domain. Can anyone think of a better way? One that gets the exact value, regardless of how it's typed, perhaps?

SELECT SYSTEM_USER
SELECT	s.*
FROM	Sandbox.sys.schemas s 
WHERE	REPLACE(s.name,'JusticeLeague\','') = REPLACE(SYSTEM_USER,'JusticeLeague\','')

Thanks for any thoughts

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[Olaf Doschke]

>if the user's name is something like 'JusticeLeague\SuperMan' but the Schema is only 'SuperMan' for some reason

I don't understand that part of your question.

What do you expect abour a schema name, instead? Domain names and schema names have no dependency whatsoever, do they? You may have a naming convention in a company for a system (windows) user domain\schema as a owner of a schema, or something along those lines, but it's not a natural match, is it?

What is your real problem? Is it you don't know which default schema is effective, when some user logs in via windows authentication and belongs to several user groups? As you only talk about windows domain accounts and not windows user groups, I don't see a problem. So how do users authenticate in your database at all? Default schema is not really important, if you write verbose queries with full qualified names on the one side and a well organised way of authentication is allowing to use queries wuth short table names, on the other side.

As you have designed a user/owner per schema you may EXECUTE AS. Eg if you define the same procedure name in different schemas you may either execute schema1.proc and execute schema2.proc or execute proc as user='schema1owner' or as user='schema2owner', or execute proc to let the default schema apply.

What kind of dynamic schema switching do you need for what reason?

Bye, Olaf.

 

[kjv1611]

The issue is that we have a standard naming convention of DOMAIN\username as the schema for individuals in the SQL Sandbox databases. However, sometimes the DBAs forget or else someone perhaps puts in a special request that gets accepted. In my case, I didn't put in a request, but it was a goof on the DBA's part, and it's been there for a while, so I figure best not to try and change it now and just work with it. It's not normally a big deal.

So my full scenario is this:
I'm using Linked Servers
The schema for me on one server is DOMAIN\username, and on the other server it's just username
I want this to work equally as well if I manually go through the script and run it verifying everything, or just go and run it all in one swoop.
I also want to allow this script to be run by others in my absence (I'll be the main person, but I could be out sick, on vacation, whatever).

So for instance, normally, I could swap out the schema name by simply getting the SYSTEM_USER for the schema. I realize you can use the .. notation in some circumstances, but some circumstances (dynamic SQL) that does not work well or not at all.

So for some queries, I can get around it and just use double dot (..):

EXEC ('SELECT 1 FROM Sandbox..MyRemoteSandboxTable') AT LinkedServer

But in others, correct me if I'm wrong, I need to fully qualify:

DECLARE @test AS varchar(200)
SET @test = 'IF OBJECT_ID(''Sandbox.[' + [highlight #FCE94F]SYSTEM_USER[/highlight] + '].MySandboxTable'') IS NOT NULL DROP TABLE Sandbox..MySandboxTable'
EXEC (@test) AT LinkedServer

Am I making any sense? Sorry if it seems difficult to follow. Thanks for your patience.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[Olaf Doschke]

Well, you seem to have a convention along the lines of dbo being both database owner user and also default and first schema name of any new database.
How about using SCHEMA_NAME() or SCHEMA_ID()?

That is not needing this convention and would work in general, wouldn't it?

Bye, Olaf.

 

[kjv1611]

hmm... the SCHEMA_NAME() function seems interesting. But it's giving me dbo (ID is 1). Yes, that is used for system tables, but not sandbox tables.

I wonder if there's a way I can get the schema for the logged in user by using something within the SCHEMA_NAME() function? I'm going to test around and see what I can come up with.


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

OK, this may be a long way around it, but here's what I've come up with that seems to work:

DECLARE @SchemaID AS int
DECLARE @SchemaName AS varchar(100)
SELECT @SchemaID = s.schema_id FROM Sandbox.sys.schemas s WITH (NOLOCK) WHERE REPLACE(s.name,'DOMAIN\','') = REPLACE(SYSTEM_USER,'DOMAIN\','')
SELECT @SchemaName = s.name FROM Sandbox.sys.schemas s WITH (NOLOCK) WHERE REPLACE(s.name,'DOMAIN\','') = REPLACE(SYSTEM_USER,'DOMAIN\','')

SELECT @SchemaID ,@SchemaName

Does anyone see anything wrong with this method? Thoughts? One possible hiccup I could see is if there were a typo when the DBA creates the schema, but otherwise, seems like it should work just fine.


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[Olaf Doschke]

Well, how about executing AT LinkedServer, as you want the SCHEMA_NAME there?

[pre]EXEC ("SELECT SCHEMA_NAME()") AT LinkedServer[/pre]

Bye, Olaf.

 

[kjv1611]

Yeah, I didn't show the linked server portion for that, but that is definitely my plan. Let me test..


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Works BEAUTIFULLY. Here's what I've got:

EXECUTE (
			'
	DECLARE @SchemaID AS int
	DECLARE @SchemaName AS varchar(100)
	SELECT @SchemaID = s.schema_id FROM Sandbox.sys.schemas s WITH (NOLOCK) WHERE REPLACE(s.name,''DOMAIN\'','''') = REPLACE(SYSTEM_USER,''DOMAIN\'','''')
	SELECT @SchemaName = s.name FROM Sandbox.sys.schemas s WITH (NOLOCK) WHERE REPLACE(s.name,''DOMAIN\'','''') = REPLACE(SYSTEM_USER,''DOMAIN\'','''')

	SELECT @SchemaID ,@SchemaName
			'
		)
	AT LinkedServer

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57