Solved

SQL Server - Schema

Posted on 2014-03-12
10
352 Views
Last Modified: 2014-04-07
I have two set of tables with the same name/structures/data but different schema; one set of tables with "TRP" schema and another set of tables with "DEV" schema. The TRP schema is for the production tables, and the DEV schema is for the testing tables.

I have a procedure to run using those tables. There are occasions where I need to run this procedure using the testing tables (tables with DEV schema). How do I specify in my procedure which set of tables to use on the top so that I don't have to go into every lines in the codes and change the schema of the table?

My procedure is very long, I'm just pasting here the first few lines of my procedure so that you can tell me where and how and can modify my procedure to use the DEV or the TRP set of tables in the procedure.

ALTER PROCEDURE [TRP].[FFS_validation_check]
      -- Add the parameters for the stored procedure here
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

--updating DealStatus on APC/DRG according to CTR
exec TRP.sp_FFS_updDealStatus_MDCR_MPPO

declare @intCheckCount smallint

-- reset TRP.Validation_check/detail
DELETE FROM TRP.Validation_Check where source = 'ffs'
DELETE FROM TRP.Validation_Check_detail where source = 'ffs'

--PRINT '1-- Check_if Year <> Year Plan/Act'
INSERT INTO [PSF_Gen].[TRP].[Validation_check](Insert_dt,[Source],[Check_point],[Rec_count])
SELECT getdate(), 'FFS', 'Check_01',
count(*) as Check_1 from (
SELECT      'Check_01' as Check_point_1, FFSContr_ID, Par_id, Trend_type, TypeOfContract, [Year], year(PlanEffDt) as Year_Plan, year(ActEffDt) as Year_Actual, DealStatus, max(updatedt) as UpdateDt
FROM      TRP.FFS_Contracts
WHERE      year(PlanEffDt) <> 2009 and (year(PlanEffDt) <> [Year] and year(ActEffDt) <> [Year])
            and Par_id not in ('770324630 A','940535360 A','951816017 A','952282647 A','952294234 A','956064971 A')
GROUP BY FFSContr_ID, Par_id, Trend_type, TypeOfContract, [Year], year(PlanEffDt), year(ActEffDt), DealStatus
) x having count(*) <> 0
0
Comment
Question by:HNA071252
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 3

Assisted Solution

by:Rita
Rita earned 375 total points
ID: 39925305
Easy solution:
Remove all references to schema name in the proc and add the below SQL at the top. It will execute all the queries in the tables under the default schema.
ALTER USER currentuser WITH DEFAULT_SCHEMA = TRP
Remember to change the default schema back.

Best practice is to create different users in SQL with default schema set to either TRP or Dev or PROD. So User1 has default schema set to DEV, User2 to TRP and User3 to PROD.
Remove all references to schema name in the procs. So when the procs are executed by User1, tables from DEV schema will be used and procs executed by User2 will use tables from TRP. SPs need not be modified. This will also ensure that during development TRP tables do not get corrupted accidently due to wrong default schema set.
0
 

Author Comment

by:HNA071252
ID: 39925325
Thanks for a quick response, it seems easy. Another question: how do I reference the default schema from a table instead of hard code in the procedure? I will create a new field call "Default_schema" in the table TRP.Param. I will change the value in this table to either TRP or DEV whenever applicable. How do I have modify this line to refer to the value of the default schema in the table?

ALTER USER currentuser WITH DEFAULT_SCHEMA = TRP

Because I have many procedures to run and I don't want to changes every procedure each time, I just need to change one in the Param table.
0
 
LVL 3

Assisted Solution

by:Rita
Rita earned 375 total points
ID: 39925383
I would suggest that you go in for the second option as this will resolve this query also. You need not refer to schema name in any of the procedures. Change the user who executes the procedure. It would be a bit of extra work but it is neat and secure !!!

Also this is a chicken and egg question. Unless you know which schema to access, how will you know whether to get the data from TRP.Param or Dev.Param? :-)
You can have a dbo.Param table that has a field called Current_Schema with value as TRP or DEV. In the proc you can get the value as:
Select @current_schema = current_schema from dbo.Param
ALTER USER currentuser WITH DEFAULT_SCHEMA = @current_schema
Woes this work? Not sure as I haven;t tried it. The user has to be given permission to access this table from dbo schema.
Also if the user belongs to sysadmin group, default schema cannot be changed. Check this:
http://msdn.microsoft.com/en-us/library/ms176060.aspx
"The value of DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin fixed server role. All members of the sysadmin fixed server role have a default schema of dbo."
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:HNA071252
ID: 39925597
How do I go with the second option? Please show me how do I  create different users in SQL with default schema set to either TRP or Dev? I'm still learning about the sql server.
0
 
LVL 3

Assisted Solution

by:Rita
Rita earned 375 total points
ID: 39925951
This should help you:
http://technet.microsoft.com/en-us/library/ms173463.aspx

CREATE USER user_name
WITH DEFAULT_SCHEMA = schema_name

CREATE USER hna071252_TRP
WITH DEFAULT_SCHEMA=TRP


CREATE USER hna071252_DEV
WITH DEFAULT_SCHEMA=DEV

Grant access to users on database so that it can execute stored procedures and have appropriate access on tables (select, modiy, delete) (Refer to http://technet.microsoft.com/en-us/library/ms178569.aspx)
0
 

Author Comment

by:HNA071252
ID: 39926911
How do I use this line?

ALTER USER currentuser WITH DEFAULT_SCHEMA = TRP

What do I replace currentuser with? How do I know what is my currentuser?
0
 

Author Comment

by:HNA071252
ID: 39926931
I got this error message:

Cannot alter the user 'currentuser', because it does not exist or you do not have permission.
0
 

Author Comment

by:HNA071252
ID: 39926953
When I run this "select current_user" it shows I'm a "dbo"

So I changed the code like this:

ALTER USER dbo WITH DEFAULT_SCHEMA = Dev

and I got this error:

Cannot alter the user 'dbo'.
0
 
LVL 3

Expert Comment

by:Rita
ID: 39928074
Yes because dbo is a default user created by SQL and hence you cannot alter the properties of dbo.
'currentuser' was just a reference and is not a keyword. You will have to use actual user id. Create a user and if you are running the SP in SQL mgmt studio, connect to SQL using this user id.
eg.
CREATE USER TRP_user
WITH DEFAULT_SCHEMA=TRP

Login into SQL using TRP_user user id and then execute the SP.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 125 total points
ID: 39928094
What do you base your SQL logins on?

Typically they're based on AD groups (that's what DBAs and other admins usually prefer, as that  makes security much easier to manage overall).  But logins based on AD groups can't specify a default schema.

You can, and should, create separate (native) SQL Server logins that can have a default schema specified.  But don't switch default schemas back and forth.  Instead, create a separate user for each default schema you need.

[I've often had a bear of a time getting default schemas and unqualified table names to work as you'd want in SQL Server, particularly when the original proc was created by a sysadmin, but hopefully you'll have better luck.]
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question