Scribd
Upload
16 views5 pages

SQL Procedures for User Management

The document contains code for stored procedures and views to manage database users and login authentication, including procedures to add and remove logins and grant database access, as well as a view to select employee names from a table. It also includes C# code for a program that connects to the database, executes queries and non-query commands, and provides functions for common database operations like executing a data reader or data adapter.

Uploaded by

Trần Văn Phượng
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
16 views5 pages

SQL Procedures for User Management

The document contains code for stored procedures and views to manage database users and login authentication, including procedures to add and remove logins and grant database access, as well as a view to select employee names from a table. It also includes C# code for a program that connects to the database, executes queries and non-query commands, and provides functions for common database operations like executing a data reader or data adapter.

Uploaded by

Trần Văn Phượng
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd

create PROC [dbo].

[SP_TAOLOGIN]
@LGNAME VARCHAR(50),
@PASS VARCHAR(50),
@USERNAME VARCHAR(50),
@ROLE VARCHAR(50)
AS
DECLARE @RET INT
EXEC @RET= SP_ADDLOGIN @LGNAME, @PASS,'QLDSV'
IF (@RET =1) -- LOGIN NAME BI TRUNG
RETURN 1

EXEC @RET= SP_GRANTDBACCESS @LGNAME, @USERNAME


IF (@RET =1) -- USER NAME BI TRUNG
BEGIN
EXEC SP_DROPLOGIN @LGNAME
RETURN 2
END
EXEC sp_addrolemember @ROLE, @USERNAME
IF @ROLE= 'ADMIN_TRUONG' OR @ROLE= 'ADMIN_KHOA'
BEGIN
EXEC sp_addsrvrolemember @LGNAME, 'SecurityAdmin'
EXEC sp_addsrvrolemember @LGNAME, 'ProcessAdmin'
END
RETURN 0 -- THANH CONG

CREATE PROC [dbo].[Xoa_Login]


@LGNAME VARCHAR(50),
@USRNAME VARCHAR(50)

AS
EXEC SP_DROPUSER @USRNAME
EXEC SP_DROPLOGIN @LGNAME

CREATE VIEW V_NHANVIEN


SELECT MANV, HO, TEN, HO + ' ' + TEN AS HOTEN
FROM [Link]
ORDER BY TEN, HO

* Program :
namespace QLDSV_PHANTAN
{
static class Program
{
/// <summary>
/// The main entry point for the application.
/// </summary>
public static SqlConnection conn = new
SqlConnection();
public static String connstr;
public static SqlDataAdapter da;
public static SqlDataReader myReader;
public static String servername = "";
public static String servername1 = "THU-PC\\
TINTIN";
public static String servername2 = "THU-PC\\SUSU";
public static String username;
public static String password;
public static String database = "QLDSV";
public static String mlogin;
public static String mGroup;
public static String mHoten;

public static int KetNoi()


{
if ([Link] != null && [Link]
== [Link]) [Link]();
try
{
[Link] = "Data Source=" +
[Link] + ";Initial Catalog=" + [Link]
+ ";User ID=" +
[Link] + ";password=" +
[Link];
[Link] =
[Link];
[Link]();
return 1;
}

catch (Exception e)
{
[Link]("Lỗi kết nối cơ sở dữ
liệu.\nBạn xem lại user name và password.\n " + [Link],
"", [Link]);
return 0;
}
}

public static SqlDataReader


ExecSqlDataReader(String cmd, String connectionstring)
{
SqlDataReader myreader;
//[Link] = new
SqlConnection(connectionstring);

SqlCommand sqlcmd = new SqlCommand();


[Link] = [Link];
[Link] = cmd;
[Link] = [Link];
if ([Link] ==
[Link]) [Link]();
try
{
myreader = [Link](); return
myreader;
}
catch (SqlException ex)
{
[Link]();
[Link]([Link]);
return null;
}
}

public static DataTable ExecSqlQuery(String cmd,


String connectionstring)
{
DataTable dt1 = new DataTable();
conn = new SqlConnection(connectionstring);
da = new SqlDataAdapter(cmd, conn);
[Link](dt1);
return dt1;

public static int ExecSqlNonQuery(String cmd,


String connectionstring)
{
conn = new SqlConnection(connectionstring);
SqlCommand Sqlcmd = new SqlCommand();
[Link] = conn;
[Link] = cmd;
[Link] = [Link];
[Link] = 300;
if ([Link] == [Link])
[Link]();
try
{

[Link](); [Link]();
return 1;
}
catch (SqlException ex)
{
[Link]([Link]);
[Link]();
return 0;
}
}
public static void SetEnableOfButton(Form frm,
Boolean Active)
{

foreach (Control ctl in [Link])


if ((ctl) is Button)
[Link] = Active;
}

[STAThread]
static void Main()
{
[Link]();

[Link](false);
[Link](new frmMenu());

}
}
}
CREATE PROCEDURE Tim_Hoten
@manv int
AS
select ho, ten from nhanvien where manv = @manv
GO

You might also like