关于sqlserver schema/user/login/role

最新推荐文章于 2024-02-07 22:09:49 发布

qingqingtimes

最新推荐文章于 2024-02-07 22:09:49 发布

阅读量454

点赞数

分类专栏： sqlserver 文章标签： sqlserver schema database login user object

本文链接：https://blog.csdn.net/qingqingtimes/article/details/6684220

版权

sqlserver 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

对sqlserver schema/user/login/role的理解

explain schema/user/login/role
    LOGIN:
        Login is server level object not database level. don't belong to any database.
        One login can having multi server-roles
        Multi user can be mapped to one login
        Can grant securables to login. securables:
                            (Servers(alter, connect, shutdown, view database etc.), Endpoints, Logins(control, alter, view definitions))
    USER:
        One user be mapped to one login
        Have the default schema
        Can beloning to multi database-roles
        Can grant securables to user: securables:(database, tables, procedure....... )

    ROLE:
        Server-level:
            Pre-defined database-level role:we can not Delete/alter:(sysadmin,serveradmin,setupadmin,securityadmin,processadmin,dbcreator,diskadmin)

        Database-level:
            Database-roles:
                Predefined database-level role, we can not delete/alter: (db_accessadmin, db_backupoperator ......),
                User defined roles: we can create/alter/delete.
                    one role can own several schema.
                    one role can have Users, Database roles as memebers

            Application-roles

    SCHEMA:
        We can create same schema in diff database. means schema is uniqe only in database level. so we need toidentify object by: select * from [databasename].[schemaname].[tablename]

        For every database, after created, we have 4 schema is mandatory, can not be deleted: dbo，guest，sys.INFORMATION_SCHEMA

        If do not specify the schemaname, sqlserver searche the schema by this sequence:
            sys schema
            user default schema
            dbo schema