Recently one of my friend was doing some investigation in a new/unknown database and had really hard time locating the primary key – foreign key relations. Like it’s really tough to locate these relations when not enforced at database level in terms of primary key and foreign key constraints. But generally all database developers keep […]
2011-09-14 (first published: 2007-08-15)
2,781 reads
Delete files on a fileshare leaving behind files not older than n-days.
2011-09-13 (first published: 2008-04-02)
10,055 reads
2011-09-12 (first published: 2011-09-02)
1,507 reads
2011-09-09 (first published: 2007-11-19)
8,934 reads
Script one or all roles in a database. Includes users, obect permissions and column permissions.
2011-09-08 (first published: 2008-01-08)
5,265 reads
Generate the scripts that will allow you to compare tables pass/fail.
2011-09-07 (first published: 2007-12-28)
12,593 reads
2011-09-05 (first published: 2007-01-25)
3,947 reads
2011-08-31 (first published: 2011-08-24)
2,539 reads
Lists all the tables in a database that do not contain any LOB columns(text, ntext, image, varchar (max), nvarchar (max), varbinary (max) and xml).
Useful when rebuilding indexes online.
2011-08-30 (first published: 2011-08-25)
760 reads
2011-08-24 (first published: 2011-08-12)
1,164 reads
By Arun Sirpal
Not every production incident is a database in RECOVERY_PENDING or a corrupted event (like...
It is Friday, the queries are running, and nobody is watching the bill. That...
By Steve Jones
Annabel retired from Redgate Software this week. Across most of my career at Redgate,...
Comments posted to this topic are about the item SQL Art, Part 4: Happy...
Comments posted to this topic are about the item Extreme DAX: Take your Power...
Comments posted to this topic are about the item What is the Cloud?
I set up a few users on my SQL Server 2022 instance.
CREATE LOGIN User1 WITH PASSWORD = 'Demo12#1' CREATE USER User1 FOR LOGIN User1 GO CREATE LOGIN User2 WITH PASSWORD = 'Demo12#2' CREATE USER User2 FOR LOGIN User2 GO CREATE LOGIN User3 WITH PASSWORD = 'Demo12#3' CREATE USER User3 FOR LOGIN User3 GOI then created a schema that one of them owned. Under this schema, I added a table with some data.
CREATE SCHEMA MySchema AUTHORIZATION User1
GO
CREATE TABLE Myschema.MyTable(myid INT)
GO
INSERT MySchema.MyTable
(
myid
)
VALUES
(1), (2), (3)
GO
SELECT * FROM MySchema.MyTable
GO
I granted rights and verified that User2 could access this table.
GRANT SELECT ON Myschema.MyTable TO User2 GO SETUSER 'USER2' GO SELECT * FROM MySchema.MyTable GOThis worked. Now, I move this schema to a new user.
ALTER AUTHORIZATION ON SCHEMA::Myschema TO User3; GOWhat happens with this code?
SETUSER 'USER2' GO SELECT * FROM MySchema.MyTable GOSee possible answers