It looks like you did not restore a backup but you just transferred the tables with some kind of bcp tool.
Common issues with backup/restore are, in my experience:
a) Logins missing or mismatched, because they are in the master database
b) Collation problems when the collation of the server on which you restore the backup is not the same as the one from which you made the backup.
I had exactly the problems you had on sqlserver 7 when I had a primary / slave setup based on replication. The day I switched the slave as a master I had to reseed all the identity columns and process all the grants manually. This was not a surprise this was because replication does not replicate system tables and because the identity columns were set as "NOT FOR REPLICATION".
I've done this a couple of weeks ago -
Created LOGIN
, ROLE
and USER
. Added USER
to ROLE
and granted explicit permissions to the ROLE
itself. The advantage in my opinion is, that you can add further users later on without the struggle to grant the permissions, again.
First I created the login:
IF NOT EXISTS
(
SELECT
"name"
FROM
"master"."dbo"."syslogins"
WHERE
"name" = 'your_login'
)
BEGIN
CREATE LOGIN your_login WITH PASSWORD = 'your_password', CHECK_POLICY = OFF;
END
I've added the CHECK_POLICY param because things are created on user interaction and I'm not able to see, if policies are activated server-sided.
Followed by the user:
IF NOT EXISTS
(
SELECT
1
FROM
"sys"."database_principals"
WHERE
"name" = N'your_username'
)
BEGIN
CREATE USER your_username FOR LOGIN your_login;
END
And the role third:
IF NOT EXISTS
(
SELECT
1
FROM
"sys"."database_principals"
WHERE
"name" = N'your_role_name'
)
BEGIN
CREATE ROLE "your_role_name";
END
Finally I added the user to the created role:
EXECUTE sp_addrolemember N'your_role_name', N'your_username';
After that, permissions can be set for each type of object, eg
GRANT EXECUTE ON "dbo"."your_sproc" TO your_role_name;
You don't have to add denydatareader
or else, if you explicitly grant permissions to the specific objects. Beware that an for example INSERT
permission doesn't include the SELECT
permission on the same object. ( at least in my case it didn't ;) )
If you have problems executing or selecting from your UDF
, check if you have granted all permissions on the referenced objects in the UDF
itself!
Update after Cobus' update in OP:
I've executed the queries you provided based on my initial answer, results following:
select * from dbo.testtable
-- The SELECT permission was denied on the object 'testtable', database 'xyz', schema 'dbo'.
update dbo.testtable set id = 4 where id = 2
-- The SELECT permission was denied on the object 'testtable', database 'xyz', schema 'dbo'.
-- The UPDATE permission was denied on the object 'testtable', database 'xyz', schema 'dbo'.
update dbo.testtable set id = 4
-- The INSERT permission was denied on the object 'testtable', database 'xyz', schema 'dbo'.
insert into dbo.testtable (id) values (5)
-- The INSERT permission was denied on the object 'testtable', database 'xyz', schema 'dbo'.
It behaves as intended. Do you have any further permissions granted / set or properties which allow you to access dbo-schema?
Best Answer
You are using the right syntax:
However, a schema in SQL Server is not the same as a database.
The default schema for objects is
dbo
as in my example. You do not need to specify the database name if you are connected to it, but you could like this:There seems to be no way to get the current setting as it is session-scoped and not persisted.
If you run this query in your database it returns the fully qualified names for all tables in there:
SET IDENTITY_INSERT ON...
must be run in the same session as the insert.