时间戳

首页 > 解决方案 > T-SQL 获取 SQL Server 2012 中登录角色的信息

sql-server - T-SQL 获取 SQL Server 2012 中登录角色的信息

问题描述

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

然后我创建了一个存储过程，它将添加一个登录帐户来访问 SQL 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存在，但我不知道如何获取用户名和角色。

标签： sql-servertsql

解决方案

您的代码完全错误。

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

您可能会收到任何其他错误，例如您提供空密码或弱密码并获取

消息 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是 only db_datareader？但是你给了他们sysadmin服务器角色。这意味着不仅不需要将它们添加为 and 的成员， SecurityAdmin而且ProcessAdmin不需要创建它们，db_datareader因为这些登录已经拥有服务器上所有可能的权限。

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

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

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

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

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

推荐阅读