SQL Server basic permission management [memo]

 

In SQL ServerStored ProcedureManagementLogin Name, Including:

Sp_addlogin sp_droplogin sp_helplogins

Sp_grantlogin sp_revokelogin sp_denylogin

Note thatStored ProcedureRequiredInMaster Database.

 

BelowCodeImplement simple three-tier permissions:

Use S100801a
Go

-- ====================================== Database logon ====================== ==========
-- Add a logon user (sp_addlogin 'username', 'Password', 'default database name ')
Exec Sp_addlogin n ' XG ' , N ' Xg123 '
-- Or
Exec Sp_addlogin n ' XG ' , N ' Xg123 ' , N ' Master '

-- remove a logon user
exec sp_droplogin n ' XG '

-- ================================== database access ============== ===========

-- Add a database access user (sp_grantdbaccess 'username', 'database name ')
use s100801a
go
exec sp_grantdbaccess n ' XG ' , n ' db_xg '

--Remove Database Access Users
ExecSp_revokedbaccess n'Db_xg'

-- ====================================== Database permissions ====================== ==========

-- Grant all permissions to the database (sp_addrolemember 'Role ', 'user ')
Exec Sp_addrolemember n ' Db_owner ' , N ' Db_xg '
-- You can only access your own database and the Public tables of guest users.

-- Remove all permissions to the database (sp_droprolemember 'Role ', 'user ')
Exec Sp_droprolemember n ' Db_owner ' , N ' Db_xg '

/* ===================================================== ==========================================
Important:
In a later version of Microsoft SQL Server, the access permission function using 'System stored process' will be deleted.
To avoid using this function in the new development work, you should modify the method to implement this function.
========================================================== ========================================= */

 

The implementation method is as follows: -- The following example first creates a Server login name named 'xg' with a password,
-- Then, create the user 'abo _ xg' corresponding to the login name 'xg' in the database '100801a '.

-- Add Login User
Create Login XG With Password =   ' Xg123 ' ;

--Remove Login User
DropLogin XG;

--Add Database Access Users
UseS100801a;
Create UserDbo_xgForLogin XG;
Go 

-- Remove Database Access Users
Use S100801a;
Drop   User Dbo_xg;
Go
 
-- Distribution permission
Grant   Insert , Select , Update , Delete   On Students To   Public   -- Dbo_xg
Revoke   Insert , Select , Update , Delete   On Students From   Public   -- Dbo_xg

-- Grant all on students to aa_0801
-- Revoke all on students from aa_0801

When you use SQL commands to assign permissions, you can use commands such as grant, deny, and revoke.