Sql-server – is it safe to grant permission to all users in Microsoft sql server

access-controldatabase-designpermissionsSecuritysql server

I have a global production server and database, as I all users should have access to it by remote desktop.

Is it possible to grant NTFS permission on all database files (mdf+ldf files) to all users? something like

everyone can full access to it.

note that : we didn't need to share anything by file sharing service.

our app admins can remote and then if needed they all can detach-attach all database if they want. then they want to move or change location of mdf or ldf if they want

my suggestion is we can grant full access to system server admins.

but our dba said:

we grant permission to everyone and it didn't have any risk

is this safe?

Best Answer

I'm not entirely sure what problem you're trying to solve, but if you grant the kind of access you're describing to "Everyone" and you get hacked, now you have opened up a lot more possibilities for someone to do damage to your systems. That is access that they could not have done if access had only been granted to a select few people. It sounds like you want to have certain people with the ability to move database files around as needed. This will require a login and user and the ALTER DATABASE permission as well as an AD group that is either a local admin on the server or has been granted specific access to the folder(s) where the database files are stored. See the below question and answer about ALTER Database.

https://stackoverflow.com/questions/9798968/permissions-required-to-run-alter-database-set-single-user-statement-on-sql-se

Related Solutions

Grant create permission on a specific schema in Oracle 11g

You are correct that there is no way to grant a user create/drop/etc permissions on an entire schema. I suggest you look into proxy authentication. This basically involves altering user A to allow user B to proxy as A:

ALTER USER A GRANT CONNECT THROUGH B;

Then the connection uses user B's authentication, but gets the permissions of user A.

connect B[A]/Password@Database

This question was somewhat covered by my more specific question here.

Note on Roles: Roles work well for giving Object Privileges to another user since the privileges are tied to a specific object. While Roles can grant System Privileges, they apply either to the users own schema or to the entire database and therefore can't apply to another schema. For example, the user B could be granted CREATE TABLE which would allow it to create tables in its own schema or CREATE ANY TABLE which would allow it to create tables in any schema. These permissions could be granted directly or through a role, but the former wouldn't allow create privileges in the A schema. The latter would, but would also allow create privileges in any schema including sys, which would be a security concern.

Sql-server – Grant a login of Sql server authentication the same permission of a Windows group login with Windows authentication

At the database level, you could simply add both to the same database role. Of course, when you create the role, you'll have to grant it all the same permissions as the existing group, but you only have to do that once.

At the server level, to do that without re-applying all of the permissions (which you could probably script without a whole lot of effort - you can get some ideas here), you'll have to wait for SQL Server 2012, unless you can use a fixed server role.

Best Answer

Related Solutions

Grant create permission on a specific schema in Oracle 11g

Sql-server – Grant a login of Sql server authentication the same permission of a Windows group login with Windows authentication

Related Question