Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Schema confusion Expand / Collapse
Author
Message
Posted Thursday, March 27, 2014 11:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:49 AM
Points: 51, Visits: 137
I've been a SQL DBA for decades, but I've never used schemas. I've never had a need to. Now I'm working in a pre-existing server that is using them, but I'm not convinced they're being used correctly. I've looked all over the place, including BOL, but can't find my answer.

The problem:
There's a stored procedure that executes an agent job (EXEC msdb.dbo.sp_start_job)
That stored procedure has GRANT EXECUTE ON RunSqlJob_CreateIBMImportFile TO [xxxxxx\RPTGroup_SOES]
xxxxxx\RPTGroup_SOES is defined as a User for the database the sproc lives in
xxxxxx\RPTGroup_SOES is also defined as a Schema for that same database
xxxxxx\RPTGroup_SOES is NOT defined as a Login for the server

I can't figure out what the xxxxxx\RPTGroup_SOES schema even owns, if anything, or who has what permissions to the schema, if anyone.

I know that I may have actually just described 2 separate problems/concepts

The lead developer has another developer testing the sproc in SSMS and this is the exact verbiage I was given...

"229 is ‘The %ls permission was denied on the object '%.*ls', database '%.*ls', schema '%.*ls'.’ You are a member of the RPTGroup_SOES AD group so you should have execute permissions."

1st off, I don't know what all the %blah blah ls% blah is supposed to translate to, but I do get that it's a permissions issue surrounding RPTGroup_SOES.

I also gather that there is an Active Directory group named RPTGroup_SOES, but if that is the case, why not create a server-level Login for that group? What does the schema have to do with the AD group?

Ultimately, my goal is to figure out what has to have what permissions to make this sproc successfully execute, so it will successfully execute the sql agent job.
Post #1555563
Posted Thursday, March 27, 2014 11:35 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
robin.pryor (3/27/2014)
I've been a SQL DBA for decades, but I've never used schemas. I've never had a need to. Now I'm working in a pre-existing server that is using them, but I'm not convinced they're being used correctly. I've looked all over the place, including BOL, but can't find my answer.

Well, you're asking a lot of questions here, I'll try to answer them as best I can.


The problem:
There's a stored procedure that executes an agent job (EXEC msdb.dbo.sp_start_job)
That stored procedure has GRANT EXECUTE ON RunSqlJob_CreateIBMImportFile TO [xxxxxx\RPTGroup_SOES]
xxxxxx\RPTGroup_SOES is defined as a User for the database the sproc lives in
xxxxxx\RPTGroup_SOES is also defined as a Schema for that same database
xxxxxx\RPTGroup_SOES is NOT defined as a Login for the server

"xxxxxx\RPTGroup_SOES" is an AD group that is mapped directly as a user to the DB without having a corresponding Login. IIRC, this is an archaic, deprecated, though still supported practice. If it bothers you, you should be able to add the corresponding Login, but linking the two up might be tricky (I can't remember right now).

There is also a Schema with the same name. I am not sure how or why, but in some databases, matching schemas get created whenever a user is added. I hate this practice for anything other than multi-user development databases and I generally remove any schemas that don't have any objects in them.


I can't figure out what the xxxxxx\RPTGroup_SOES schema even owns, if anything, or who has what permissions to the schema, if anyone.


Technically a Schema isn't a Principal (Users and Roles are), so it doesn't own anything. It can, however, contain almost any DB level SQL Objects.

I like to think of Schemas as the equivalent of filesystem folders (except that they only have one level). They can be owned by someone, they can contain other objects that are then (usually) owned by the owner of the schema, and you can set access controls on them instead of having to set them on every object in the folder individually.

Try this command to see what is in the schema:
select SCHEMA_NAME(schema_id), * 
from sys.objects
Where SCHEMA_NAME(schema_id) = 'xxxxxx\RPTGroup_SOES'

Figuring out who has access to a schema is the same as figuring out who has access to a table or view.


The lead developer has another developer testing the sproc in SSMS and this is the exact verbiage I was given...

"229 is ‘The %ls permission was denied on the object '%.*ls', database '%.*ls', schema '%.*ls'.’ You are a member of the RPTGroup_SOES AD group so you should have execute permissions."

1st off, I don't know what all the %blah blah ls% blah is supposed to translate to, but I do get that it's a permissions issue surrounding RPTGroup_SOES.

This is the error message template for error #229. Apparently your lead developer only had the error number and not the actual error message, which is unfortunate because permissions problems can be complex and it's not necessarily the schema that's causing the problem.


I also gather that there is an Active Directory group named RPTGroup_SOES, but if that is the case, why not create a server-level Login for that group? What does the schema have to do with the AD group?

As above, the schema was probably created for the group and is probably owned by the group. You may very well not need it.



-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #1555588
Posted Thursday, March 27, 2014 12:00 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
Ultimately, my goal is to figure out what has to have what permissions to make this sproc successfully execute, so it will successfully execute the sql agent job.


Unfortunately, automated submission of SQL Agent Jobs tends to be messy and difficult, and may involve some security compromises.

IIRC, this is the minimum that you have to do (fair warning, there may be more):

1. Grant the user group access to the stored procedure. You have already done this.

2. Make the DB user a Login also, and link them up.

3. Give the Login access to [msdb] and the "SQLAgentUserRole" in msdb.

4. Alter the source database to be "Trustworthy".

Note that step #4 is a significant step and should not be made lightly. If your application database is not truly secure and trustworthy, turning this on exposes your whole SQL Server to the security flaws of this one database.

There are a couple of other ways to do this without rewriting the Proc, but they are more complicated.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #1555595
Posted Thursday, March 27, 2014 12:08 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:49 AM
Points: 51, Visits: 137
Sorry the original "question" is so all over the place. Your response was very enlightening actually.
Post #1555598
Posted Thursday, March 27, 2014 12:46 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 11:13 AM
Points: 15,527, Visits: 27,909
Came over here to answer your question and I find that Barry has already covered it better than I can. I'm out!

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1555607
Posted Thursday, March 27, 2014 1:20 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 1:54 PM
Points: 3,972, Visits: 2,983
RBarryYoung (3/27/2014)

There is also a Schema with the same name. I am not sure how or why, but in some databases, matching schemas get created whenever a user is added. I hate this practice for anything other than multi-user development databases and I generally remove any schemas that don't have any objects in them.

I've seen this happen when a user is created without specifying a default schema. The same is true if you use the GUI (awk) or SQL.

This will create a user and schema of the same name:
create user [test_user] from login [test_user];

This will create a user and set the default schema to [dbo];
create user [test_user] from login [test_user] with default_schema = dbo;

I agree that it is highly annoying and wish it didn't happen.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1555627
Posted Thursday, March 27, 2014 3:06 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:28 PM
Points: 786, Visits: 691
I see that Barry already has attempted to answer your questions, so I will not try to since there are so many of them.

However, in this article on my web site there are two examples how you can write a stored procedure that starts a specific job. One uses certificate-signing and one uses EXECUTE AS.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1555669
Posted Friday, March 28, 2014 2:15 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
Erland Sommarskog (3/27/2014)
I see that Barry already has attempted to answer your questions, so I will not try to since there are so many of them.

However, in this article on my web site there are two examples how you can write a stored procedure that starts a specific job. One uses certificate-signing and one uses EXECUTE AS.


Top Drawer stuff as always Erland. Thanks for the link.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #1556146
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse