I came across a very nice feature of Entity Framework Core that I would like to share with you, it is the owned types.
EF Core’s owned types allow you to group fields, that you do not want to appear as a reference, in a separate type.
(more…)I came across a very nice feature of Entity Framework Core that I would like to share with you, it is the owned types.
EF Core’s owned types allow you to group fields, that you do not want to appear as a reference, in a separate type.
(more…)هذه المقالة متوفرة أيضا باللغة العربية، اقرأها هنا.
Contents of this article:
This writing is like an encyclopedia for the SQL statements and stored procedures used to enumerate, attach, and detach databases on a SQL Server instance. It lists the statements and stored procedures that you can use and discusses them in simple examples.
Today we are going to talk about how to programmatically enumerate databases on a SQL Server instance and how you can attach and detach SQL Server databases.
You can get a list of databases on a server using one of many ways:
If you are using SQL Server 2000, you can query the system view INFORMATION_SCHEMA.SCHEMATA to get information about current databases on the server.
The following is the table diagram for INFORMATION_SCHEMA.SCHEMATA system view:
Actually, you don’t need to worry about any of the view columns, just the first column, CATALOG_NAME, that you need to worry about, it is the database (i.e. catalog) name.
The following code simply prints out the databases currently found on the default SQL Server instance in the current machine:
// C# Code SqlConnection conn = new SqlConnection("Server=(local);Data Source=;Integrated Security=SSPI"); SqlCommand cmd = new SqlCommand("", conn); SqlDataReader rdr; cmd.CommandText = "SELECT DISTINCT CATALOG_NAME FROM INFORMATION_SCHEMA.SCHEMATA"; conn.Open(); rdr = cmd.ExecuteReader(); while (rdr.Read()) { Console.WriteLine(rdr.GetString(0)); } rdr.Dispose(); cmd.Dispose(); conn.Dispose();
' VB.NET Dim conn As New SqlConnection("Server=(local);Data Source=;Integrated Security=SSPI") Dim cmd As New SqlCommand("", conn) Dim rdr As SqlDataReader cmd.CommandText = "SELECT DISTINCT CATALOG_NAME FROM INFORMATION_SCHEMA.SCHEMATA" conn.Open() rdr = cmd.ExecuteReader() While (rdr.Read()) Console.WriteLine(rdr.GetString(0)) End While rdr.Dispose() cmd.Dispose() conn.Dispose()
Again, this is for SQL Server 2000 only.
Check the MSDN documentation for INFORMATION_SCHEMA.SCHEMATA system view here.
This is a system table specific to SQL Server 2000. In SQL Server 2005 it is provided for backward compatibility as a system view. Therefore, do not rely on this system view (or table) because it is expected to be removed in a future version of SQL Server.
The definition of this table/view is as following:
Only the first column, name, is the most important to us, it contains the database name. Other columns of importance (not for the subject of this topic) are:
You can change the line that sets the command text in the previous code to this line:
// C# Code cmd.CommandText = "SELECT [name] FROM sys.sysdatabases";
' VB.NET Code cmd.CommandText = "SELECT [name] FROM sys.sysdatabases";
Again, using the sys.sysdatabases system table/view is not recommended because it would be removed in a future version of SQL Server.
Check the MSDN documentation for sys.sysdatabases system view/table here.
Check this MSDN article out: Mapping System Tables to System Views for more information about SQL Server 2000 tables mapped to SQL Server 2005 views or stored procedure.
This is the new version included in SQL Server 2005 (and higher versions) replaces the SQL Server 2000 sys.sysdatabases table.
This is a very lengthy system view, it includes tenths of columns, we are interested only on the first column, name, that contains the database name.
You can change the line that sets the command text in the first code to this line:
// C# Code cmd.CommandText = "SELECT [name] FROM sys. databases";
' VB.NET Code cmd.CommandText = "SELECT [name] FROM sys. databases"
Check the MSDN documentation for sys.sysdatabases system view/table here.
This way is different from all others because it is not a system view or a system table, it is a system stored procedure.
This stored procedure accepts no parameters and returns a result set of three columns:
The following code demonstrates this stored procedure:
// C# Code cmd.CommandText = "exec sys.sp_databases";
' VB.NET Code cmd.CommandText = "exec sys.sp_databases"
Check the MSDN documentation for sys.sysdatabases system view/table here.
You can programmatically attach a database to the server in two ways:
The CREATE DATABASE statement can be used to create databases into the server or to attach existing database files.
If you are going to attach a database, this statement should be formed as the following:
CREATE DATABASE database_name ON [ ,...n ] FOR ATTACH [;]
The database_name is the name that you wish to give to the database. In addition, this statement takes filegroups of the database files.
Keep in mind that the database name should not be exist in the server or the function would fail.
The following example shows how you can attach the database database.mdf to the server and give it the name MyDatabase:
// C# Code SqlConnection conn = new SqlConnection("Server=(local);Data Source=;Integrated Security=SSPI"); SqlCommand cmd = new SqlCommand("", conn); cmd.CommandText = "CREATE DATABASE 'MyDatabase' ON " + "PRIMARY ( FILENAME = 'database.mdf' ) " + "FOR ATTACH"; conn.Open(); cmd.ExecuteNonQuery(); cmd.Dispose(); conn.Dispose();
' VB.NET Code Dim conn As New SqlConnection("Server=(local);Data Source=;Integrated Security=SSPI") Dim cmd As New SqlCommand("", conn) cmd.CommandText = "CREATE DATABASE 'MyDatabase' ON " & _ "PRIMARY ( FILENAME = 'database.mdf' ) " & _ "FOR ATTACH" conn.Open() cmd.ExecuteNonQuery() cmd.Dispose() conn.Dispose()
If no log file (.LDF) can be found, SQL Server creates one for you.
The following code attaches the same database along with its log file. Just change the third line of the previous example that sets the command text with this line:
// C# Code cmd.CommandText = "CREATE DATABASE 'MyDatabase' ON " + "PRIMARY ( FILENAME = 'database.mdf' ), " + "FILEGROUP MyDatabase_Log ( FILENAME = 'database.ldf')" + "FOR ATTACH";
' VB.NET Code cmd.CommandText = "CREATE DATABASE 'MyDatabase' ON " & _ "PRIMARY ( FILENAME = 'database.mdf' ), " & _ "FILEGROUP MyDatabase_Log ( FILENAME = 'database.ldf')" & _ "FOR ATTACH"
Check the MSDN documentation for the CREATE DATABASE statement here.
More about database files and filegroups can be found in the MSDN documentation here.
Another way that allows to attach a database to the server is the sys.sp_attach_db stored procedure. The definition of this stored procedure is as following:
sp_attach_db [ @dbname= ] 'dbname' , [ @filename1= ] 'filename_n' [ ,...16 ]
This function takes the database name as the first argument. In addition it accepts another 16 arguments (only the first is required) represent database files. The following code attaches the same database to the server. Again, just change the third line of the previous code to the following line:
// C# Code cmd.CommandText = "exec sys.sp_attach_db MyDatabase, 'database.mdf'";
' VB.NET Code cmd.CommandText = "exec sys.sp_attach_db MyDatabase, 'database.mdf'"
Check the MSDN documentation for the sys.sp_attach_db statement here.
This statement is the same as sys.sp_attach_db stored procedure. However, this statement accepts only one file, the database file.
Check the following code out:
// C# Code cmd.CommandText = "exec sys.sp_attach_single_file_db MyDatabase, 'database.mdf'";
' VB.NET Code cmd.CommandText = "exec sys.sp_attach_single_file_db MyDatabase, 'database.mdf'"
Check the MSDN documentation for the sys.sp_attach_single_file_db statement here.
Unlike attaching databases, you can detach a database from a server in only two ways:
This statement is used to remove one or more databases from SQL Server. It has the following syntax:
DROP DATABASE database_name [ ,...n ] [;]
The following code simply executes this statement against our database MyDatabase:
// C# Code SqlConnection conn = new SqlConnection("Server=(local);Data Source=;Integrated Security=SSPI"); SqlCommand cmd = new SqlCommand("", conn); cmd.CommandText = "DROP DATABASE MyDatabase"; conn.Open(); cmd.ExecuteNonQuery(); cmd.Dispose(); conn.Dispose();
' VB.NET Code Dim conn As New SqlConnection("Server=(local);Data Source=;Integrated Security=SSPI") Dim cmd As New SqlCommand("", conn) cmd.CommandText = "DROP DATABASE MyDatabase" conn.Open() cmd.ExecuteNonQuery() cmd.Dispose() conn.Dispose()
Check the MSDN documentation for the sys.sp_attach_single_file_db statement here.
Huh, the last one. This stored procedure is used to detach a database from the server. It has the following syntax:
sp_detach_db [ @dbname= ] 'database_name'
It accepts a single argument, the database name. The following code removes our database, MyDatabase, from the server:
// C# Code cmd.CommandText = "sys.sp_detach_db MyDatabase";
' VB.NET Code cmd.CommandText = "sys.sp_detach_db MyDatabase"
Check the MSDN documentation for the sys.sp_attach_single_file_db statement here.
Every statement and stored procedure we discussed in this writing requires specific permissions. Check the MSDN documentation to get more information.
هذه المقالة متوفرة أيضا باللغة العربية، اقرأها هنا.
This lesson discusses all the details of SQL Server logins. It begins by discussing how to create SQL Server logins. After that, it focuses on how to change the properties of existing login. Next, it discusses how to delete an existing login. Moreover, we will focus on how to enumerate a list of existing logins and roles. Lastly, we will talk a look on how to manage login permissions in SQL Server. In addition, we will link between SQL Server and .NET Framework and we will teach you many techniques other than what this lesson is focusing on.
This is the table of contents of this lesson:
Today, we are concentrating on how to work with SQL Server logins. A login is simply credentials for accessing SQL Server. For example, you provide your username and password when logging on to Windows or even your e-mail account. This username and password build up the credentials. Therefore, credentials are simply a username and a password.
You need valid credentials to access your SQL Server instance or even to access a database from a .NET application for instance. Like Windows credentials, SQL Server uses multiple credentials to secure each of its granules differently from the other. For example, Windows links the user’s identity with multiple roles. Therefore, user is allowed to access only the resources that are allowed for him based on his identity and roles. In addition, using ACL you can limit access to some system resources and allow others. SQL Server on the other hand uses credentials to manage what the user is allowed to do with SQL Server. For instance, a specific user may not be allowed to access the Northwind database or even to modify it only.
For those reasons and more, we decide to discuss in this lesson how to work with SQL Server logins.
SQL Server allows four types of logins:
Actually, we are interested in only logins based on Windows Credentials and logins specific to SQL Server. For more information about mapped logins, consult MSDN documentation.
Logins based on Windows credentials, allows you to log in to SQL Server using a Windows user’s name and password. If you need to create your own credentials (username and password,) you can create a login specific to SQL Server.
To create, alter, or remove a SQL Server login, you can take one of three approaches:
Of course, you can combine the last two approaches with either the SQL Server IDE or through .NET code.
If you are using SQL Server Express, you can skip the first way that creates the login using the SQL Server Management Studio.
To create a login via the SQL Server Management Studio, follow those steps:
What about ‘Mapped to certificate’ and ‘Mapped to asymmetric key’ logins? Actually, these logins cannot be created through New Login window. However, you can create it through the CREATE LOGIN statement which is covered soon. However, we would not cover these two types. Therefore, it is useful checking MSDN documentation.
What about security settings in the New Login dialog? Enforcing password policy means enforcing password rules like password complexity requirement and password length. Enforcing password expiration means that the user will be required to change his password from time to time every a specific period specified by SQL Server. In addition, you can require the user to change his password the next time he logs on to SQL Server. Notice that the three settings are related to each other. For example, disabling the enforcement of password policy disables other settings. It is worth mentioning that these settings are only available for logins specific to SQL Server only.
Another way to create a new login is through the CREATE LOGIN T-SQL statement. The syntax of this statement is as following:
CREATE LOGIN login_name { WITH | FROM } ::= WINDOWS [ WITH [ ,... ] ] | CERTIFICATE certname | ASYMMETRIC KEY asym_key_name ::= PASSWORD = 'password' [ HASHED ] [ MUST_CHANGE ] [ , [ ,... ] ] ::= SID = sid | DEFAULT_DATABASE = database | DEFAULT_LANGUAGE = language | CHECK_EXPIRATION = { ON | OFF} | CHECK_POLICY = { ON | OFF} [ CREDENTIAL = credential_name ] ::= DEFAULT_DATABASE = database | DEFAULT_LANGUAGE = language
Actually this statement can be used to create a login of any type. However, because of the needs of our lesson, we will focus on how to create logins based on Windows domain accounts -and groups- and logins specific to SQL Server.
Now we are going to create the login ‘Mohammad Elsheimy’ which is based on the user account ‘Mohammad Elsheimy’ which exists on the machine ‘BillGates-PC’. In addition, we will change the default database to Northwind and the default language to English.
CREATE LOGIN [BillGates-PCMohammad Elsheimy] FROM WINDOWS WITH DEFAULT_DATABASE=[Northwind], DEFAULT_LANGUAGE=[English];
Actually, changing default database and default language is optional. Therefore, you can omit both or only one.
If you need to create a login of the Windows domain group change the login name to the group name.
When working with T-SQL statements, be sure to refresh the Logins node after executing your T-SQL statement to see your new baby.
If you are using SQL Server Express, of course you would not find SQL Server Management Studio to execute the commands. However, you can execute these commands via .NET which is covered soon.
Some T-SQL statements -and stored procedure- require the current login to have some privileges to execute them. If you faced a security problem executing any of the T-SQL statements found here, check the MSDN documentation for more help about the required permissions. Although, be sure to check the last section of this lesson.
Now we are going to create the login ‘My Username’ with the password ‘buzzword’. In addition, this user will have to change his password at the next logon. Moreover, password policy and expiration are turned on. Furthermore, default database is set to Northwind and default language is set to English.
CREATE LOGIN [My Username] WITH PASSWORD=N'buzzword' MUST_CHANGE, CHECK_EXPIRATION=ON, CHECK_POLICY=ON, DEFAULT_DATABASE=[Northwind], DEFAULT_LANGUAGE=[English];
Again, changing default database and default language is optional. In addition, explicitly setting password settings is optional too; you can omit one of them or all of them. However, if you omit the password policy enforcement setting, it is still turned on. If you want to turn it off, set it explicitly.
You can create a new login via the system stored procedure sp_addlogin. The definition of this stored procedure is as following:
sp_addlogin [ @loginame = ] 'login' [ , [ @passwd = ] 'password' ] [ , [ @defdb = ] 'database' ] [ , [ @deflanguage = ] 'language' ] [ , [ @sid = ] sid ] [ , [ @encryptopt= ] 'encryption_option' ]
This stored procedure accepts the following arguments:
This function returns 0 if succeeded or 1 otherwise.
Here is an example creates again the login ‘My Username’ that was created earlier.
EXEC sys.sp_addlogin N'My Username', N'buzzword';
As you might expect, you will receive an error if you tried to execute the last line while a login with the same name exists.
As you know, you can execute any SQL Server command through the SqlCommand object. Therefore, we are going to combine the last two approaches for creating the login with C#. We will create the login programmatically via .NET and C# through our T-SQL statements. Here is the code:
// Connecting to SQL Server default instance // to the default database using current // Windows user's identity. // The default database is usually MASTER. SqlConnection conn = new SqlConnection ("Data Source=;Initial Catalog=;Integrated Security=True"); // Creating a login specific to SQL Server. SqlCommand cmd = new SqlCommand ("CREATE LOGIN [My Username] WITH PASSWORD=N'buzzword' " + "MUST_CHANGE, CHECK_EXPIRATION=ON, CHECK_POLICY=ON, " + "DEFAULT_DATABASE=[Northwind], DEFAULT_LANGUAGE=[English];", conn); // In addition, you can use this command: // EXEC sys.sp_addlogin N'My Username', N'buzzword' // Moreover, you can set the command type to stored procedure, // set the command to sys.sp_addlogin, // and add parameters to the command to specify the arguments. try { conn.Open(); cmd.ExecuteNonQuery(); } catch (SqlException ex) { if (ex.Number == 15025) Console.WriteLine("Login already exists."); else Console.WriteLine("{0}: {1}", ex.Number, ex.Message); } finally { cmd.Dispose(); conn.Close(); }
Notice the numbers assigned to errors.
Here, we will cover the two techniques for accessing SQL Server. We will cover accessing SQL Server through its IDE (SQL Server Management Studio) and accessing SQL Server programmatically via .NET.
If you are using SQL Server Express, you can step the SQL Server Management Studio section and dive directly into .NET.
After you start SQL Server Management Studio, you face the ‘Connect to Server’ dialog that allows you to connect to SQL Server using valid credentials (login in other words.) Figure 3 shows the ‘Connect to Server’ dialog.
If you cancelled this dialog, you will not be able to connect to the server. However, as you know, you can connect again through either the Object Explorer window or File->Connect Object Explorer command.
From this dialog, you can choose between two types of authentication:
In .NET, you can access SQL Server through another login the same way as you do while accessing it via the ‘sa’ login but with changing the username and password to the new information.
The following example shows how to access SQL Server through Windows authentication.
// Connecting to SQL Server default instance // to the default database using current // Windows user's identity. // The default database is usually MASTER. SqlConnection conn = new SqlConnection ("Data Source=;Initial Catalog=;Integrated Security=True"); try { conn.Open(); // Connected } catch (SqlException ex) { Console.WriteLine("{0}: {1}", ex.Number, ex.Message); } finally { conn.Close(); }
Now, it is the time for the code that accesses SQL Server through SQL Server authentication. This code tries to log-in to SQL Server via our newly created login ‘My Username.’ You can specify your username and password via the User ID (or UID) and Password (or PWD) settings in the connection string.
// Connecting to SQL Server default instance // to the default database using the user // "My User" and his password "buzzword" // The default database is usually MASTER. SqlConnection conn = new SqlConnection ("Data Source=;Initial Catalog=;" + "User ID=My Username;Password=buzzword"); try { conn.Open(); // Connected } catch (SqlException ex) { if (ex.Number == 18456) Console.WriteLine("Bad username or password."); else Console.WriteLine("{0}: {1}", ex.Number, ex.Message); } finally { conn.Close(); }
Notice that if you created the user with MUST_CHANGE setting specified, you will not be able to access SQL Server with this user unless you change his password. Notice the error number returned.
While you can create a new login using SQL Server Management Studio, T-SQL statements, or stored procedures, you cannot change (alter) your login using the third way. Therefore, you have only two ways to change your new login, either using SQL Server Management Studio or using the ALTER LOGIN T-SQL statement.
To change a login via SQL Server Management Studio, step down to the Logins node in the Object Explorer then double-click your login to show the Login Properties dialog. This dialog is very similar (yet identical) to the New Login dialog; it is used to change the login properties. Figure 4 shows the Login Properties dialog.
Actually, if this is a Windows authentication login, you would not be able to change any information in the General tab except the default database and default language. However, you can change other characteristics in the other tabs.
If this is a SQL Server authentication login, you are allowed only to change the password besides the default database and default language. However, as with Windows authentication logins, you can change other properties in the other tabs.
What if you need more control over the changing process? What if you need to alter (change) other mapped logins? The answer is you can do this via the T-SQL statement ALTER LOGIN. The syntax of this statement is as following:
ALTER LOGIN login_name { | WITH [ ,... ] } ::= ENABLE | DISABLE ::= PASSWORD = 'password' [ OLD_PASSWORD = 'oldpassword' | [ ] ] | DEFAULT_DATABASE = database | DEFAULT_LANGUAGE = language | NAME = login_name | CHECK_POLICY = { ON | OFF } | CHECK_EXPIRATION = { ON | OFF } | CREDENTIAL = credential_name | NO CREDENTIAL ::= MUST_CHANGE | UNLOCK
While you cannot change the name of a user via the Login Properties dialog, you can do this through the ALTER LOGIN statement. The following T-SQL statement changes our user ‘My Username’ to be ‘someuser’ and changes his password too to be ‘newbuzzword’.
ALTER LOGIN [My Username] WITH NAME = [someuser] , PASSWORD = N'newbuzzword';
As a refresher, some T-SQL statements require special permissions. If you faced a security problem executing statements found here, then you are not authorized. Check the MSDN documentation for more help about permissions required.
Again, if you do not have SQL Server Management Studio, you can use the Server Explorer in Visual Studio .NET.
The following statement disables the login ‘someuser’ so that nobody can access it.
ALTER LOGIN [someuser] DISABLE;
To get your login back, change the DISABLE keyword to the ENABLE keyword.
As we did earlier, you can use the SqlCommand object combined with a SqlConnection object to execute T-SQL statements against your database. The following code segment disables a login and tries to login to SQL Server using it.
// Log in using Windows authentication SqlConnection conn = new SqlConnection ("Data Source=;Initial Catalog=;Integrated Security=True"); SqlCommand cmd = new SqlCommand("ALTER LOGIN [someuser] DISABLE;", conn); try { conn.Open(); // Connected cmd.ExecuteNonQuery(); // Succeeded conn.Close(); // Another technique to create your connection string SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(conn.ConnectionString); // Use this line to remove the Windows auth keyword // builder.Remove("Integrated Security"); // Or else, set Windows authentication to False builder.IntegratedSecurity = false; builder.UserID = "someuser"; builder.Password = "newbuzzword"; conn.ConnectionString = builder.ToString(); // The following line would raise the error 18470 conn.Open(); // Connected conn.Close(); // Closed } catch (SqlException ex) { if (ex.Number == 18470) Console.WriteLine("Account disabled."); else Console.WriteLine("{0}: {1}", ex.Number, ex.Message); } finally { cmd.Dispose(); conn.Close(); }
Notice that new technique of building connection string; it is using the ConnectionBuilder object.
Like creating a new login, deleting (dropping) an existing login can be done through a way of three:
Again, you can combine either the second or the last way with .NET.
If you are using SQL Server Express, you can skip the first way that creates the login using the SQL Server Management Studio.
Form SQL Server Management Studio, step down to the logins object in the Object Explorer and select your login. From the context menu of the login, you can select Delete to delete the login.
To remove a login from SQL Server using a T-SQL statement, you can use the DROP LOGIN statement. The following is the syntax for this statement:
DROP LOGIN login_name
As you know, to delete the login ‘someuser’, use the following example:
DROP LOGIN [someuser];
Actually, you cannot delete a user that already logged on.
This way is used for all types of logins. Just specify the login name.
The system stored procedure sp_droplogin is the procedure responsible for dropping an existing login. The definition of this function is as following:
sp_droplogin [ @loginame = ] 'login'
This function accepts only a single argument which is the name of the login. This is the T-SQL statement that deletes the login ‘someuser’.
EXEC sp_droplogin 'someuser';
Again and again, some T-SQL statements and procedures require special permissions. Check MSDN for details.
As we said earlier, you can execute a T-SQL statement or stored procedure in .NET via the SqlCommand and SqlConnection object. Here is the code that deletes the login ‘someuser’.
SqlConnection conn = new SqlConnection ("Server=.;Data Source=;UID=someuser;PWD=newbuzzword"); SqlCommand cmd = new SqlCommand ("DROP LOGIN [asomeuser];", conn); // In addition, you can use this command: // EXEC sp_droplogin 'someuser'; try { conn.Open(); cmd.ExecuteNonQuery(); } catch (SqlException ex) { if (ex.Number == 15151) Console.WriteLine("Login ds not exist."); else if (ex.Number == 15007) Console.WriteLine("Login already logged on."); else Console.WriteLine("{0}: {1}", ex.Number, ex.Message); } finally { conn.Close(); }
You can enumerate existing logins through the system table server_principals. This table encapsulates SQL Server principals’ information such as logins and roles. The following query statement retrieves all data from the sys.server_principals table.
SELECT * FROM sys.server_principals ORDER BY type, [name];
Figure 5 shows a snapshot of the results on my SQL Server instance.
From the names of column names you can know what every column stores. However, server_principals encapsulates much security data that we are not interested in. For example, it encapsulates roles information that we are not interested in here. The columns ‘type’ and ‘type_desc’ both specifies data type. The ‘type’ column specifies the type while the ‘type_desc’ stores a simple description of that type.
The ‘type’ column could store several values including:
It is worth mentioning that the column is_disabled specifies whether the account is disabled (which equals 1,) or enabled (which equals 0.)
Worth mentioning too that the column sid specifies the SID (Security Identifier) of the login. If this is a Windows principal (user or group,) it matches Windows SID. Otherwise, it stores the SID created by SQL Server to that login (or role.)
We have faced many times permission problems. Here, we will talk about how to work with permissions and to grant or deny a user a specific permission.
You have seen many times how the user can be prevented from executing some T-SQL statements due to his privileges. You can change a user’s permission from many places including Login Properties dialog and Server Explorer dialog.
Take a second look at the Properties dialog of the login. You might notice that other property pages exist such as Server Roles, User Mapping, Securables, and Status pages. Take your time playing with these settings and do not forget to check MSDN documentation.
Another way to change user permissions is through the Server Properties dialog. You can right-click the server and choose Properties to open the Server Properties dialog. Figure 6 shows the Server Properties dialog showing the Permissions page.
As you might think, changing the permissions done through the Permissions dialog. It is worth mentioning that all of those options can be changed through the Securable page of the Login Properties dialog. However, here you can change permissions for many logins -or roles- easily. But with Login Properties dialog, you need to change every login separately.
Notice that, from this page you can change permissions for a login -or role- explicitly. Which means that if you did not change that permission explicitly it (the login or role,) will get that permission from another membership that it belongs to. For example, if you did not specify explicitly the permission ‘Alter any login’ for the Windows user Administrator login, he will get that permission from -for instance- the Windows group Administrators login. If that login denied the permission, then the Administrator login would be denied. You can call this technique ‘Intersection’ as with Windows ACLs.
Take a time playing with the Server Properties dialog especially in the Security and Permissions pages. And be sure to have the MSDN documentation with your other hand.
If you want more help, it is always helpful to check the MSDN documentation. It is useful using the search feature to search for a T-SQL statement or for help on a dialog or window.
At the end of this lesson, you learned many techniques about SQL Server and .NET. You learned all the bits of logins and how to deal with them. Plus, you learned many .NET techniques that help you coding better.
Have a nice day!
هذه المقالة متوفرة أيضا باللغة العربية، اقرأها هنا.
Starting from version 2.0, .NET supports a mechanism to enumerate the SQL Server instances in the local network. This is done by System.Sql.SqlDataSourceEnumerator class that resides on the assembly System.Data.
This class is a implements the singleton pattern, means that you can not instantiate it, and only a single instances serves all, accessed via the static property Instance.
Using the GetDataSources method of SqlDataSourceEnumerator, you get a DataTable object that contains four columns, ServerName, InstanceName, IsCulstered, and Version (Clear names, right?)
The following code retrieves information about all visible SQL Server instances in the local network:
// C# Code static void Main() { DataTable table = SqlDataSourceEnumerator.Instance.GetDataSources(); DisplayTable(table); } private static void DisplayTable(DataTable table) { foreach (DataRow row in table.Rows) { foreach (DataColumn col in table.Columns) Console.WriteLine("{0} = {1}", col.ColumnName, row[col]); Console.WriteLine(new string(' ', 30)); } }
' VB.NET Code Sub Main() Dim table As DataTable = SqlDataSourceEnumerator.Instance.GetDataSources() DisplayTable(table) End Sub Sub DisplayTable(ByVal table As DataTable) For Each row As DataRow In table.Rows For Each col As DataColumn In table.Columns Console.WriteLine("{0} = {1}", col.ColumnName, row(col)) Console.WriteLine(New String(" "c, 30)) Next Next End Sub
If you have a firewall installed on your machine, you will be asked to give permissions to the application.
GetDataSources() demands the FullTrust permission set.
Due to the nature of the mechanism used by SqlDataSourceEnumerator to locate data sources on a network, the method will not always return a complete list of the available servers, and the list might not be the same on every call.
هذه المقالة متوفرة أيضا باللغة العربية، اقرأها هنا.
Code: Geming.Samples.Blob.zip
Binary Large Objects (BLOBs) are pieces of data that have -usually- exceptionally large size (such as pictures or audio tracks). These values stored in SQL Server in an image column.
Sometimes the term BLOB is also applied to large character data values, such as those stored in text or ntext columns.
Also you can store BLOB data in a binary column, but it doesn’t take larger than 8000 bytes. And image columns are more flexible.
Working with BLOB data is a bit strange because:
Our example is fairly simple. This example stores files in a database (FileStore) and retrieves it by name. The example relies on a database that contains one table, MyFiles. And the table itself contains two columns one for filename (PK) and the other is an image column for the file itself.
Storing BLOB data in a database is easiest part:
In order to run this code, you must add using statements to Sql.Data.SqlClient and System.IO.
// C# Code static void StoreFile(string filename) { SqlConnection connection = new SqlConnection ("Server=(local) ; " + "Initial Catalog = FileStore ; " + "Integrated Security = SSPI"); SqlCommand command = new SqlCommand ("INSERT INTO MyFiles VALUES (@Filename, @Data)", connection); command.Parameters.AddWithValue("@Filename", Path.GetFileName(filename)); command.Parameters.AddWithValue("@Data", File.ReadAllBytes(filename)); connection.Open(); command.ExecuteNonQuery(); connection.Close(); }
' VB.NET Code Sub StoreFile(ByVal filename As String) Dim connection As New SqlConnection( _ "Server=(local) ; Initial Catalog = FileStore ; " & _ "Integrated Security = SSPI") Dim command As New SqlCommand( _ "INSERT INTO MyFiles VALUES " & _ "(@Filename, @Data)", connection) command.Parameters.AddWithValue("@Filename", _ Path.GetFileName(filename)) command.Parameters.AddWithValue("@Data", _ File.ReadAllBytes(filename)) connection.Open() command.ExecuteNonQuery() connection.Close() End Sub
Code explanation:
First, we created a connection to the SQL Server database. And then, we created the SqlCommand object that will hold the T-SQL Insert statement. After that, we filled the command parameters with required values. Finally, we executed the command.
Well, for avoiding SQL-Injection attacks, it’s recommended that you use parameters instead of hard-coding the argument. Moreover, you can’t represent binary values as strings.
Frankly, it’s recommended using stored procedures instead of coding the commands.
It’s highly recommended that you dispose disposable objects like SqlConnection and SqlCommand. Try encapsulating it in a using statement.
Retrieving BLOB data is a bit complex than storing it. The following method demonstrates this:
// C# Code static byte[] RetrieveFile(string filename) { SqlConnection connection = new SqlConnection ("Server=(local) ; Initial Catalog = FileStore ; Integrated Security = SSPI"); SqlCommand command = new SqlCommand ("SELECT * FROM MyFiles WHERE Filename=@Filename", connection); command.Parameters.AddWithValue("@Filename", filename); connection.Open(); SqlDataReader reader = command.ExecuteReader(System.Data.CommandBehavior.SequentialAccess); reader.Read(); MemoryStream memory = new MemoryStream(); long startIndex = 0; const int ChunkSize = 256; while (true) { byte[] buffer = new byte[ChunkSize]; long retrievedBytes = reader.GetBytes(1, startIndex, buffer, 0, ChunkSize); memory.Write(buffer, 0, (int)retrievedBytes); startIndex += retrievedBytes; if (retrievedBytes != ChunkSize) break; } connection.Close(); byte[] data = memory.ToArray(); memory.Dispose(); return data; }
' VB.NET Code Function RetrieveFile(ByVal filename As String) As Byte() Dim connection As New SqlConnection( _ "Server=(local) ; Initial Catalog = FileStore ; " & _ "Integrated Security = SSPI") Dim command As New SqlCommand( _ "SELECT * FROM MyFiles " & _ "WHERE Filename=@Filename", connection) command.Parameters.AddWithValue("@Filename", filename) connection.Open() Dim reader As SqlDataReader = command.ExecuteReader _ (System.Data.CommandBehavior.SequentialAccess) reader.Read() Dim memory As New MemoryStream() Dim startIndex As Long = 0 Const ChunkSize As Integer = 256 While (True) Dim buffer(ChunkSize) As Byte Dim retrievedBytes As Long = _ reader.GetBytes(1, startIndex, buffer, 0, ChunkSize) memory.Write(buffer, 0, CInt(retrievedBytes)) startIndex += retrievedBytes If (retrievedBytes ChunkSize) Then Exit While End If End While connection.Close() Dim data() As Byte = memory.ToArray() memory.Dispose() Return data End Function
Code explanation:
After connecting to the database and writing our query, we executed the query by calling ExecuteReader() method of the command object to get read-only forward-only pointer to the retrieved rows.
By default, SqlDataReader reads entire rows -that can be gigabytes of data.- By specifying CommandBehavior.SequentialAccess, it reads the data sequentially in a given chunk size by calling the GetBytes() -or GetChars for BLOB textual data- method.
Calling Read() of the SqlDataReader objects advances the pointer to the next row which is the first single row -if found- in our example.
The GetBytes() method takes five arguments:
It it worth mentioning that this method returns number of bytes retrieved. After calling this method we used a MemoryStream object to write all data retrieved to.
Finally, we retrieve data by calling MemoryStream’s ToArray() function. (I think the code is now clear)
It’s not recommended using MemoryStream if the data is very huge.
SqlConnection, SqlCommand, SqlDataReader, and MemoryStream are all disposable objects.
Because the MemoryStream object may contain the retrieved data it’s highly recommended that you dispose it as soon as possible.
For a complete example download the sample project FileStore.
This project uses a database for storing files and retrieving it.
This database contains only one table, its definition is as follows:
For creating the database, the project also inculdes a SQL Query file that contains the commands for creating it. Simply execute the file.