August 10, 2017 at 4:07 am
I have a db on an instance where someone else is the sysadmin.
What steps can I take to hide my table, view, procedure etc definitions/design from this logon. I too am sysadmin.
But the other user/client is currently copying tables , views design etc and I would like to stop them copying my work?
Thanks
August 10, 2017 at 4:15 am
If they are sysadmin, there is nothing you can do. Someone who is a member of the sysadmin role has all permissions on the server, and nothing can be denied to them.
Move your DB to another server. or revoke the other user's sysadmin rights.
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
August 10, 2017 at 4:21 am
Thanks Gail.
Ok , I'll look into revoking the sysadmin rights. I'm not sure that will be possible.
Whats the best way of securing all design from a logon with lesser rights. What rights should I give them, is encrytion of any use?
Or indeed of hampering a sysadmin from just lifting everything?
Thanks
August 10, 2017 at 4:38 am
Adam Sandler - Thursday, August 10, 2017 4:21 AMWhats the best way of securing all design from a logon with lesser rights. What rights should I give them, is encrytion of any use?
Or indeed of hampering a sysadmin from just lifting everything?Thanks
If a user/login doesn't have sysadmin/db_owner right, then can't access anything they haven't been granted access to or given permission to do. If that means that they are no longer a sysadmin, and a db_owner of a another database, they will have no access to your database. If, however, all your objects are on the same database, are they at least on different schemas? What permissions does the other user need? Do they need to be able to make DDL changes, INSERT data, EXECUTE stored procedures? If the other user is implicitly given permission to your objects, you can use DENY to stop them access them, but that won't work if they are a syadmin or db_owner.
Encryption works on data, it won't work on the actual SQL you write (views, sps, etc are stored as unencrypted SQL commands). Even then, as a sysadmin, they will have access to all the encryption keys on the server, so would be able to decrypt any stored data you have by using the relevant key.
If you both needs sysadmin rights, personally, I agree with Gail's solution of spinning up a new instance. Do your work there and don't grant them access to the instance.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 10, 2017 at 5:02 am
They really only need access to read write data, execute sps, functons. Thats their only legitimate role on the db anyway.
You can use with encrytion on view etc creation cant you. But this is easily undone - for a sysadmin?
Thanks
August 10, 2017 at 5:46 am
Adam Sandler - Thursday, August 10, 2017 4:21 AMWhats the best way of securing all design from a logon with lesser rights. What rights should I give them, is encrytion of any use?
Give them the minimum permissions needed to do their job. Encrypting data may be required, it will depend on what data you have and how sensitive it is.
Or indeed of hampering a sysadmin from just lifting everything?
You cannot hamper a sysadmin. Anyone with sysadmin can do anything on the instance, that's the point of the role.
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
August 10, 2017 at 5:48 am
Adam Sandler - Thursday, August 10, 2017 5:02 AMYou can use with encrytion on view etc creation cant you. But this is easily undone - for a sysadmin?
It's not actually encryption, it's a form of obfuscation, and for a sysamin it'll maybe slow them down by a minute or so, probably less.
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
August 10, 2017 at 5:54 am
Ok thanks. I'll see if I am able to remove their sysadmin.
August 10, 2017 at 6:01 am
Adam Sandler - Thursday, August 10, 2017 5:02 AMThey really only need access to read write data, execute sps, functons. Thats their only legitimate role on the db anyway.You can use with encrytion on view etc creation cant you. But this is easily undone - for a sysadmin?
Thanks
On all Schemas? You could, therefore, make use of the db_reader, db_writer roles. You could also create an executor role on the database for them along the lines of:Use [YourDB];
GO
CREATE ROLE db_executor;
GRANT EXECUTE TO db_executor;
GO
Otherwise, if it's on a specific Schema, you could do something like: Use [YourDB];
GO
GRANT SELECT ON SCHEMA::[YourSchema] TO [OtherUser];
GRANT UPDATE ON SCHEMA::[YourSchema] TO [OtherUser];
GRANT INSERT ON SCHEMA::[YourSchema] TO [OtherUser];
GRANT SELECT ON SCHEMA::[YourSchema] TO [OtherUser];
GRANT DELETE ON SCHEMA::[YourSchema] TO [OtherUser];
GRANT EXECUTE ON SCHEMA::[YourSchema] TO [OtherUser];
GO
Obviously remove any you don't want the user to have, and ensure you remove their sysadmin first. You may also need to ensure you create a user for them on the database; as they are sysadmin at the moment one may not have been created for them.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 10, 2017 at 6:28 am
Thom A - Thursday, August 10, 2017 6:01 AMAdam Sandler - Thursday, August 10, 2017 5:02 AMThey really only need access to read write data, execute sps, functons. Thats their only legitimate role on the db anyway.You can use with encrytion on view etc creation cant you. But this is easily undone - for a sysadmin?
Thanks
On all Schemas? You could, therefore, make use of the db_reader, db_writer roles. You could also create an executor role on the database for them along the lines of:
Use [YourDB];
GO
CREATE ROLE db_executor;
GRANT EXECUTE TO db_executor;
GO
Otherwise, if it's on a specific Schema, you could do something like:Use [YourDB];
GO
GRANT SELECT ON SCHEMA::[YourSchema] TO [OtherUser];
GRANT UPDATE ON SCHEMA::[YourSchema] TO [OtherUser];
GRANT INSERT ON SCHEMA::[YourSchema] TO [OtherUser];
GRANT SELECT ON SCHEMA::[YourSchema] TO [OtherUser];
GRANT DELETE ON SCHEMA::[YourSchema] TO [OtherUser];
GRANT EXECUTE ON SCHEMA::[YourSchema] TO [OtherUser];
GOObviously remove any you don't want the user to have, and ensure you remove their sysadmin first. You may also need to ensure you create a user for them on the database; as they are sysadmin at the moment one may not have been created for them.
Right thanks yes. I'll ensure they only have the db_reader etc. Thanks for your help.
August 10, 2017 at 6:32 am
Adam Sandler - Thursday, August 10, 2017 6:28 AMRight thanks yes. I'll ensure they only have the db_reader etc. Thanks for your help.
If you do that, they won't be able to execute stored procedures or modify data.
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
August 10, 2017 at 6:37 am
GilaMonster - Thursday, August 10, 2017 6:32 AMAdam Sandler - Thursday, August 10, 2017 6:28 AMRight thanks yes. I'll ensure they only have the db_reader etc. Thanks for your help.
If you do that, they won't be able to execute stored procedures or modify data.
Etc - I mean the 3 Thom specified. Thanks. Thats what most users are anyway.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply