March 27, 2014 at 11:00 am
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.
March 27, 2014 at 11:35 am
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.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 27, 2014 at 12:00 pm
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.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 27, 2014 at 12:08 pm
Sorry the original "question" is so all over the place. Your response was very enlightening actually.
March 27, 2014 at 12:46 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 27, 2014 at 1:20 pm
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.
March 27, 2014 at 3:06 pm
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.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
March 28, 2014 at 2:15 pm
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.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply