ALTER AUTHORIZATION

Question

ALTER AUTHORIZATION

mar.ko

Default port

Points: 1451
More actions
September 3, 2015 at 10:07 am

#304039

I am tired of having to prefix my calls to stored procedures, functions with "dbo".
If I alter authorization to my userid, will that have any side-effects on other users ?
In other words, after I alter, the existing calls with the dbo prefix will still work, correct ?
But I won't have to prefix them any longer, right ?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

Nope. If you change them to be owned by you, you have to give different sets of permissions to all the users. They won't even necessarily see them. Further, good coding practice says to always show the schema owner, so if you change the schema owner to marko your procs will have to be called marko.myproc. There are reasons for setting up schemas (usually involving security and object management), but trying to avoid using them entirely is not a good solution.

"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

mar.ko Default port Points: 1451 More actions · Answer 2

Thanks Grant.

There's no way around this ? Aliases ?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Prefixing objects with their schema is a good thing to do and is a recommended practice. Not doing so can cause problems with plan reuse. It's something I flag as a problem when I do an audit of client DBs.

Recommended practice:

- always prefix the objects with their schema

- always prefix the columns with their table (or table alias)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

Not really. Certain parts of the language seem like a pain until you see all the possibilities for their use.

Let's take schemas. Most people, most of the time, use dbo and nothing else.

You can get imaginative with it.

dbo.TableA

security1.TableA

security2.TableA

Three tables, all named TableA, but, you have to supply the schema to get access to them. Or, are they three tables. I could have a table, dbo.TableA, and then two views, each called TablA, but with different schemas. Then have your reporting tool only log into and have access to the security1 schema (or security2). It'll see something it thinks is TableA, but inside you have code that blocks off or rewrites sensitive information.

So, if you're in the dbo and nothing else use case, yeah, it's a minor pain (one that goes away eventually). But you wouldn't want to take away the ability to do other stuff. If you're doing other stuff, the language has to support it. That's why you have to do the schema name.

"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