About SQL Server database permissions a mess of things

Research permissions These things are mainly because today just toss the database backup related things, backup say, Backup is finished.

But when you restore a backup, you need to take the database offline, then restore it, and then bring it online, well, the problem is online.

According to the MSDN statement: https://msdn.microsoft.com/zh-cn/library/bb522682.aspx

OFFLINE only needs ALTER DATABASE permission for this database, but ONLINE requires the server-level alter ANY DATABASE permission.

It feels like a pit.

Then the server default several roles inside, who has ALTER any DATABASE permissions, in order to see this permission, and see a lot of relationship is not too big things ...

Server-level roles: https://msdn.microsoft.com/zh-cn/library/ms188659.aspx

Database level role: https://msdn.microsoft.com/zh-cn/library/ms189121.aspx

These two URLs are described in the description of the default roles that we normally see when assigning rights to a user.

If you are interested to see, in fact, the main is now I also on the database of the rights assignment requirements are not very rigorous, can be used on the line, it should be strictly for the different permissions of the user assigned role permissions.

By the way also looked at the structure of what is what, of course, did not read too understand is, so first paste two links it:

Understanding of schemas in SQL SERVER 2005/2008 (i): http://www.cnblogs.com/chillsrc/p/3383098.html

Understanding of schemas in SQL SERVER 2005/2008 (ii): http://www.cnblogs.com/chillsrc/p/3389374.html

Feel a bit like the namespace in the program, the database of the various objects are placed in different schemas, so that convenient centralized management and allocation of permissions and so on, can also solve the problem of duplicate names? (Guess only, not verified)

Well, get back to the point and see what permissions the default roles have.

For server roles: https://msdn.microsoft.com/zh-cn/library/ms179872.aspx

DECLARE @TEMP TABLE(RoleNVARCHAR( -), PermissionNVARCHAR( -))INSERT  into @TEMP EXECsp_srvrolepermissionSELECT *  from @TEMP WHEREPermission= 'ALTER DATABASE'

Well, I also learned how to take advantage of the results of stored procedures ... To store with temporary tables

The results of the investigation, the two can meet our requirements. There is a very sad news, this method will be deleted in the future, but not sure which version to delete, do not know who is the successor method, very pit ...

For database roles: https://msdn.microsoft.com/zh-cn/library/ms188423.aspx

This is nothing to look at.

So, here in order to solve the problem of online, the corresponding database users to give dbcreator role is good ... Toss such a big circle ...

Finish

About SQL Server database permissions a mess of things