获得Server 2012中登录角色信息的to

Question

我是Server的新手。我创建了一个数据库，并在数据库角色中添加了3个角色：

• 学校:数据中心
• 品牌:数据所有者
• 老师:数据采集器，数据写入器

然后，我创建了一个存储过程，它将添加一个登录帐户来访问Server：

ALTER PROC [dbo].[sp_CreateLogin]
    @LGNAME VARCHAR(50),
    @PASS VARCHAR(50),
    @USERNAME VARCHAR(50),
    @ROLE VARCHAR(50)
AS
BEGIN
    DECLARE @RET INT

    EXEC @RET = SP_ADDLOGIN @LGNAME, @PASS,'mydatabase'                     

    IF (@RET =1)  -- LOGIN NAME EXIST
        RETURN 1

    EXEC @RET = SP_GRANTDBACCESS @LGNAME, @USERNAME

    IF (@RET =1)  -- USER  NAME EXIST
    BEGIN
        EXEC SP_DROPLOGIN @LGNAME
        RETURN 2
    END

    EXEC sp_addrolemember @ROLE, @USERNAME

    IF @ROLE = 'SCHOOL'
    BEGIN
        EXEC sp_addsrvrolemember @LGNAME, 'sysadmin'
        EXEC sp_addsrvrolemember @LGNAME, 'SecurityAdmin'
        EXEC sp_addsrvrolemember @LGNAME, 'ProcessAdmin'
    END

    IF @ROLE = 'BRAND'
    BEGIN 
        EXEC sp_addsrvrolemember @LGNAME, 'sysadmin'
        EXEC sp_addsrvrolemember @LGNAME, 'SecurityAdmin'
        EXEC sp_addsrvrolemember @LGNAME, 'ProcessAdmin'
    END

    IF @ROLE= 'TEACHER'
    BEGIN  
        EXEC sp_addsrvrolemember @LGNAME, 'ProcessAdmin'
    END
END

现在，我想创建一个存储过程来检查LoginName (@LGNAME)是否已经作为登录帐户存在。如果存在这种情况，则返回用户名(@USERNAME)和角色(@ROLE)。

我可以检查LoginName是否存在，但我不知道如何获得用户名和角色。

Answer 1

你的代码完全错了。

你为什么认为IF (@RET =1) -- LOGIN NAME EXIST

您可以获得任何其他错误，例如您提供空的或弱的密码，并获得

Msg 15116，级别16，状态1，行16密码验证失败。密码不符合Windows策略要求，因为密码太短。

那么您就不了解服务器角色，至少是sysadmin角色。你说过

I create a database and added 3 role in my database roles:

SCHOOL: datareader
BRAND: dataowner
TEACHER: datareader, datawriter

然后你的代码会：

IF @ROLE = 'SCHOOL'
    BEGIN
        EXEC sp_addsrvrolemember @LGNAME, 'sysadmin'
        EXEC sp_addsrvrolemember @LGNAME, 'SecurityAdmin'
        EXEC sp_addsrvrolemember @LGNAME, 'ProcessAdmin'
    END

所以你想让SCHOOL的成员只做db_datareader？但是你给了他们sysadmin服务器的角色。这意味着不仅不需要将它们作为成员添加到SecurityAdmin和ProcessAdmin中，而且不需要使它们成为db_datareader，因为这些登录已经在服务器上拥有了所有可能的权限。

我认为你应该从学习SQL Server固定server roles开始，至少你应该明白sysadmin不仅不需要任何permission，也没有办法去deny smth到sysadmin。因此，您不应该将sysadmin给任何人，这个登录可以删除您的数据库并将您排除在sysadmin之外。

我可以检查LoginName是否存在，但我不知道如何获得用户名和角色

要找到相应的用户，可以使用以下代码：

select name
from sys.database_principals
where suser_sname(sid) = 'your_login';

要检查它是否是角色成员，请检查IS_ROLEMEMBER ('your_role','your_user')是否返回1，your_user是your_role的成员。