Are the posted questions getting worse?
-
Ed Wagner (4/11/2016)
Hugo Kornelis (4/11/2016)
I need some help in this topic.Short story: OP tries to give users access to a view but not to a base table. First went about this wrong (different owners, no ownership chaining). Based on my suggestions they change the objects to all have the same owner and now the security works as expected.
With one exception. In a script that has a USE statement, a GO, and then a SELECT from the view, they get a permission violation. Remove the GO between USE and SELECT, or remove the USE completely, and the violation goes away.
I am at a loss to explain this. If anyone has an idea, please chime in!
You got hold of good one there, Hugo. I gave it a shot, but I really doubt that'll be it.
So why does some of the code to be tested include two GO statements?
If I put the code together, ignoring the comments between code blocks, I see
USE Database
GO
GO
SELECT ....
But yeah, I got nothing.
-
The downside of not having a hand in development-type stuff...
You don't know about handy things like the SSIS task "Transfer Jobs" and "Transfer Logins"...
Thankfully I came across those today, going to give them a whirl migrating to my new servers soon.
Even if the jobs need a bit of cleanup, it'll still be easier (and I think faster) than right-click->script->create to->CTRL+A->CTRL+C->new query on new server->CTRL-V->F5...
😎
-
jasona.work (4/13/2016)
The downside of not having a hand in development-type stuff...You don't know about handy things like the SSIS task "Transfer Jobs" and "Transfer Logins"...
Thankfully I came across those today, going to give them a whirl migrating to my new servers soon.
Even if the jobs need a bit of cleanup, it'll still be easier (and I think faster) than right-click->script->create to->CTRL+A->CTRL+C->new query on new server->CTRL-V->F5...
😎
What a feeling - Every day is a learning day 😀
-
BL0B_EATER (4/13/2016)
jasona.work (4/13/2016)
The downside of not having a hand in development-type stuff...You don't know about handy things like the SSIS task "Transfer Jobs" and "Transfer Logins"...
Thankfully I came across those today, going to give them a whirl migrating to my new servers soon.
Even if the jobs need a bit of cleanup, it'll still be easier (and I think faster) than right-click->script->create to->CTRL+A->CTRL+C->new query on new server->CTRL-V->F5...
😎
What a feeling - Every day is a learning day 😀
Yeah, I enjoy picking up new tricks like this.
Once I get my first new QA box out of the penalty box (Also known as the set it up, let the security team scan for vulnerabilities, fix what they find, rinse and repeat until clean box) I'm going to give it a whirl.
-
jasona.work (4/13/2016)
The downside of not having a hand in development-type stuff...You don't know about handy things like the SSIS task "Transfer Jobs" and "Transfer Logins"...
Thankfully I came across those today, going to give them a whirl migrating to my new servers soon.
Even if the jobs need a bit of cleanup, it'll still be easier (and I think faster) than right-click->script->create to->CTRL+A->CTRL+C->new query on new server->CTRL-V->F5...
😎
be warned - transfer logins does not carry across passwords, so for SQL authenticated IDS its all but useless
---------------------------------------------------------------------
-
george sibbald (4/13/2016)
jasona.work (4/13/2016)
The downside of not having a hand in development-type stuff...You don't know about handy things like the SSIS task "Transfer Jobs" and "Transfer Logins"...
Thankfully I came across those today, going to give them a whirl migrating to my new servers soon.
Even if the jobs need a bit of cleanup, it'll still be easier (and I think faster) than right-click->script->create to->CTRL+A->CTRL+C->new query on new server->CTRL-V->F5...
😎
be warned - transfer logins does not carry across passwords, so for SQL authenticated IDS its all but useless
Figures...
Not surprising, but figures...
I don't have too many SQL logins, and really only one server has a hoard of AD Logins, but as long as it keeps the database access information, I'll be happy.
-
I have never seen a better way of moving SQL Auth logins than sp_help_revlogin. I always go straight to that and it never fails.
-
Has the transfer stuff improved since 2k5?
I remember trying it and it failing because it tried to create things in the wrong order. Dependencies were created before their precedents. At that point, I gave up and haven't tried it since.
-
jasona.work (4/13/2016)
The downside of not having a hand in development-type stuff...You don't know about handy things like the SSIS task "Transfer Jobs" and "Transfer Logins"...
Thankfully I came across those today, going to give them a whirl migrating to my new servers soon.
Even if the jobs need a bit of cleanup, it'll still be easier (and I think faster) than right-click->script->create to->CTRL+A->CTRL+C->new query on new server->CTRL-V->F5...
😎
Hopefully the right click is from system object view with multiple jobs selected.
On the flip side, after I script it, I just change my connection. no copy/paste.
As for logins, I have an updated version of help_revlogin that I use. Same concept just a bit more efficient.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Last I read though, sp_help_revlogin doesn't work with SQL2014, which is what I'm moving to. Yeah, looking at the KB for it, it looks like it quit working (at least if you want to include the password) as of SQL2012...
(For those wanting to see the KB, here)
Also, I'd used this when we went from SQL2008 to 2008R2 and new servers last time.
OK, take away my DBA membership card, I didn't know about the "Change Connection" thing...
Previously I had scripted out the Agent jobs, then did my copy->paste->run thing. This will (if I go this route,) greatly simplify things...
-
Hmm... does that mean that they more strongly encrypt the SQL 2014 password? What is different there?
-
jeff.mason (4/13/2016)
Hmm... does that mean that they more strongly encrypt the SQL 2014 password? What is different there?The SP doesn't actually decrypt the passwords, it hashes them. It looks like there were changes in the hashing algorithms, such that SQL2012 on up can't "unhash" the password value...
-
Right, so does that make the basic password more secure? SQL Auth is not what I would call the most secure way to authenticate, and I was curious if the changes to the hash would make the basic password more secure.
-
jeff.mason (4/13/2016)
Right, so does that make the basic password more secure? SQL Auth is not what I would call the most secure way to authenticate, and I was curious if the changes to the hash would make the basic password more secure.I'd say no, it doesn't. Presuming this article is correct[/url] they went from SHA1 to SHA_512 with a salt. Glancing at the first page of results for break sha512, it looks like it's not broken yet, but that's no guarantee...
-
SQLRNNR (4/13/2016)
jasona.work (4/13/2016)
The downside of not having a hand in development-type stuff...You don't know about handy things like the SSIS task "Transfer Jobs" and "Transfer Logins"...
Thankfully I came across those today, going to give them a whirl migrating to my new servers soon.
Even if the jobs need a bit of cleanup, it'll still be easier (and I think faster) than right-click->script->create to->CTRL+A->CTRL+C->new query on new server->CTRL-V->F5...
😎
As for logins, I have an updated version of help_revlogin that I use. Same concept just a bit more efficient.
Okay, you've spiked my curiosity. Is it something you can share?
Viewing 15 posts - 53,491 through 53,505 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply