6
I am not quite sure if using the built-in privilege system in combination with encapsulation of some or all of the SQL code in stored procedures is the only way, but it certainly is the sane way of going about restricting access to the database. You are free to introduce your own system, of course, but from what I have learnt this far, there is no need to ...
4
What am I missing?
The DOMAIN\user login should be able to run dbo.sp_HelloWorld with it's corresponding user in the MyDB context.
The EXECUTE AS USER command is the reason for the error being returned.
Execute as user documentation
Specifies the context to be impersonated is a user in the current
database. The scope of impersonation is restricted ...
3
First you convert sysdate to a char type, then you convert it back to a date type without specifying the format string, which may or may not work based on the settings of the session running this code.
This is completely unnecessary, just use trunc(sysdate) to remove the hours, minutes and seconds.
Also, if you return a date type, then the format depends ...
2
Use REPLACE(SearchTowns, ',', '|') to change commas to pipes.
Put that result in, say, @regexp.
Construct, via CONCAT a single SELECT with
SET @sql = CONCAT('SELECT
name,
Town,
FROM userLocation where Town REGEXP "', @regexp, '"');
PREPARE and EXECUTE the query.
That query should look like:
SELECT
name,
Town,
FROM userLocation ...
2
You're watching a 4-year-old video on YouTube, and sp_BlitzFirst no longer works that way.
If you want to run sp_BlitzWho with @ExpertMode = 1, your best bet is to go ahead and call it directly.
1
The use of views backed by linked server queries puts you in a pretty tough situation. You get bad cardinality estimates coming out of the "remote query" operators (10k rows estimated, vs. the actuals of 521k and 30k respectively). This results in SQL Server choosing to sort and use a merge join, when it would probably have chosen a hash join had the ...
1
Beginning a transaction causes any pending transaction to be committed. See Section 13.3.3, “Statements That Cause an Implicit Commit”, for more information.
-- https://dev.mysql.com/doc/refman/8.0/en/commit.html
and
Transactions cannot be nested. This is a consequence of the implicit commit performed for any current transaction when you issue a START ...
1
There is a simple and a complicated way.
The complicated way is to join with pg_roles and get the user name from there.
But since PostgreSQL hackers don't want to type more than necessary, they came up with something else:
Internally, every object is not identified by its name (which is mutable) but by its “object ID”. This is the number that ...
1
@nbk had given what you want - atleast what you made us to understand.
But...
Think of re-designing your whole job. Do not go by your fluctuating number of columns. There are better ways. json is one. Transactional database is what is been used in lot of projects. Those are the right methods to store / retrieve than having variable number of columns.
1
The update command for Table 1 to update all names score
Would look like
UPDATE table1 as t1
INNER JOIN (SELECT DISTINCT(name) AS distname, SUM(points)-SUM(penalizes) AS score
FROM table2
GROUP BY name) t2
ON t1.distname = t2.distname SET t1.score = t2.score;
But better would be a trigger, which update only the rows in table1, username is actualy ...
1
Using (QUERYTRACEON 460) did not work for me when putting it at the end of my query.
I turned it on at the DB level and it worked:
DBCC TRACEON(460, -1);
GO
But, make sure to turn it back off once you've found and fixed the issue, do not leave it on!
DBCC TRACEOFF(460, -1);
GO
Only top voted, non community-wiki answers of a minimum length are eligible
